Oracle的一些基本索引

Oracle的一些基本索引



Oracle复合索引介绍(多字段索引)

 首先,在大多数情况下,复合索引比单字段索引好.以税务系统的SB_ZSXX(申报类_征收信息表)为例,该表为税务系统最大的交易表.如果分别按纳税人识别号,税务机关代码,月份3个字段查询,每个字段在该表中的可选性或约束性都不强,如一个纳税人识别号有很多纳税记录,一个税务机关代码和同一月份记录就更多了,所以3个字段合起来,”某个纳税人识别号+某个税务机关代码+某月”的记录就少多了.因此复合索引比单字段索引的效率高多了.很多系统就是靠新建一些合适的复合索引,使效率大幅度提高.

      但是,复合索引比单字段索引的内容原理复杂,复合索引有两个重要原则需要把握: 前缀性和可选性.如果糊里糊涂的滥用复合索引,效果适得其反.

以例子来说明,例子如下:

      假设在员工表(emp)的(ename,job,mgr)3个字段上建了一个索引,例如索引名叫idx_1.3个字段分别为员工姓名,工作和所属经理号.然后,写如下一个查询语句,并不断进行查询条件和次序的排列组合,例如:

Sql代码  Oracle的一些基本索引

  1. select * from emp where ename = ‘a’ and job = ‘b’ and mgr = 3 ;  
  2. select * from emp where job = ‘b’ and ename = ‘a’ and mgr = 3 ;  
  3. select * from emp where mgr = 3 and ename = ‘a’ and job = ‘b’ ;  
  4. select * from emp where mgr = 3 and job = ‘b’ and ename = ‘a’ ;  
  5. select * from emp where job = ‘b’ and mgr = 3 and ename = ‘a’ ;  
  6. …..  

回答问题:在各种条件组合情况下,刚才建的索引(idx_1) 是用还是不用?也就是说对emp表的访问是全表扫描还是按索引(idx_1)访问?

答案是 :  上述语句中只要有ename=’a’条件,就能用上索引(ind_1),而不是全表扫描(这就是复合索引的前缀性).

复合索引的原理和设计建议

1.复合索引的第一个建议: 前缀性(Prefixing)

     先从例子说起.假设省,市,县分别用3个字段存储数据,并建立了一个复合索引.请记住: oracle索引,包括复合索引都是排序的.例如该复合索引在数据库索引树上是这样排序的,即先按省排序,再按市排序,最后按县排序:

省  市  县
北京  北京  东城
北京  北京  西城
北京  北京  海淀
… …
黑龙江  哈尔滨  道里区
黑龙江  哈尔滨  道外区
黑龙江  哈尔滨  香坊区
… …
黑龙江  齐齐哈尔 龙沙区
黑龙江  齐齐哈尔 铁锋区
黑龙江  齐齐哈尔 富拉尔基区
… …
湖南  长沙  芙蓉区
湖南  长沙  岳路区
湖南  长沙  开福区
… …

oracle不是智能的,它只会按图索骥,该索引结构是先按省排序的,所以只要给出省名,就能使用索引.如果没有省名,oracle就成了无头苍蝇,乱找一气,变成了全表扫描了.例如,如果你只给一个县条件,如”开福区”,oracle肯定不会使用该索引了.

2.关于skip scan index

有时候复合索引第一个字段没有在语句中出现,oralce也会使用该索引.对,这叫oralce的skip scan index功能,oracle 9i才提供的.

skip scan index功能适合于什么情况呢?如果oracle发现第一个字段值很少的情况下,例如假设emp表有gender(性别)字段,并且建立了(gender,ename,job,mgr)复合索引.因为性别只有男和女,所以为了提高索引的利用率,oracle可将这个索引拆成(‘男’,ename,job,mgr),(‘女’,ename,job,mgr)两个复合索引.这样即便没有gender条件,oracle也会分别到男索引树和女索引树进行搜索.

但是,(gender,ename,job,mgr)索引本身设计是不合理的,它违背了复合索引的第二个原理,可选性(Selectivity),见下面描述.

3.复合索引的第二个原理:可选性(Selectivity)

您可能会问:复合索引中如何排序字段顺序?这时就要用到复合索引的第二个原理:可选性(Selectivity)规则.oracle建议按字段可选性高低进行排序,即字段值多的排在前面.例如,(ename,job,mgr,gender),(县,市,省).这是因为,字段值多,可选性越强,定位的记录越少,查询效率越高.例如,全国可能只有一个”开福区”,而湖南省的记录则太多了.

