Oracle优化器模式与Oracle索引优化规则

Oracle优化器介绍.Oracle索引介绍.SQL优化规则介绍.索引优化规则介绍.,Oracle优化器模式:Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的.Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具.Oracle的优化器有两种优化方式：基于规则的优化方式(Rule-BasedOptimization,简称为RBO)和基于代价的优化方式(Cost-BasedOptimization,简称为CBO).RBO方式优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引.CBO方式CBO是看语句的代价,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的.在Oracle8及以后的版本,Oracle列推荐用CBO的方式。,优化器的优化模式(OptermizerMode):包括Rule,Choose,Firstrows,Allrows这四种方式.Rule:不用多说,即走基于规则的方式。rboChoose:指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。FirstRows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。对于排序分页页显示这种查询尤其适用.AllRows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式.查看缺省的Oracle优化器:SQLshowparametersoptimizer_mode;可以在init文件中对整个instance的所有会话设置.也可以单独对某个会话设置：SQLALTERSESSIONSEToptimizer_mode=FIRST_ROWS;,查看统计信息：1.selecttt.table_name,tt.num_rows,tt.blocks,tt.empty_blocks,tt.avg_row_lenfromdba_tablesttwherett.owner=SCOTT;2.selectttt.index_name,ttt.num_rows,ttt.distinct_keys,ttt.avg_leaf_blocks_per_key,ttt.clustering_factorfromdba_indexestttwherettt.owner=SCOTT;人工进行统计：对某一个用户下的所有表和索引执行统计分析:executedbms_stats.gather_schema_stats(ownname=SCOTT,cascade=true);对单个表执行统计分析:EXECUTEdbms_stats.gather_table_stats(ownname=SCOTT,tabname=EMP,estimate_percent=50,cascade=true).,从OracleDatabase10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息,调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息.默认情况下在工作日晚上10：00-6：00和周末全天开启.一起运行的还有另外一个Job:AUTO_SPACE_ADVISOR_JOB.可以通过以下查询这个JOB的运行情况：select*fromDba_Scheduler_JobswhereJOB_NAME=GATHER_STATS_JOB.关闭自动统计功能：SQLexecBMS_SCHEDULER.DISABLE(GATHER_STATS_JOB);,Oracle索引介绍,Oracle索引简介,索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据(加快查询)；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。,索引分类,逻辑上:单列索引,多列索引,唯一索引,非惟一索引。物理上:B*-Tree索引，反向索引，位图索引。,单列索引和复合索引,单列索引是基于单个列所建立的索引。多列索引是基于两列或多列所建立的索引。单列索引Createindexemp_ind1onemp(ename);复合索引Createindexemp_ind2onemp(ename,job);,惟一索引和非惟一索引,惟一索引是索引列值不能重复的索引。非惟一索引是索引列值可以重复的索引。无论是惟一索引还是非惟一索引，索引列都允许NULL。,B*-tree索引,B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branchblock）和叶块（leafblock）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址.创建索引：1:createindexSTUDENT_IND_nameonSTUDENT(NAME)tablespaceUSERS;2:createindexSTUDENT_IND_nameonSTUDENT(NAME,AGE)tablespaceUSERS;,B*-tree索引,B*-tree索引,假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如wherecolumn20andcolumnvalue,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。注意：只有索引中的值是逆向的，表中的值保持不变。,位图索引,位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。创建位图索引:createbitmapindexinx_bitmap_emponemp(sex);,位图索引的格式,行值12345678910Male1000000011Female0111001100Null0000110000,位图索引,如果搜索wheregender=Male,要统计性别是”Male”的列行数的话，Oracle很快就能从位图中找到共3行即第1，9，10行是符合条件的；如果要搜索wheregender=Maleorgender=Female的列的行数的话，也很容易从位图中找到共8行即1，2，3，4，7，8，9，10行是符合条件的。如果要搜索表的值的话，那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。,聚簇,聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数据的目的。聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。一个表最多只能有一个聚簇索引。,使用Oracle聚簇索引,在Oracle当中，聚簇不是索引的组织形式，而是表的组织形式。多用于表之间的连接字段。例:dept(deptno,dnma,eloc)和表emp(empno,ename,job,mgr,.deptno),常在deptno上进行连接,可以针对deptno字段建立聚簇，然后建立基于该聚簇的索引，并让两个表都使用上该聚簇。,Oracle聚簇索引,CREATECLUSTERpersonnel(department_numberNUMBER(2)SIZE512STORAGE(INITIAL100KNEXT50K);CREATETABLEemp(empnoNUMBERPRIMARYKEY,enameVARCHAR2(10)NOTNULLCHECK(ename=UPPER(ename),jobVARCHAR2(9),mgrNUMBER,commNUMBER(9,0)DEFAULTNULL,deptnoNUMBER(2)NOTNULL)CLUSTERpersonnel(deptno);CREATETABLEdept(deptnoNUMBER(2),dnameVARCHAR2(9),locVARCHAR2(9)CLUSTERpersonnel(deptno);CREATEINDEXidx_personnelONCLUSTERpersonnel;,Oracle聚簇索引,这样可以让两个表同时用上聚簇索引。保证两个表的记录按照depno值尽量存放到同一个物理块当中。,使用索引的一些规则,1.索引对大表最有用，不要在小表上加索引.2.为每个表中的主码指定一个唯一索引。3.索引对于那些频繁出现在SQL命令中的where子句中的列最有用，不管这些列在选择中用来限定行还是为了表连接。4.当一个属性中存在很多不同的值时，可以使用索引。Oracle建议当一个属性中有少于30个不同值时，索引不是很有用，当属性中有100或更多不同值时索引就很明显地有用了。相似地，只有当使用索引进行查询的结果不超过文件中所有记录总数的20%时，使用索引才有帮助。5.检查你的DBMS对索引的限制，即便要在每个表允许的索引个数上。许多系统不超过16个索引而且限制每个索引键值的大小。对一个表创建的索引数一般不超过5个.,7.对于包含空值的属性建立索引时要小心，在很多DBMS里有空值的行不能在索引中作为参照。8.时常需要做删除、更新、插入操作的表不要创建索引.9.将表和索引建立在不同的表空间内(TABLESPACES).不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里.同时,确保数据表空间和索引表空间置于不同的硬盘上.减少I/O竞争.,SQL优化的一些规则:(有些规则只有在基于规则的优化器里有效).,1选择最有效率的表名顺序：1.把记录少的表放在from子句的最后面一个表.2.如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表.原因：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.,2WHERE子句中的连接顺序：ORACLE采用自右向左的顺序解析WHERE子句,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.3.SELECT子句中避免使用*ORACLE在解析的过程中,需要通过查询数据字典将*依次转换成所有的列名.4.使用表的别名(Alias)当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)5.减少访问数据库的次数:当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.,6.(可能的话)用TRUNCATE替代DELETE.当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到执行删除命令之前的状况.而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.(TRUNCATE只在删除全表里的记录时适用,TRUNCATE是DDL不是DML)7.(可能的话)使用COMMIT只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a.回滚段上用于恢复数据的信息.b.被程序语句获得的锁c.redologbuffer中的空间d.ORACLE为管理上述3种资源中的内部花费,8.(可能的话)用Where子句替换HAVING子句尽量少使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.9.(某些情况下)可以用EXISTS替代IN.NOTEXISTS替代NOTIN性能比较:1.Select*fromt1wherexin(selectyfromt2)2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)当t1记录比较少,t2比较大时适合用exists(exists大部分情况会利用到index),当子查询记录集很小时用in比较合适.,原因分析：1.Select*fromT1wherexin(selectyfromT2)执行的过程相当于:select*fromt1,(selectdistinctyfromt2)t3wheret1.x=t3.y;2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)执行的过程相当于:forxin(select*fromt1)loopif(exists(select1fromt2wheret2.y=t1.x)thenOUTPUTTHERECORDendifendloop这样表T1要被完全扫描一遍.所以可以得出结论:当t1记录比较少,t2比较大时适合用exists(exists大部分情况会利用到index),当子查询记录集很小时用in比较合适.,10.用表连接替换EXISTS改进第9打优化规则的例子.11.用EXISTS替换DISTINCTEXISTS使查询更为迅速,因为RDBMS核心模块在子查询的条件一旦满足后立刻返回结果.DISTINCT会先进行排序,然后会根据排序后的顺序去除相同的行.12.使用显式的游标(CURSOR)使用隐式的游标,将会执行两次操作.第一次检索记录,第二次检查TOOMANYROWS这个exception.而显式游标不执行第二次操作.,11-例：1.(低效)SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO;2.高效:SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT*FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);,13.用索引提高效率通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.除了那些LONG或LONGRAW数据类型,你可以索引几乎所有的列.在大型表中使用索引特别有效.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.定期的重构索引是有必要的.ALTERINDEXREBUILD,Oracle索引优化规则,索引优化规则：1.like件中不要以通配符(WILDCARD)开始,否则索引将不被采用.例:SELECTLODGINGFROMLODGINGWHEREMANAGERLIKEHANMAN;2.避免在索引列上使用计算或改变索引列的类型或使用!=及例:SELECTFROMDEPTWHERESAL*1225000;SELECTFROMEMPWHEREEMP_TYPE=to_char(123);select.WhereACCOUNT_NAME|ACCOUNT_TYPE=AMEXA;selectwhereempno!=8888;3.避免在索引列上使用NOT.4.用=替代.高效:SELECT*FROMEMPWHEREDEPTNO=4低效:SELECT*FROMEMPWHEREDEPTNO3两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.,5.用UNION替换OR(适用于索引列)通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.在下面的例子中,LOC_ID和REGION上都建有索引.高效:SELECTLOC_ID,LOC_DESC,REGIONFROMLOCATIONWHERELOC_ID=10UNIONSELECTLOC_ID,LOC_D