ORACLE_索引篇

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

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

3、)*/a.passwd from t_userinfo 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 USABLE ID_LOCAL P2 20000 USABLE ID_LOCAL P3 M

4、AXVALUE USABLE 全局分区索引 全局分区索引是对整个分区表建立的索引， 然后由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

5、 selected SQL select INDEX_NAME,INDEX_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

6、 values less than (10000) , partition p2 values 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 valu

7、es less than (10000) , partition p2 values less than (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 USABLE ID_GLOBAL P2 MAXVALUE USABLE 典型索引

8、失效 1、在索引列上使用函数。如SUBSTR,DECODE,INSTR等，对索引列进行运算.需 要建立函数索引就可以解决了。 2、新建的表还没来得及生成统计信息，分析一下就好了 3、基于cost的成本分析，访问的表过小，使用全表扫描的消耗小于使用索引。 4、使用、not in 、not exist，对于这三种情况大多数情况下认为结果集很大， 一般大于5%-15%就不走索引而走FTS。 5、单独的、。 6、like %_ 百分号在前。 7、单独引用复合索引里非第一位置的索引列。 8、字符型字段为数字时在where条件里不添加引号。 9、当变量采用的是times变量，而表的字段采用的是date变量

9、时.或相反情况。 10、索引失效，可以考虑重建索引，rebuild online。 11、B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走、 联合索引 is not null 只要在建立的索引列（不分先后）都会走。 创建索引的注意 1、一般来说，不需要为比较小的表创建索引； 2、即使是大表，如果经常需要查询的数据不超过10%到 15%的话，那就没有必要为其建立索引的必要。因为此 时建立索引的开销可能要比性能的改善大的多。这个比 例只是一个经验的数据。如果数据库管理员需要得出一 个比较精确的结论，那么就需要进行测试分析。

10、 3、如对于一些重复内容比较少的列，特别是对于那些 定义了唯一约束的列。在这些列上建立索引，往往可以 起到非常不错的效果。如对于一些null值的列与非Null值 的列混合情况下，如果用户需要经常查询所有的非Null 值记录的列，则最好为其设置索引。如果经常需要多表 连接查询，在用与连接的列上设置索引可以达到事半功 倍的效果。 创建索引的注意 4、数据库管理员，需要隔一段时间，如一年， 对数据库的索引进行优化。该去掉的去掉，该 调整的调整，以提高数据库的性能。 5、通常来说，表的索引越多，其查询的速度 也就越快。但是，表的更新速度则会降低。这 主要是因为表的更新(如往表中插入一条记录) 速度，反

11、而随着索引的增加而增加。这主要是 因为，在更新记录的同时需要更新相关的索引 信息。为此，到底在表中创建多少索引合适， 就需要在这个更新速度与查询速度之间取得一 个均衡点。 创建索引的注意 6、对于一些数据仓库或者决策型数据库系统，其主要用来进行 查询。相关的记录往往是在数据库初始化的时候倒入。此时， 设置的索引多一点，可以提高数据库的查询性能。同时因为记 录不怎么更新，所以索引比较多的情况下，也不会影响到更新 的速度。即使在起初的时候需要导入大量的数据，此时也可以 先将索引禁用掉。等到数据导入完毕后，再启用索引。可以通 过这种方式来减少索引对数据更新的影响。相反，如果那些表 中经常需要更新记录

12、，如一些事务型的应用系统，数据更新操 作是家常便饭的事情。此时如果在一张表中建立过多的索引， 则会影响到更新的速度。 7、关于位图索引。 基数是位图索引中的一个基本的定义，它是指数据库表中某个 字段内容中不重复的数值。如在员工信息表中的性别字段，一 般就只有男跟女两个值，所以，其基数为2;婚姻状况字段的话， 则其只有已婚、未婚、离婚三种状态，其基数就为3;民族一览 内也是只有有限的几个值 Oracle创建索引的基本规则 选择索引字段的原则: 在WHERE子句中最频繁使用的字段 联接语句中的联接字段 选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好) Oracle在UN

13、IQUE和主键字段上自动建立索引 在选择性很差的字段上建索引只有在这个字段的值分布非常倾斜的情况下才有益(在这种情况 下,某一,两个字段值比其它字段值少出现很多) 不要在很少独特值的字段上建B-TREE索引,在这种情况下,你可以考虑在这些字段上建位图索引. 在联机事务处理环境下,并发性非常高,索引经常被修改,所以不应该建位图索引 不要在经常被修改的字段上建索引.当有UPDATE,DELETE,INSETT操作时,ORACLE除了要更新表的 数据外,同时也要更新索引,而且就象更新数据一样,或产生还原和重做条目 不要在有用到函数的字段上建索引,ORACLE在这种情况,优化器不会用到索引,除非你建立

14、函数 索引 当建立索引后,请比较一下索引后所获得的查询性能的提高和UPDATE,DELETE,INSERT操作性能 上的损失,比较得失后,再最后决定是否需建立这个索引 Oracle创建索引的基本规则 复合索引的优点: 改善选择性:复合索引比单个字段的索引更具选择性 减少I/O:如果要查询的字段刚好全部包含在复合索引的字段里,则ORACLE只须访问索引,无须 访问表 什么情况下优化器会用到复合索引呢? (a) 当SQL语句的WHERE子句中有用到复合索引的领导字段时,ORACLE优化器会考虑用到复 合索引来访问. (b) 当某几个字段在SQL语句的WHERE子句中经常通过AND操作符联合在一起使

15、用作为过滤 谓词,并且这几个字段合在一起时选择性比各自单个字段的选择性要更好时,可 能考虑用这几个字段来建立复合索引. (c) 当有几个查询语句都是查询同样的几个字段值时,则可以考虑在这几个字段上建立复合 索引. 复合索引字段排序的原则: 确保在WHERE子句中使用到的字段是复合索引的领导字段 如果某个字段在WHERE子句中最频繁使用,则在建立复合索引时,考虑把这个字段排在第一位 (在CREATE INDEX语句中) 如果所有的字段在WHERE子句中使用频率相同,则将最具选择性的字段排在最前面,将最不具 选择性的字段排在最后面 如果所有的字段在WHERE子句中使用频率相同,如果数据在物理上是按

16、某一个字段排序的,则 考虑将这个字段放在复合索引的第一位 Oracle创建索引的基本规则 四、建立索引常用的规则如下 表的主键、外键必须有索引； 数据量超过300的表应该有索引； 经常与其他表进行连接的表，在连接字段上应该建立索引； 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 索引应该建在选择性高的字段上； 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： A、正确选择复合索引中的主列字段，一般是选择性较好的字段； B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询

17、是否 极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引； C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引； D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段； E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 频繁进行数据操作的表，不要建立太多的索引； 删除无用的索引，避免对执行计划造成负面影响； 不走索引 不走索引的几种情况 1. 隐式的类型转换 例：fnumber是字符型，但是查询时使用数字型 select * from t_lea_waybill where fnumber = 122

18、35572 2. 符号的查询 例：select * from wlbussiness where id 12030001 and id select * from tbl where userid = 100; 执行计划 - Plan hash value: 1167568666 - | 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 |

19、 1 | 38 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | TBLID | 1 | | 1 (0)| 00:00:01 | - 从上面的执行计划可以看出，优化器首先是根据为刚才建立的索引TBLID来找到100的ROWID，然后根据 ROWID去找到100所在的行数据。 示例 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 | TABLE