4.复合索引设计建议

(1).分析SQL语句中的约束条件字段.

(2).如果约束条件字段比较固定,则优先考虑创建针对多字段的普通B*树复合索引.如果同时涉及到月份,纳税人识别号,税务机关代码3个字段的条件,则可以考虑建立一个复合索引.

(3).如果单字段是主键或唯一字段,或者可选性非常高的字段,尽管约束条件比较固定,也不一定要建成复合索引,可建成单字段索引,降低复合索引开销.

(4).在复合索引设计中,需首先考虑复合索引的第一个设计原理:复合索引的前缀性.即在SQL语句中,只有将复合索引的第一个字段作为约束条件,该复合索引才会启用.

(5).在复合索引设计中,其实应考虑复合索引的可选性.即按可选性高低,进行复合索引字段的排序.例如上述索引的字段排序顺序为:纳税人识别号,税务机关代码,月份.

(6).如果条件涉及的字段不固定,组合比较灵活,则分别为月份,税务机关代码和纳税人识别号3个字段建立索引.

(7).如果是多表连接SQL语句,注意是否可以在被驱动表(drived table)的连接字段与该表的其他约束条件字段上创建复合索引.

(8).通过多种SQL分析工具,分析执行计划以量化形式评估效果.



B树索引

(B-tree indexes)

说明

B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引,也可以是组合/复合(多个列)的索引。B树索引最多可以包括32列。Oracle会穿过树枝块(branch block),到达包含有ROWID的树叶块。在每个树枝块中,树枝行包含链中下一个块的ID号。树叶块包含索引值ROWID,以及指向前一个和后一个树叶块的指针Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引,而其中列上包含NULL值,这一行就会处于包含NULL值的索引列中,且将被处理为空(视为NULL)

特点

索引列的值都存储在索引中。因此,可以建立一个组合(复合)索引,这些索引可以直接满足查询,而不用访问表。这就不用从表中检索数据,从而减少了I/O量。

技巧

适合与大量的增、删、改(OLTP);
不能用包含OR操作符的查询;
适合高基数的列(唯一值多)
典型的树状结构;
每个结点都是数据块;
大多都是物理上一层、两层或三层不定,逻辑上三层;
叶子块数据是排序的,从左向右递增;
在分支块和根块中放的是索引的范围;

1、针对唯一值或小范围的数据的访问时非常快。特别适合与精度匹配查询与范围查询

2B*树可以自动进行平衡

3、虽然过多的B树索引会影响DML操作,但是单个B*树索引对DML操作的影响是很小的

4、大多数情况下,B*树索引可以随着数据量的增长而很好的进行扩展

B*树单字段索引使用建议

1、分析SQL语句中约束条件的字段

2、如果条件字段不固定,可以针对单字段建立普通B*树索引

3、针对可选性高的字段建立索引

4、如果是多表连接,可以考虑在被驱动表上的连接字段加索引

5、通过对SQL语句进行分析,查看执行计划来进行优化

语句

create index 索引名 on 表名 (列名) tablespace 空间;

create index 索引名 on 表名(列名1,列名2) tablespace 表空间;

位图索引

(Bitmap indexes)

说明

位图索引非常适合于决策支持系统(Decision Support SystemDSS)和数据仓库,它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列,但通常它们都只用于少量的列。当一个表内包含了多个位图索引时,如果有多个可用的位图索引,Oracle就可以合并从每个位图索引得到的结果集,快速删除不必要的数据。位图索引存储为压缩的索引值,其中包含了一定范围的ROWID,因此Oracle必须针对一个给定值定所有范围内的ROWID。这种锁定类型可能在某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。

技巧

对于有较低基数的列需要使用位图索引。性别列就是这样一个例子,它有两个可能值:男或女(基数仅为2)位图对于低基数(少量的不同值)列来说非常快,这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引,所以非常小,因此您可以经常检索表中超过半数的行,并且仍使用位图索引。
当大多数条目不会向位图添加新的值时,位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引,在大多数事务处理应用程序中都会发生这种情况。在一个查询中合并多个位图索引后,可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。

特点

适合与决策支持系统;
UPDATE代价非常高;
非常适合OR操作符的查询;
基数比较少的时候才能建位图索引;

限制

基于规则的优化器不会考虑位图索引。
当执行ALTER TABLE语句并修改包含有位图索引的列时,会使位图索引失效。
位图索引不包含任何列数据,并且不能用于任何类型的完整性检查。
位图索引不能被声明为唯一索引。
位图索引的最大长度为30

