ORACLE_索引篇分析

1、Oracle 索引索引RDM 吴桂林索引的概念 索引是与表关联的可选独立对象,提高查询速度 通过默认的键值排序来取代全表扫描，提高查询效率 索引是以二叉树的机构存储的，叶节点中存储的是表中数据行的rowid（数据的物理地址）B树索引位图索引索引优缺点 优点：提高查询速度，含索引条件的select，update，delete；提高分组排序的速度 缺点：创建和维护索引需要耗费时间，随着数据量的增加而增加；索引需要物理空间，对表中的数据进行insert，update，delete时，索引要进行动态维护索引的类型 1. B_tree单列索引 2. B_tree复合索引 3.位图索引 4.函数索引 5.

2、反向索引 6.分区索引和全局索引B_tree单列索引 基于单个列创建的B_TREE索引，是oracle默认的索引类型 由于索引是通过rowid来访问数据的，当范围扫描的数据占总数据量的10%以上时，使用索引的消耗不如全表扫描（全表扫描是多块读取，索引扫描每次有两次IO，一次对索引块，一次对数据）示例1select /*+full(a)*/a.passwd from t_userinfo a 2 where phonenumber =and phonenumber select /*+index(a pk_t_userinfo)*/a.passwd from t_use

3、rinfo a 2 where phonenumber =and phonenumber ”“ select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=ID_LOCAL;INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS- - - -ID_LOCAL P1 10000 USABLEID_LOCAL P2 20000 USABLEID_LOCAL P3 MAXVALUE USABLE全局分区索引 全局分区索引

4、是对整个分区表建立的索引，然后由oracle对索引进行分区，索引分区与分区表之间不是简单的一对一关系全局分区索引删除id_local索引drop index id_local;重新在ID列上创建一个GLOBAL的索引create index id_global on test(id) global;SQL select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=ID_GLOBAL;no rows selectedSQL select INDEX_NAME,INDEX_

5、TYPE,TABLE_NAME from dba_indexes where index_name=ID_GLOBAL;INDEX_NAME INDEX_TYPE TABLE_NAME- - -ID_GLOBAL NORMAL TEST从上面可以看出，它此时是个普通索引。dba_ind_partitions里根本就没有记录。全局分区索引SQLcreate index i_id_global on test(data) global partition by range(id) ( partition p1 values less than (10000) , partition p2 valu

6、es less than (MAXVALUE) ); partition by range(id) *ERROR at line 2:ORA-14038: GLOBAL partitioned index must be prefixed此错误表示GLOBAL的索引必须是prefixed，即索引分区的列，必须是其基表的分区列。SQLcreate index id_global on test(id) global partition by range(id) ( partition p1 values less than (10000) , partition p2 values less t

7、han (MAXVALUE) );Index created.SQL select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name=ID_GLOBAL;INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS- - - -ID_GLOBAL P1 10000 USABLEID_GLOBAL P2 MAXVALUE USABLE典型索引失效1、在索引列上使用函数。如SUBSTR,DECODE,INSTR等，对索引列进行运算.需要建立函数索引就可以

8、解决了。2、新建的表还没来得及生成统计信息，分析一下就好了3、基于cost的成本分析，访问的表过小，使用全表扫描的消耗小于使用索引。4、使用、not in 、not exist，对于这三种情况大多数情况下认为结果集很大，一般大于5%-15%就不走索引而走FTS。5、单独的、。6、like %_ 百分号在前。7、单独引用复合索引里非第一位置的索引列。8、字符型字段为数字时在where条件里不添加引号。9、当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。10、索引失效，可以考虑重建索引，rebuild online。11、B-tree索引 is null不会走,is n

9、ot null会走,位图索引 is null,is not null 都会走、联合索引 is not null 只要在建立的索引列（不分先后）都会走。创建索引的注意 1、一般来说，不需要为比较小的表创建索引； 2、即使是大表，如果经常需要查询的数据不超过10%到15%的话，那就没有必要为其建立索引的必要。因为此时建立索引的开销可能要比性能的改善大的多。这个比例只是一个经验的数据。如果数据库管理员需要得出一个比较精确的结论，那么就需要进行测试分析。 3、如对于一些重复内容比较少的列，特别是对于那些定义了唯一约束的列。在这些列上建立索引，往往可以起到非常不错的效果。如对于一些null值的列与非Nu

10、ll值的列混合情况下，如果用户需要经常查询所有的非Null值记录的列，则最好为其设置索引。如果经常需要多表连接查询，在用与连接的列上设置索引可以达到事半功倍的效果。创建索引的注意 4、数据库管理员，需要隔一段时间，如一年，对数据库的索引进行优化。该去掉的去掉，该调整的调整，以提高数据库的性能。 5、通常来说，表的索引越多，其查询的速度也就越快。但是，表的更新速度则会降低。这主要是因为表的更新(如往表中插入一条记录)速度，反而随着索引的增加而增加。这主要是因为，在更新记录的同时需要更新相关的索引信息。为此，到底在表中创建多少索引合适，就需要在这个更新速度与查询速度之间取得一个均衡点。创建索引的注

