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

1、Oracle 优化器介绍.Oracle索引介绍.SQL优化规则介绍.索引优化规则介绍.Oracle优化器模式优化器模式:Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer) 来完成的 .Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具.Oracle的优化器有两种优化方式：基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO). RBO方式优化器在分析SQL语

2、句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引. CBO方式CBO是看语句的代价,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的.在Oracle8及以后的版本,Oracle列推荐用CBO的方式。优化器的优化模式(Optermizer Mode) :包括Rule,Choose,First rows,All rows这四种方式.Rule:不用多说,即走基于规则的方式。 rboCh

3、oose:指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。对于排序分页页显示这种查询尤其适用.All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式 .查看缺省的Oracle优化器:SQLshow parameters optimizer_mod

4、e;可以在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

5、,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开始，Or

6、acle在建库后就默认创建了一个名为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(

7、GATHER_STATS_JOB); Oracle 索引介绍Oracle索引简介索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据(加快查询)； 索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。 通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。

8、 索引分类索引分类l逻辑上: 单列索引, 多列索引, 唯一索引,非惟一索引。l物理上: B*-Tree索引，反向索引，位图索引。单列索引和复合索引单列索引和复合索引l单列索引是基于单个列所建立的索引。多列索引是基于两列或多列所建立的索引。l单列索引 Create index emp_ind1 on emp(ename);l复合索引 Create index emp_ind2 on emp(ename,job); 惟一索引和非惟一索引惟一索引和非惟一索引l惟一索引是索引列值不能重复的索引。l非惟一索引是索引列值可以重复的索引。无论是惟一索引还是非惟一索引，索引列都允许NULL。B*-tree索引

9、索引lB*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址 .l 创建索引： 1: cre

10、ate 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索引索引l假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如where column 20 and c

11、olumn value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。注意：只有索引中的值是逆向的，表中的值保持不变。位图索引位图索引l位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。l 创建位图索引: create bitmap index inx_bitmap_emp on emp(sex);位图索引的格式

12、位图索引的格式 l行值 1 2 3 4 5 6 7 8 9 10 Male 1 0 0 0 0 0 0 0 1 1 Female 0 1 1 1 0 0 1 1 0 0 Null 0 0 0 0 1 1 0 0 0 0 位图索引位图索引l如果搜索where gender=Male,要统计性别是”Male”的列行数的话，Oracle很快就能从位图中找到共3行即第1，9，10行是符合条件的；如果要搜索where gender=Male or gender=Female的列的行数的话，也很容易从位图中找到共8行即1，2，3，4，7，8，9，10行是符合条件的。如果要搜索表的值的话，那么Oracle会

13、用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。 聚簇聚簇l聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数据的目的。聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。一个表最多只能有一个聚簇索引。 使用使用 Oracle 聚簇索引聚簇索引 l在Oracle当中，聚簇不是索引的组织形式，而是表的组织形式。多用于表之间的连接字段。 例:dept(deptno, dnma,e loc) 和表 emp(empno, ename, job, mgr, . deptno), 常

14、在deptno上进行连接,可以针对deptno字段建立聚簇，然后建立基于该聚簇的索引，并让两个表都使用上该聚簇。 Oracle 聚簇索引聚簇索引lCREATE CLUSTER lpersonnel( department_number NUMBER(2) )l SIZE 512 STORAGE (INITIAL 100K NEXT 50K); l CREATE TABLE emp (l empno NUMBER l PRIMARY KEY,l ename VARCHAR2(10) NOT NULL l CHECK (ename = UPPER(ename),l job VARCHAR2(9),

15、 l mgr NUMBER ,l comm NUMBER(9,0) DEFAULT NULL, l deptno NUMBER(2) NOT NULL )l CLUSTER personnel (deptno);l l CREATE TABLE dept (l deptno NUMBER(2),l dname VARCHAR2(9),l loc VARCHAR2(9)l CLUSTER personnel (deptno);l l CREATE INDEX idx_personnel ON CLUSTER personnel; Oracle 聚簇索引聚簇索引l这样可以让两个表同时用上聚簇索引。

16、保证两个表的记录按照depno值尽量存放到同一个物理块当中。 使用索引的一些规则使用索引的一些规则1.索引对大表最有用，不要在小表上加索引.2.为每个表中的主码指定一个唯一索引。3.索引对于那些频繁出现在SQL命令中的where子句中的列最有用，不管这些列在选择中用来限定行还是为了表连接。4.当一个属性中存在很多不同的值时，可以使用索引。Oracle建议当一个属性中有少于30个不同值时，索引不是很有用，当属性中有100或更多不同值时索引就很明显地有用了。相似地，只有当使用索引进行查询的结果不超过文件中所有记录总数的20%时，使用索引才有帮助。5.检查你的DBMS对索引的限制，即便要在每个表允许

17、的索引个数上。许多系统不超过16个索引而且限制每个索引键值的大小。对一个表创建的索引数一般不超过5个.7. 对于包含空值的属性建立索引时要小心，在很多DBMS里有空值的行不能在索引中作为参照。8. 时常需要做删除、更新、插入操作的表不要创建索引.9. 将表和索引建立在不同的表空间内(TABLESPACES). 不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里. 同时,确保数据表空间和索引表空间置于不同的硬盘上.减少I/O竞争.SQL优化的一些规则:( 有些规则只有在基于规则的优化器里有效).1选择最有效率的表名顺序：选择最有效率的表名顺序：1.把记录少的表放在from子句的最后

18、面一个表.2.如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.原因：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中

19、合适记录进行合并. 2 WHERE子句中的连接顺序 ： ORACLE采用自右向左的顺序解析WHERE子句, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 3.SELECT子句中避免使用 * ORACLE在解析的过程中, 需要通过查询数据字典将* 依次转换成所有的列名.4. 使用表的别名(Alias) 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误. (Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法

20、判断这个Column的归属) 5.减少访问数据库的次数: 当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.6.(可能的话)用TRUNCATE替代DELETE. 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到执行删除命令之前的状况. 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复

21、.因此很少的资源被调用,执行时间也会很短. (TRUNCATE只在删除全表里的记录时适用,TRUNCATE是DDL不是DML) 7. (可能的话)使用COMMIT 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理上述3种资源中的内部花费 8.(可能的话)用Where子句替换HAVING子句 尽量少使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集

22、进行过滤. 这个处理需要排序,总计等操作. 如果能通过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

23、.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

24、 OUTPUT THE RECORD end ifend loop这样表 T1 要被完全扫描一遍 . 所以可以得出结论:当t1记录比较少,t2比较大时适合用exists(exists大部分情况会利用到index),当子查询记录集很小时用in比较合适.10.用表连接替换EXISTS改进第9打优化规则的例子.11.用EXISTS替换DISTINCT EXISTS 使查询更为迅速,因为RDBMS核心模块在子查询的条件一旦满足后 立刻返回结果. DISTINCT会先进行排序,然后会根据排序后的顺序去除相同的行.12.使用显式的游标(CURSOR) 使用隐式的游标,将会执行两次操作. 第一次检索记录,

25、第二次检查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); l13.用索引提高效率 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Updat

26、e语句的最佳路径时, ORACLE优化器将使用索引. 除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 在大型表中使用索引特别有效. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来 存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢. 定期的重构索引是有必要的. ALTER INDEX REBUILD Oracle索引优化

27、规则索引优化规则：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.避免在

28、索引列上使用NOT .4.用=替代 . 高效: SELECT * FROM EMP WHERE DEPTNO =4 低效: SELECT * FROM EMP WHERE DEPTNO 3 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录. l5.用UNION替换OR (适用于索引列) l通常情况下, 用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