1、Oracle 索引分析与比较26.1 概述索引在各种关系型数据库系统中都是举足轻重的组成部分,其对于提高检索数据的速度起至关重要的作用。在 Oracle 中,索引基本分为以下几种:B*Tree 索引,反向索引,降序索引,位图索引,函数索引,interMedia 全文索引等。本文主要就前 6 种索引进行分析,由于 interMedia 全文索引涉及的内容可以单独写一篇文章,所以不在此对其做分析。首先给出各种索引的简要解释:b*tree index:几乎所有的关系型数据库中都有 b*tree 类型索引,也是被最多使用的。其树结构与二叉树比较类似,根据 rid 快速定位所访问的行。反向索引:反转了
2、b*tree 索引码中的字节,是索引条目分配更均匀,多用于并行服务器环境下,用于减少索引叶的竞争。降序索引:8i 中新出现的索引类型,针对逆向排序的查询。位图索引:使用位图来管理与数据行的对应关系,多用于 OLAP 系统。函数索引:这种索引中保存了数据列基于 function 返回的值,在 select * from table where function(column)=value 这种类型的语句中起作用。26.2 各种索引的结构分析26.2.1 B*Tree 索引B*Tree 索引是最常见的索引结构,默认建立的索引就是这种类型的索引。B*Tree 索引在检索高基数数据列(高基数数据列是指
3、该列有很多不同的值)时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree 索引比全表检索提供了更有效的方法。但当检查的范围超过表的 10%时就不能提高取回数据的性能。B-Tree 索引是基于二叉树的,由分支块(branch block)和叶块(leaf block)组成。在树结构中,位于最底层底块被称为叶块,包含每个被索引列的值和行所对应的 rowid。在叶节点的上面是分支块,用来导航结构,包含了索引列(关键字)范围和另一索引块的地址,如图 26-1 所示。图 26-1假设我们要找索引中值为 80 的行,从索引树的最上层入口开始,定位到大于等于 50,然后往左找,找到第 2 个分支块
4、,定位为 75100,最后再定位到叶块上,找到 80 所对应的 rowid,然后根据 rowid去读取数据块获取数据。如果查询条件是范围选择的,比如 where column 20 and column select number,dump(1,16) from dual2 union all select number,dump(2,16) from dual3 union all select number,dump(3,16) from dual;NUMBE DUMP(1,16)- -number Typ=2 Len=2: c1,2 (1)number Typ=2 Len=2: c1,3
5、 (2)number Typ=2 Len=2: c1,4 (3)再对比一下反向以后的情况:SQL select number,dump(reverse(1),16) from dual2 union all select number,dump(reverse(2),16) from dual3 union all select number,dump(reverse(3),16) from dual;NUMBE DUMP(REVERSE(1),1- -number Typ=2 Len=2: 2,c1 (1)number Typ=2 Len=2: 3,c1 (2)number Typ=2 Le
6、n=2: 4,c1 (3)我们发现索引码的结构整个颠倒过来了,这样 1,2,3 个索引码基本上不会出现在同一个叶块里,所以减少了争用。不过反向索引又一个缺点就是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用,例如,where columnvalue,因为在索引的叶块中索引码没有分类,所以不能通过搜索相邻叶块完成区域扫描。26.2.3 降序索引降序索引是 8i 里面新出现的一种索引,是 B*Tree 的另一个衍生物 ,它的变化就是列在索引中的储存方式从升序变成了降序,在某些场合下降序索引将会起作用。举个例子,我们来查询一张表并进行排序:SQL select * from test w
7、here a between 1 and 100 order by a desc,b asc;已选择 100 行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)1 0 SORT(ORDER BY)(Cost=2 Card=100 Bytes=400)2 1 INDEX (RANGE SCAN) OF IND_BT (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)这里优化器首先选择了一个索引范围扫描,然后还有一个排序的步骤。如果使用了降序索引,排序的过程会
8、被取消。SQL create index test.ind_desc on test.testrev(a desc,b asc);索引已创建。SQL analyze index test.ind_desc compute statistics;索引已分析再来看下执行路径:SQL select * from test where a between 1 and 100 order by a desc,b asc;已选择 100 行。Execution Plan(SQL 执行计划,稍后会讲解如何使用)。-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Car
9、d=100 Bytes=400)1 0 INDEX (RANGE SCAN) OF IND_DESC (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)我们看到排序过程消失了,这是因为创建降序索引时 Oracle 已经把数据都按降序排好了。另外一个需要注意的地方是要设置 init.ora 里面的 compatible 参数为 8.1.0 或以上,否则创建时 desc关键字将被忽略。26.2.4 位图索引位图索引主要用于决策支持系统或静态数据,不支持行级锁定。位图索引最好用于低 cardinality 列(即列的唯一值除以行数为一个很小的值,接近零),例如又一个“性
10、别 ”列,列值有“Male”,“Female”,“Null”等 3 种,但一共有 300 万条记录,那么 3/3000000 约等于 0,这种情况下最适合用位图索引。位图索引可以是简单的(单列)也可以是连接的(多列),但在实践中绝大多数是简单的。在这些列上多位图索引可以与 AND 或 OR 操作符结合使用。位图索引使用位图作为键值,对于表中的每一数据行位图包含了 TRUE(1)、FALSE(0 )、或 NULL 值。位图索引的位图存放在 B-Tree 结构的页节点中。B-Tree 结构使查找位图非常方便和快速。另外,位图以一种压缩格式存放,因此占用的磁盘空间比 B-Tree索引要小得多。位图索
11、引的格式如表 26-1 所示。表 26-1 位图索引的格式行 值 1 2 3 4 5 6 7 8 9 10Male 1 0 0 0 0 0 0 0 1 1Female 0 1 1 1 0 0 1 1 0 0Null 0 0 0 0 1 1 0 0 0 0如果搜索 where gender=Male,要统计性别是”Male”的列行数的话,Oracle 很快就能从位图中找到共3 行即第 1,9,10 行是符合条件的;如果要搜索 where gender=Male or gender=Female的列的行数的话,也很容易从位图中找到共 8 行即 1,2,3,4,7,8, 9,10 行是符合条件的。如
12、果要搜索表的值的话,那么 Oracle 会用内部的转换函数将位图中的相关信息转换成 rowid 来访问数据块。26.2.5 函数索引基于函数的索引也是 8i 以来的新产物,它有索引计算列的能力,它易于使用并且提供计算好的值,在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件:(1)必须拥有 QUERY REWRITE(本模式下)或 GLOBAL QUERY REWRITE(其他模式下)权限。(2)必须使用基于成本的优化器,基于规则的优化器将被忽略。(3)必须设置以下两个系统参数:QUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEGRI
13、TY=TRUSTED可以通过 alter system set,alter session set 在系统级或线程级设置,也可以通过在 init.ora 添加实现。这里举一个基于函数的索引的例子:SQL create index test.ind_fun on test.testindex(upper(a);索引已创建。SQL insert into testindex values(a,2);已创建 1 行。SQL commit;提交完成。SQL select /*+ RULE*/* FROM test.testindex where upper(a)=A;A B- -a 2Executio
14、n Plan-0 SELECT STATEMENT Optimizer=HINT: RULE1 0 TABLE ACCESS (FULL) OF TESTINDEX(优化器选择了全表扫描)-SQL select * FROM test.testindex where upper(a)=A;A B- -a 2Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost=2 Card=1 Bytes=5)2
15、1 INDEX (RANGE SCAN) OF IND_FUN (NON-UNIQUE) (Cost=1 Card=1)(使用了 ind_fun 索引)26.3 各种索引的创建方法(1)*Tree 索引。Create index indexname on tablename(columnnamecolumnname.)(2)反向索引。Create index indexname on tablename(columnnamecolumnname.) reverse(3)降序索引。Create index indexname on tablename(columnname DESCcolumnn
16、ame.)(4)位图索引。Create BITMAP index indexname on tablename(columnnamecolumnname.)(5)函数索引。Create index indexname on tablename(functionname(columnname)注意:创建索引后分析要索引才能起作用。analyze index indexname compute statistics;26.4 各种索引使用场合及建议(1)B*Tree 索引。常规索引,多用于 oltp 系统,快速定位行,应建立于高 cardinality 列(即列的唯一值除以行数为一个很大的值,存在
17、很少的相同值)。(2)反向索引。B*Tree 的衍生产物,应用于特殊场合,在 ops 环境加序列增加的列上建立,不适合做区域扫描。(3)降序索引。B*Tree 的衍生产物,应用于有降序排列的搜索语句中,索引中储存了降序排列的索引码,提供了快速的降序搜索。(4)位图索引。位图方式管理的索引,适用于 OLAP(在线分析)和 DSS(决策处理)系统,应建立于低 cardinality 列,适合集中读取,不适合插入和修改,提供比 B*Tree 索引更节省的空间。(5)函数索引。B*Tree 的衍生产物,应用于查询语句条件列上包含函数的情况,索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础
18、上能提高查询效率。26.5 附表(索引什么时候不工作)首先要声明两个知识点:(1)RBOA-1Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF TESTStatistics-0 recursive calls0 db block gets0 consistent gets0 physical reads0 redo size0 bytes sent via SQL*Net to client0 bytes received via SQL*Net from client0 SQL*Net r
19、oundtrips to/from client0 sorts (memory)0 sorts (disk)rows processedSQL set autotrace traceonlySQL select * from test.test;Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF TESTStatistics-0 recursive calls0 db block gets0 consistent gets0 physical reads0 redo size0 bytes s
20、ent via SQL*Net to client0 bytes received via SQL*Net from client0 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)rows processedHints 是 Oracle 提供的一个辅助用法,按字面理解就是 提示的意思,确实它起得作用也是提示优化器按它所提供的关键字来选择执行路径,特别适用于 sql 调整的时候。使用方法如下:DELETE|INSERT|SELECT|UPDATE /*+ hint text hinttext. */具体可参考 Ora
21、cle SQL Reference。有了前面这些知识点,接下来让我们来看一下什么时候索引是不起作用的。以下列出几种情况。(1)类型不匹配时。SQL create table test.testindex (a varchar(2),b number);表已创建。SQL create index ind_cola on test.testindex(a);索引已创建。SQL insert into test.testindex values(1,1);已创建 1 行。SQL commit;提交完成。SQL analyze table test.testindex compute statisti
22、cs for all indexes;表已分析。SQL set autotrace on;SQL select /*+RULE */* FROM test.testindex where a=1;(使用基于 rule 的优化器,数据类型匹配的情况下)A B- -1 1Execution Plan-0 SELECT STATEMENT Optimizer=HINT: RULE1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX2 1 INDEX (RANGE SCAN) OF IND_COLA (NON-UNIQUE)(使用了索引 ind_cola)SQL
23、 select /*+RULE */* FROM test.testindex where a=1;(数据类型不匹配的情况)A B- -1 1Execution Plan-0 SELECT STATEMENT Optimizer=HINT: RULE1 0 TABLE ACCESS (FULL) OF TESTINDEX(优化器选择了全表扫描)(2)条件列包含函数但没有创建函数索引。SQL select /*+ RULE */* FROM test.testindex where upper(a)= A;(使用了函数 upper()在列 a 上);A B- -a 2Execution Plan
24、-0 SELECT STATEMENT Optimizer=HINT: RULE1 0 TABLE ACCESS (FULL) OF TESTINDEX(优化器选择全表扫描)-创建基于函数的索引SQL create index test.ind_fun on test.testindex(upper(a);索引已创建。SQL insert into testindex values(a,2);已创建 1 行。SQL commit;提交完成。SQL select /*+ RULE*/* FROM test.testindex where upper(a)=A;A B- -a 2Execution
25、 Plan-0 SELECT STATEMENT Optimizer=HINT: RULE1 0 TABLE ACCESS (FULL) OF TESTINDEX(在 RULE 优化器下忽略了函数索引选择了全表扫描)-SQL select * FROM test.testindex where upper(a)=A;A B- -a 2Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost=2 Car
26、d=1 Bytes=5)2 1 INDEX (RANGE SCAN) OF IND_FUN (NON-UNIQUE) (Cost=1 Card=1)(CBO 优化器使用了 ind_fun 索引)(3)复合索引中的前导列没有被作为查询条件。创建一个复合索引SQL create index ind_com on test.testindex(a,b);索引已创建。SQL select /*+ RULE*/* from test.testindex where a=1;A B- -1 2Execution Plan-0 SELECT STATEMENT Optimizer=HINT: RULE1 0
27、 INDEX (RANGE SCAN) OF IND_COM (NON-UNIQUE)(条件列表包含前导列时使用索引ind_com)SQL select /*+ RULE*/* from test.testindex where b=1;未选定行Execution Plan-0 SELECT STATEMENT Optimizer=HINT: RULE1 0 TABLE ACCESS (FULL) OF TESTINDEX(条件列表不包括前导列是选择全表扫描)-(4) CBO 模式下选择的行数比例过大,优化器采取了全表扫描。SQL select * from test.testindex wh
28、ere a=1;A B- -1 2Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)1 0 TABLE ACCESS (FULL) OF TESTINDEX (Cost=1 Card=1 Bytes=5)(表一共 2 行,选择比例为 50%,所以优化器选择了全表扫描)下面增加表行数SQL declare i number;2 begin3 for i in 1 100 loop4 insert into test.testindex values (to_char(i),i);5 end loop
29、;6 end;7 /PL/SQL 过程已成功完成。SQL commit;提交完成。SQL select count(*) from test.testindex;COUNT(*)-102SQL select * from test.testindex where a=1;A B- -1 11 2Execution PlanSELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)1 0 INDEX (RANGE SCAN) OF IND_COM (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)(表一共 102 行,
30、选择比例为 2/102=2%,所以优化器选择了索引扫描)(5) CBO 模式下表很久没分析,表的增长明显,优化器采取了全表扫描。SQL select * from test.testindex where a like 1%;A B- -1 21 110 1011 1112 1213 1314 1415 1516 1617 1718 1819 19100 100已选择 13 行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)1 0 TABLE ACCESS (FULL) OF TESTIN
31、DEX (Cost=1 Card=13 Bytes=52) (表一共 102 行,选择比例为 13/10210%,优化器选择了全表扫描)增加表行数SQL declare i number;2 begin3 for i in 200 1000 loop4 insert into test.testindex values (to_char(i),i);5 end loop;6 end;7 /PL/SQL 过程已成功完成。SQL commit;提交完成。SQL select count(*) from test.testindex;COUNT(*)-903SQL select * from tes
32、t.testindex where a like 1%;A B- -1 21 110 1011 1112 1213 1314 1415 1516 1617 1718 1819 19100 1001000 1000已选择 14 行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)1 0 TABLE ACCESS (FULL) OF TESTINDEX (Cost=1 Card=13 Bytes=52)(表一共 903 行,选择比例为 14/903 analyze table test.test
33、index compute statistics for table for all indexed columns for all indexes;表已分析。SQL select * from test.testindex where a like 1%;A B- -1 21 110 10100 1001000 100011 1112 1213 1314 1415 1516 1617 1718 1819 19已选择 14 行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=24 Bytes=120)1 0 TAB
34、LE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost=4 Card=24 Bytes=120)2 1 INDEX (RANGE SCAN) OF IND_COLA (NON-UNIQUE) (Cost=2 Card=24)(经过分析后优化器选择了正确的路径,使用了 ind_cola 索引)小结这篇文章介绍了 oracle 数据库中的各种索引,通过分析它们的结构,我们知道了它们的特点和应用范围,同时介绍了如何去避免索引失效,希望对大家有所帮助。-oracle 表分区详解此文从以下几个方面来整理关于分区表的概念及操作:1.表空间及分区表的概念2.表分区的具体作
35、用3.表分区的优缺点4.表分区的几种类型及操作方法5.对表分区的维护性操作.(1.) 表空间及分区表的概念表空间:是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,但主要存放的是表, 所以称作表空间。分区表: 当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上) ,这样查询数据时,不至于每次都扫描整张表。( 2).表分区的具体作用Oracle 的表分区功能通过改善可管理性、性能和可用性,从而为各式应用程序带来了极大的好处。通常,分区可以
36、使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务,分区是构建千兆字节数据系统或超高可用性系统的关键工具。 分区功能能够将表、索引或索引组织表进一步细分为段,这些数据库对象的段叫做分区。每个分区有自己的名称,还可以选择自己的存储特性。从数据库管理员的角度来看,一个分区后的对象具有多个段,这些段既可进行集体管理,也可单独管理,这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是,从应用程序的角度来看,分区后的表与非分区表完全相同,使用 SQL DML 命令访问分区后的表时,无需任何修改。什么时候使用分区表:1、表的大小超过 2GB。2、表中包含历史数据,新的数据
37、被增加都新的分区中。(3).表分区的优缺点表分区有以下优点: 1、改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。2、增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;3、维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可; 4、均衡 I/O:可以把不同的分区映射到磁盘以平衡 I/O,改善整个系统性能。缺点: 分区表相关:已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。(4).表分区的几种类型及操作方法一.范围分区:范围分区将数据基于范围映射到每一个分区,这个范围是你在创建分区时指定的分区键决定的。这
38、种分区方式是最为常用的,并且分区键经常采用日期。举个例子:你可能会将销售数据按照月份进行分区。当使用范围分区时,请考虑以下几个规则:1、每一个分区都必须有一个 VALUES LESS THEN 子句,它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。2、所有分区,除了第一个,都会有一个隐式的下限值,这个值就是此分区的前一个分区的上限值。3、在最高的分区中,MAXVALUE 被定义。MAXVALUE 代表了一个不确定的值。这个值高于其它分区中的任何分区键的值,也可以理解为高于任何分区中指定的 VALUE LESS THEN 的值,同
39、时包括空值。例一:假设有一个 CUSTOMER 表,表中有数据 200000 行,我们将此表通过 CUSTOMER_ID 进行分区,每个分区存储 100000 行,我们将每个分区保存到单独的表空间中,这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码,如下:CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONE VARCHAR2(15) NOT NULL, EMAIL
40、 VARCHAR2(80), STATUS CHAR(1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 )例二:按时间划分CREATE TABLE ORDER_ACTIVITIES ( ORDER_ID NUMBER(7) NOT NULL, ORDER_DATE DATE, TOTAL_AMOUNT
41、NUMBER, CUSTOTMER_ID NUMBER(7), PAID CHAR(1) ) PARTITION BY RANGE (ORDER_DATE) ( PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE(01- MAY -2003,DD-MON-YYYY) TABLESPACEORD_TS01,PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(01-JUN-2003,DD-MON-YYYY) TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02
42、VALUES LESS THAN (TO_DATE(01-JUL-2003,DD-MON-YYYY) TABLESPACE ORD_TS03 )例三:MAXVALUECREATE TABLE RangeTable( idd INT PRIMARY KEY , iNAME VARCHAR(10), grade INT ) PARTITION BY RANGE (grade) ( PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb, PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb );二.列表分区:该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。例一CREATE TABLE PROBLEM_TI