语句

create bitmap index idx_bm_emp1_deptno on emp1(deptno);

HASH索引

(Hash Cluster indexes)

说明

使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时,也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时,所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上,并且将HASH索引作为WHERE子句中的确切匹配,Oracle就可以通过执行一个HASH函数和I/O来访问数据。通过匹配HASH列和确切的值。Oracle可以快速使用该值,基于HASH函数确定行的物理存储位置。

技巧

HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。

特点

HASH对于一些包含有序值的静态数据非常有效。

限制

集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出,然后造成额外的I/O。如果不同HASH值的数目已经被低估,您就必须在重建这个集群之后改变这个值。ALTER CLUSTER命令不能改变HASH键的数目。HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行,就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间HASH集群可能就不是最好的选择如果应用程序经常在集群表上进行全表扫描HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量,全表扫描可能非常消耗资源。
在实现HASH集群之前一定要小心。您需要全面地观察应用程序,保证在实现这个选项之前已经了解关于表和数据的大量信息。通常,HASH对于一些包含有序值的静态数据非常有效。

索引组织

说明

索引组织表会把表的存储结构改成B树结构,以表的主键进行排序。这种特殊的表和其他类型的表一样,可以在表上执行所有的DMLDDL语句。由于表的特殊结构,ROWID并没有被关联到表的行上。
对于一些涉及精确匹配和范围搜索的语句,索引组织表提供了一种基于键的快速数据访问机制。基于主键值UPDATEDELETE语句的性能也同样得以提高,这是因为行在物理上有序。由于键列的值在表和索引中都没有重复,存储所需要的空间也随之减少。

技巧

如果不会频繁地根据主键列查询数据,则需要在索引组织表中的其他列上创建二级索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表,就需要考虑使用索引组织表。

特点

可以在索引组织表上建立二级索引。

反转键索引

(Reverse Key indexes)

说明

当载入一些有序数据时,索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间,某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题,可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上
为了解决这个问题,Oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储,这些数据的值就会与原先存储的数值相反。这样,数据123412351236就被存储成432153216321结果就是索引会为每次新插入的行更新同的索引块。

技巧

如果您的磁盘容量有限,同时还要执行大量的有序载入,就可以使用反转键索引。

特点

不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。

语句

CREATE INDEX 索引名 ON 表名 (列名) reverseTABLESPACE 表空间名;

函数索引

(Function-Based indexes)

说明

可以在表中创建基于函数的索引。如果没有基于函数的索引,任何在列上执行了函数的查询都不能使用这个列的索引。

技巧

对于优化器所使用的基于函数的索引来说,必须把初始参数QUERY _REWRITE _ ENABLED设定为TRUE

1、不要轻易在字段前面加函数

2、尽量不要将字段嵌入表达式中

3、尽量减少使用函数索引,能不用就不用,因为函数索引的维护代价比普通索引高;函数索引计算值可能大于原字段值,将消耗更多的存储空间

特点

能限制在这个列上使用的函数吗?如果能,能限制所有在这个列上执行的所有函数吗
是否有足够应付额外索引的存储空间?
在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响?

限制

1、必须使用一个确定的函数定义基于该函数的索引,也就是说函数仅返回一个值

2、必须使用返回可重复值的函数来定义基于该函数的索引,如sysdate就不行

3、可以对基于函数的索引进行分区,但是对基于函数的全局分区索引来说,分区键不能是索引所基于的函数

4、函数必须使用圆括号来定义,即使没有参数

5、索引基于的函数不能包含聚合函数

语句

select * from emp where UPPER(job) = ”MGR”;
create index EMP$UPPER_JOB on emp(UPPER(job));

位图连接索引

说明

位图连接索引是基于两个表的连接的位图索引,在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时,标准方法是连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时,就不需要执行连接,因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的ROWID进一步改进性能,并且减少访问数据所需的I/O数量。

特点

位图连接的语法比较特别,其中包含FROM子句和WHERE子句,并且引用两个单独的表。索引列通常是维度表中的描述列——就是说,如果维度是CUSTOMER,并且它的主键是CUSTOMER_ID,则通常索引Customer_Name这样的列。如果事实表名为SALES,可以使用如下的命令创建索引:
create bitmap index SALES_CUST_NAME_IDX

on  SALES(CUSTOMER.Customer_Name)  from SALES, CUSTOMER
where  SALES.Customer_ID=CUSTOMER.Customer_ID;