11、意6、对于一些数据仓库或者决策型数据库系统，其主要用来进行查询。相关的记录往往是在数据库初始化的时候倒入。此时，设置的索引多一点，可以提高数据库的查询性能。同时因为记录不怎么更新，所以索引比较多的情况下，也不会影响到更新的速度。即使在起初的时候需要导入大量的数据，此时也可以先将索引禁用掉。等到数据导入完毕后，再启用索引。可以通过这种方式来减少索引对数据更新的影响。相反，如果那些表中经常需要更新记录，如一些事务型的应用系统，数据更新操作是家常便饭的事情。此时如果在一张表中建立过多的索引，则会影响到更新的速度。7、关于位图索引。基数是位图索引中的一个基本的定义，它是指数据库表中某个字段内容中不重复

12、的数值。如在员工信息表中的性别字段，一般就只有男跟女两个值，所以，其基数为2;婚姻状况字段的话，则其只有已婚、未婚、离婚三种状态，其基数就为3;民族一览内也是只有有限的几个值Oracle创建索引的基本规则选择索引字段的原则:在WHERE子句中最频繁使用的字段 联接语句中的联接字段 选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好) Oracle在UNIQUE和主键字段上自动建立索引 在选择性很差的字段上建索引只有在这个字段的值分布非常倾斜的情况下才有益(在这种情况下,某一,两个字段值比其它字段值少出现很多) 不要在很少独特值的字段上建B-TREE索引,在这种情况下,你

13、可以考虑在这些字段上建位图索引.在联机事务处理环境下,并发性非常高,索引经常被修改,所以不应该建位图索引 不要在经常被修改的字段上建索引.当有UPDATE,DELETE,INSETT操作时,ORACLE除了要更新表的数据外,同时也要更新索引,而且就象更新数据一样,或产生还原和重做条目 不要在有用到函数的字段上建索引,ORACLE在这种情况,优化器不会用到索引,除非你建立函数索引 当建立索引后,请比较一下索引后所获得的查询性能的提高和UPDATE,DELETE,INSERT操作性能上的损失,比较得失后,再最后决定是否需建立这个索引 Oracle创建索引的基本规则复合索引的优点:改善选择性:复合索

14、引比单个字段的索引更具选择性 减少I/O:如果要查询的字段刚好全部包含在复合索引的字段里,则ORACLE只须访问索引,无须访问表 什么情况下优化器会用到复合索引呢? (a) 当SQL语句的WHERE子句中有用到复合索引的领导字段时,ORACLE优化器会考虑用到复合索引来访问. (b) 当某几个字段在SQL语句的WHERE子句中经常通过AND操作符联合在一起使用作为过滤谓词,并且这几个字段合在一起时选择性比各自单个字段的选择性要更好时,可 能考虑用这几个字段来建立复合索引. (c) 当有几个查询语句都是查询同样的几个字段值时,则可以考虑在这几个字段上建立复合索引.复合索引字段排序的原则:确保在W

15、HERE子句中使用到的字段是复合索引的领导字段 如果某个字段在WHERE子句中最频繁使用,则在建立复合索引时,考虑把这个字段排在第一位(在CREATE INDEX语句中) 如果所有的字段在WHERE子句中使用频率相同,则将最具选择性的字段排在最前面,将最不具选择性的字段排在最后面 如果所有的字段在WHERE子句中使用频率相同,如果数据在物理上是按某一个字段排序的,则考虑将这个字段放在复合索引的第一位 Oracle创建索引的基本规则四、建立索引常用的规则如下表的主键、外键必须有索引； 数据量超过300的表应该有索引； 经常与其他表进行连接的表，在连接字段上应该建立索引； 经常出现在Where子句

16、中的字段，特别是大表的字段，应该建立索引； 索引应该建在选择性高的字段上； 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： A、正确选择复合索引中的主列字段，一般是选择性较好的字段； B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引； C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引； D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段； E、如果既有单字段索引

17、，又有这几个字段上的复合索引，一般可以删除复合索引；频繁进行数据操作的表，不要建立太多的索引； 删除无用的索引，避免对执行计划造成负面影响； 不走索引不走索引的几种情况1. 隐式的类型转换例：fnumber是字符型，但是查询时使用数字型select * from t_lea_waybill where fnumber = 122355722. 符号的查询例：select * from wlbussiness where id 12030001 and id select * from tbl where userid = 100;执行计划-Plan hash value: 1167568666

18、-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| TBL | 1 | 38 | 2 (0)| 00:00:01 |* 2 | INDEX UNIQUE SCAN | TBLID | 1 | | 1 (0)| 00:00:01 |-从上面的执行计划可以看出，优化器首先是根据为刚才建立的索引TBLID来找到100的ROWID，然后根据ROWID去找到10

19、0所在的行数据。示例 index full scan 为上面的表增加一个联合索引，在TBL.NAME和TBL.DEPTNO两个列上，SQL如下： CREATE INDEX INDEX_TBL_NAME_DEPTNO ON TBL(NAME,DEPTNO); Select NAME,DEPTNO from tbl示例index range scan执行下面的语句：ChenZw select * from tbl where userid between 10 and 100;已选择91行。执行计划-Plan hash value: 2314926374-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 91 | 3458 | 3 (0)| 00:00:01 | 1 |