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

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Oracle,优化器介绍,.,Oracle,索引介绍,.,SQL,优化规则介绍,.,索引优化规则介绍,.,Oracle,优化器模式,:,Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer) 来完成的 .,Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具.,Oracle的优化器有两种优化方式：基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO).,RBO方式优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引.,CBO方式CBO是看语句的代价,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的.在Oracle8及以后的版本,Oracle列推荐用CBO的方式。,优化器的优化模式,(Optermizer Mode) :,包括,Rule,Choose,First rows,All rows,这四种方式,.,Rule:,不用多说,即走基于规则的方式。,rbo,Choose:,指的是当一个表或或索引有统计信息,则走,CBO,的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走,RBO,的方式。,First Rows:,它与,Choose,方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。对于排序分页页显示这种查询尤其适用,.,All Rows:,也就是我们所说的,Cost,的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式,.,查看缺省的,Oracle,优化器,:,SQLshow parameters optimizer_mode;,可以在,init,文件中对整个,instance,的所有会话设置,.,也可以单独对某个会话设置：,SQLALTER SESSION SET optimizer_mode,= FIRST_ROWS;,查看统计信息：,1.select tt.table_name,tt.num_rows,tt.blocks,tt.empty_blocks,tt.avg_row_len from dba_tables tt where tt.owner=SCOTT;,2.select ttt.index_name,ttt.num_rows,ttt.distinct_keys,ttt.avg_leaf_blocks_per_key,ttt.clustering_factor from dba_indexes ttt where ttt.owner=SCOTT;,人工进行统计：,对某一个用户下的所有表和索引执行统计分析,:,execute dbms_stats.gather_schema_stats(ownname =SCOTT,cascade=true);,对单个表执行统计分析,:,EXECUTE dbms_stats.gather_table_stats (ownname=SCOTT, tabname=EMP,estimate_percent=50,cascade=true).,从,Oracle Database 10g,开始，,Oracle,在建库后就默认创建了一个名为,GATHER_STATS_JOB,的定时任务，用于自动收集,CBO,的统计信息,调用,DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC,收集统计信息,.,默认情况下在工作日晚上,10,：,00-6,：,00,和周末全天开启,.,一起运行的还有另外一个,Job:AUTO_SPACE_ADVISOR_JOB .,可以通过以下查询这个,JOB,的运行情况：,select * from Dba_Scheduler_Jobs where JOB_NAME =GATHER_STATS_JOB .,关闭自动统计功能：,SQL exec BMS_SCHEDULER.DISABLE(GATHER_STATS_JOB);,Oracle,索引介绍,Oracle,索引简介,索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据,(,加快查询,),；,索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的,rowid,。,查询,DBA_INDEXES,视图可得到表中所有索引的列表，注意只能通过,USER_INDEXES,的方法来检索模式,(schema),的索引。访问,USER_IND_COLUMNS,视图可得到一个给定表中被索引的特定列。,通过每个行的,ROWID,，索引,Oracle,提供了访问单行数据的能力。,ROWID,其实就是直接指向单独行的线路图。,索引分类,逻辑上,:,单列索引,多列索引,唯一索引,非惟一索引。,物理上,:,B*-Tree,索引，反向索引，位图索引。,单列索引和复合索引,单列索引是基于单个列所建立的索引。多列索引是基于两列或多列所建立的索引。,单列索引,Create index emp_ind1 on emp(ename);,复合索引,Create index emp_ind2 on emp(ename,job);,惟一索引和非惟一索引,惟一索引是索引列值不能重复的索引。,非惟一索引是索引列值可以重复的索引。无论是惟一索引还是非惟一索引，索引列都允许NULL。,B*-tree,索引,B*Tree,索引是最常见的索引结构，默认建立的索引就是这种类型的索引。,B*Tree,索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时,B-Tree,索引比全表检索提供了更有效的方法。但当检查的范围超过表的,10%,时就不能提高取回数据的性能。,B-Tree,索引是基于二叉树的，由分支块（,branch block,）和叶块（,leaf block,）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的,rowid,。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址,.,创建索引：,1: create index STUDENT_IND_name on STUDENT(NAME) tablespace USERS ;,2:create index STUDENT_IND_name on STUDENT(NAME,AGE) tablespace USERS ;,B*-tree,索引,B*-tree,索引,假设我们要找索引中值为,80,的行，从索引树的最上层入口开始，定位到大于等于,50,，然后往左找，找到第,2,个分支块，定位为,75,100,，最后再定位到叶块上，找到,80,所对应的,rowid,，然后根据,rowid,去读取数据块获取数据。如果查询条件是范围选择的，比如,where column 20 and column value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。注意：只有索引中的值是逆向的，表中的值保持不变。,位图索引,位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低,cardinality,列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“,Male”,，“,Female”,，“,Null”,等,3,种，但一共有,300,万条记录，那么,3/3000000,约等于,0,，这种情况下最适合用位图索引。,创建位图索引,:,create bitmap index inx_bitmap_emp on emp(sex);,位图索引的格式,行值 12345678910 Male 1000000011 Female0111001100 Null 0000110000,位图索引,如果搜索,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,e loc),和表,emp(empno, ename, job, mgr, . deptno),常在,deptno,上进行连接,可以针对,deptno,字段建立聚簇，然后建立基于该聚簇的索引，并让两个表都使用上该聚簇。,Oracle,聚簇索引,CREATE CLUSTER,personnel( department_number NUMBER(2) ),SIZE 512 STORAGE (INITIAL 100K NEXT 50K);,CREATE TABLE emp (,empno NUMBER,PRIMARY KEY,ename VARCHAR2(10) NOT NULL,CHECK (ename = UPPER(ename),job VARCHAR2(9),mgr NUMBER ,comm NUMBER(9,0) DEFAULT NULL,deptno NUMBER(2) NOT NULL ),CLUSTER personnel (deptno);,CREATE TABLE dept (,deptno NUMBER(2),dname VARCHAR2(9),loc VARCHAR2(9),CLUSTER personnel (deptno);,CREATE INDEX idx_personnel ON CLUSTER personnel;,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,个以上的表连接查询,那就需要选择交叉表,(intersection table),作为基础表,交叉表是指那个被其他表所引用的表,.,原因：,ORACLE,的解析器按照从右到左的顺序处理,FROM,子句中的表名,因此,FROM,子句中写在最后的表,(,基础表,driving table),将被最先处理,.,在,FROM,子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表,.,当,ORACLE,处理多个表时,会运用排序及合并的方式连接它们,.,首先,扫描,第一个表,(FROM,子句中最后的那个表,),并对记录进行排序,然后扫描第二个表,(FROM,子句中最后第二个表,),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并,.,2 WHERE,子句中的连接顺序 ：,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.,当删除表中的记录时,在通常情况下,回滚段,(rollback segments ),用来存放可以被恢复的信息,.,如果你没有,COMMIT,事务,ORACLE,会将数据恢复到执行删除命令之前的状况,.,而当运用,TRUNCATE,时,回滚段不再存放任何可被恢复的信息,.,当命令运行后,数据不能被恢复,.,因此很少的资源被调用,执行时间也会很短,. (TRUNCATE,只在删除全表里的记录时适用,TRUNCATE,是,DDL,不是,DML),7. (,可能的话,),使用,COMMIT,只要有可能,在程序中尽量多使用,COMMIT,这样程序的性能得到提高,需求也会因为,COMMIT,所释放的资源而减少,: COMMIT,所释放的资源,: a.,回滚段上用于恢复数据的信息,. b.,被程序语句获得的锁,c. redo log buffer,中的空间,d. ORACLE,为管理上述,3,种资源中的内部花费,8.(,可能的话,),用,Where,子句替换,HAVING,子句,尽量少使用,HAVING,子句, HAVING,只会在检索出所有记录之后才对结果集进行过滤,.,这个处理需要排序,总计等操作,.,如果能通过,WHERE,子句限制记录的数目,那就能减少这方面的开销,.,9.(,某些情况下,),可以用,EXISTS,替代,IN . NOT EXISTS,替代,NOT IN,性能比较,:,1.Select * from t1 where x in ( select y from t2),2.select * from t1 where,exists ( select 1 from t2 where t2. y = t1.x ),当,t1,记录比较少,t2,比较大时适合用,exists(exists,大部分情况会利用到,index),当子查询记录集很小时用,in,比较合适,.,原因分析：,1.Select * from T1 where x in ( select y from T2 ),执行的过程相当于,: select * from t1, ( select distinct y from t2 ) t3 where t1.x = t3.y;,2. select * from t1 where exists ( select 1 from t2 where t2.y = t1.x ),执行的过程相当于,:for x in ( select * from t1 ) loop if ( exists ( select 1 from t2 where t2.y = t1.x ) then OUTPUT THE RECORD end ifend loop,这样表,T1,要被完全扫描一遍,.,所以可以得出结论,:,当,t1,记录比较少,t2,比较大时适合用,exists(exists,大部分情况会利用到,index),当子查询记录集很小时用,in,比较合适,.,10.,用表连接替换,EXISTS,改进第,9,打优化规则的例子,.,11.,用,EXISTS,替换,DISTINCT,EXISTS,使查询更为迅速,因为,RDBMS,核心模块在子查询的条件一旦满足后,立刻返回结果,. DISTINCT,会先进行排序,然后会根据排序后的顺序去除相同的行,.,12.,使用显式的游标,(CURSOR),使用隐式的游标,将会执行两次操作,.,第一次检索记录,第二次检查,TOO MANY ROWS,这个,exception .,而显式游标不执行第二次操作,.,11-,例：,1.(,低效,)SELECT DISTINCT DEPT_NO,DEPT_NAME,FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO ;,2.,高效,: SELECT DEPT_NO,DEPT_NAME,FROM DEPT D WHERE EXISTS ( SELECT * FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);,13.,用索引提高效率,通常,通过索引查询数据比全表扫描要快,.,当,ORACLE,找出执行查询和,Update,语句的最佳路径时, ORACLE,优化器将使用索引,.,除了那些,LONG,或,LONG RAW,数据类型,你可以索引几乎所有的列,.,在大型表中使用索引特别有效,.,虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价,.,索引需要空间来 存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改,.,这意味着每条记录的,INSERT , DELETE , UPDATE,将为此多付出,4 , 5,次的磁盘,I/O .,因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢,.,定期的重构索引是有必要的,.,ALTER INDEX REBUILD ,Oracle,索引优化规则,索引优化规则：,1. like,件中不要以通配符,(WILDCARD),开始,否则索引将不被采用,.,例,:SELECT LODGING FROM LODGING,WHERE MANAGER LIKE ,HANMAN;,2.,避免在索引列上使用计算或改变索引列的类型或使用,!=,及,例,: SELECT FROM DEPT WHERE SAL * 12 25000;,SELECT FROM EMP WHERE EMP_TYPE=to_char(123); select . Where ACCOUNT_NAME|ACCOUNT_TYPE=AMEXA;,select where empno!=8888 ;,3.,避免在索引列上使用,NOT .,4.,用,=,替代, .,高效,: SELECT * FROM EMP WHERE DEPTNO =4,低效,: SELECT * FROM EMP WHERE DEPTNO 3,两者的区别在于,前者,DBMS,将直接跳到第一个,DEPT,等于,4,的记录而后者将首先定位到,DEPTNO=3,的记录并且向前扫描到第一个,DEPT,大于,3,的记录,.,5.,用,UNION,替换,OR (,适用于索引列,),通常情况下,用,UNION,替换,WHERE,子句中的,OR,将会起到较好的效果,.,对索引列使用,OR,将造成全表扫描,.,注意,以上规则只针对多个索引列有效,.,如果有,column,没有被索引,查询效率可能会因为你没有选择,OR,而降低,.,在下面的例子中, LOC_ID,和,REGION,上都建有索引,.,高效,: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”,低效,: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”,如果你坚持要用,OR,那就需要返回记录最少的索引列写在最前面,注意：,WHERE KEY1 = 10,（返回最少记录）,OR KEY2 = 20,（返回最多记录）,ORACLE,内部将以上转换为,WHERE KEY1 = 10 AND,（,NOT KEY1 = 10,）,AND KEY2 = 20,）,6.,避免在索引列上使用,IS NULL,和,IS NOT NULL,避免在索引中使用任何可以为空的列，,ORACLE,将无法使用该索引 对于单列索引，如果列包含空值，索引中将不存在此记录,.,对于复合索引，如果每个列都为空，索引中同样不存在此记录,.,如果至少有一个列不为空，则记录存在于索引中,(,建议：可以给,null,值的字段设置一个默认值,),7.,如果索引是建立在多个列上,索引时段需要放在,where,条件的第一个条件,(Oracle8i,之前,),Oracle8i,之后允许跳跃式索引,.,8. (,可能的话,),用,UNION-ALL,替换,UNION.,UNION-ALL,就是做简单的合并，不会进行排序，,UNION,先做简单的合并，然后做进行排序，最后去除重复的记录。,9.,避免使用耗费资源的操作,带有,DISTINCT,UNION ,MINUS,INTERSECT,ORDER BY,的,SQL,语句会启动,SQL,引擎,.,执行耗费资源的排序,(SORT),功能,. DISTINCT,需要一次排序操作,而其他的至少需要执行两次排序,.,例如,一个,UNION,查询,其中每个查询都带有,GROUP BY,子句, GROUP BY,会触发嵌入排序,(NESTED SORT) ;,这样,每个查询需要执行一次排序,然后在执行,UNION,时,又一个唯一排序,(SORT UNIQUE),操作被执行而且它只能在前面的嵌入排序结束后才能开始执行,.,嵌入的排序的深度会大大影响查询的效率,.,通常,带有,UNION, MINUS , INTERSECT,的,SQL,语句都可以用其他方式重写,.,the end!,