如果用户接下来使用指定Customer_Name列值的WHERE子句查询SALESCUSTOMER表,优化器就可以使用位图连接索引快速返回匹配连接条件和Customer_Name条件的行。

限制

1只可以索引维度表中的列。

2用于连接的列必须是维度表中的主键或唯一约束;如果是复合主键,则必须使用连接中的每一列。

3不可以对索引组织表创建位图连接索引,并且适用于常规位图索引的限制也适用于位图连接索引。

语句

创建位图连接索引时,指定涉及的两个表。相应的语法应该遵循如下模式:

create bitmap index FACT_DIM_COL_IDX on FACT(DIM.Descr_Col) from FACT, DIM
where FACT.JoinCol = DIM.JoinCol;

分区索引

说明

分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断,可以访问更小的片断(也更快),并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)B树和位图索引都可以被分区,而HASH索引不可以被分区。可以有好几种分区方法:表被分区而索引未被分区表未被分区而索引被分区表和索引都被分区。不管采用哪种方法,都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性有两种类型的分区索引:本地分区索引全局分区索引。每个类型都有两个子类型,有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引,就必须是本地索引。把索引分区最主要的原因是可以减少所需读取的索引的大小,另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。在使用分区后的表和索引时,Oracle还支持并行查询和并行DML。这样就可以同时执行多个进程,从而加快处理这条语句。

本地分区索引

说明

可以使用与表相同的分区键和范围界限来对本地索引分区。每个本地索引的分区只包含了它所关联的表分区的键和ROWID。本地索引可以是B树或位图索引。如果是B树索引,它可以是唯一或不唯一的索引。
这种类型的索引支持分区独立性,这就意味着对于单独的分区,可以进行增加、截取、删除、分割、脱机等处理,而不用同时删除或重建索引。Oracle自动维护这些本地索引。本地索引分区还可以被单独重建,而其他分区不会受到影响。

语句

CREATE INDEX IDX_PT_C2 ON P_TAB(C2) LOCAL (PARTITION P1,PARTITION P2,PARTITION P3,PARTITION P4);

有前缀的索引

说明

有前缀的索引包含了来自分区键的键,并把它们作为索引的前导。

技巧

本地的有前缀索引可以让Oracle快速剔除一些不必要的分区。也就是说没有包含WHERE条件子句中任何值的分区将不会被访问,这样也提高了语句的性能。

无前缀的索引

说明

无前缀的索引并没有把分区键的前导列作为索引的前导列。如果要把无前缀的索引设为唯一索引,这个索引就必须包含分区键的子集。

技巧

对于一个唯一的无前缀索引,它必须包含分区键的子集。

全局分区索引

说明

全局分区索引在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。在创建全局分区索引时,必须定义分区键的范围和值。全局索引只能是B树索引Oracle在默认情况下不会维护全局分区索引。如果一个分区被截取、增加、分割、删除等,就必须重建全局分区索引,除非在修改表时指定ALTER TABLE命令的UPDATE GLOBAL INDEXES子句。

语句

CREATE INDEX IDX_PT_C4 ON P_TAB(C4) GLOBAL PARTITION BY RANGE(C4)  

(  

 PARTITION IP1 VALUES LESS THAN(10000),  

 PARTITION IP2 VALUES LESS THAN(20000),  

 PARTITION IP3 VALUES LESS THAN(MAXVALUE)  

);

有前缀的索引

说明

全局有前缀索引在底层表中没有经过对等分区。没有什么因素能限制索引的对等分区,但Oracle在生成查询计划或执行分区维护操作时,并不会充分利用对等分区。如果索引被对等分区,就必须把它创建为一个本地索引,这样Oracle可以维护这个索引,并使用它来删除不必要的分区,如下图所示。在该图的3个索引分区中,每个分区都包含指向多个表分区中行的索引条目。

技巧

如果一个全局索引将被对等分区,就必须把它创建为一个本地索引,这样Oracle可以维护这个索引,并使用它来删除不必要的分区。

无前缀的索引

说明

Oracle不支持无前缀的全局索引。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



About Me

……………………………………………………………………………………………………………….

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

QQ群230161599     微信群:私聊

● 联系我请加QQ好友(646634621),注明添加缘由

● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

……………………………………………………………………………………………………………….

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边二维码加入小麦苗的QQ群,学习最实用的数据库技术。

Oracle的一些基本索引Oracle的一些基本索引

Oracle的一些基本索引 Oracle的一些基本索引 Oracle的一些基本索引

本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » Oracle的一些基本索引

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址