Oracle优化经典文章------索引原理篇

Oracle优化经典文章------索引原理篇

 

Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，这样会使您立刻成为一位英雄。这篇文章就将简单的讨论每个索引选项。主要有以下内容：

[1] 基本的索引概念
    查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。

[2] 组合索引
    当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。
    特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！

[3] ORACLE ROWID
    通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用，可以在任何表中使用和指定rowid列。   

[4] 限制索引
    限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题：
   
    4.1 使用不等于操作符（<>、!=）
        下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。
         select cust_Id,cust_name
         from   customers
         where  cust_rating <> 'aa';
         把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。
         select cust_Id,cust_name
         from   customers
         where  cust_rating < 'aa' or cust_rating > 'aa';
     特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
    
     4.2 使用IS NULL 或IS NOT NULL
         使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开     发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索     引，关于位图索引在稍后在详细讨论）。

     4.3 使用函数
         如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。
         下面的查询不会使用索引（只要它不是基于函数的索引）
          select empno,ename,deptno
          from   emp
          where  trunc(hiredate)='01-MAY-81';
          把上面的语句改成下面的语句，这样就可以通过索引进行查找。
          select empno,ename,deptno
          from   emp
          where  hiredate

     4.4 比较不匹配的数据类型
         比较不匹配的数据类型也是比较难于发现的性能问题之一。
         注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。
         select bank_name,address,city,state,zip
         from   banks
         where  account_number = 990354;
         Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：
         select bank_name,address,city,state,zip
         from   banks
         where  account_number ='990354';
     特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一               次“全表扫描”。

[5] 选择性
    使用USER_INDEXES视图，该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数，就可以判断索引的选择性。选择性越高，索引返回的数据就越少。

[6] 群集因子(Clustering Factor)
    Clustering Factor位于USER_INDEXES视图中。该列反映了数据相对于已索引的列是否显得有序。如果Clustering Factor列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就越有序。如果它的值接近于表中的行数，则表中的数据就不是很有序。
   

[7] 二元高度(Binary height)
    索引的二元高度对把ROWID返回给用户进程时所要求的I/O量起到关键作用。在对一个索引进行分析后，可以通过查询DBA_INDEXES的B-level列查看它的二元高度。二元高度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被删除的行，它的二元高度也会增加。更新索引列也类似于删除操作，因为它增加了已删除键的数目。重建索引可能会降低二元高度。

[8] 快速全局扫描
    在Oracle7.3后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许Oracle执行一个全局索引扫描操作。快速全局扫描读取B-树索引上所有树叶块。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。

[9] 跳跃式扫描
    从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多。下面的程序清单显示出性能的差别：
    create index skip1 on emp5(job,empno);
    index created.

    select count(*)
    from emp5
    where empno=7900;

    Elapsed:00:00:03.13

    Execution Plan
    0     SELECT STATEMENT ptimizer=CHOOSE(Cost=4 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(FAST FULL SCAN) OF 'SKIP1'(NON-UNIQUE)

    Statistics

    6826 consistent gets
    6819 physical   reads

    select /*+ index(emp5 skip1)*/ count(*)
    from emp5
    where empno=7900;

    Elapsed:00:00:00.56

    Execution Plan
    0     SELECT STATEMENT ptimizer=CHOOSE(Cost=6 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(SKIP SCAN) OF 'SKIP1'(NON-UNIQUE)

    Statistics

    21 consistent gets
    17 physical   reads

[10] 索引的类型
     B-树索引
     位图索引
     HASH索引
     索引编排表
     反转键索引
     基于函数的索引
     分区索引
     本地和全局索引
     特别说明：关于这部分内容大家感兴趣可以自己查找资料学习!

[11]  登录ORACLE管理平台在会话中查找锁表的进程的系统pid ,如果是UNIX 则kill 该pid -9 
如果是windows 则ntsd -c q -p PID 来强制关闭进程。

[12] 
编译超时的ORA-04021错误2007-04-28 15:38某个应用正在锁定该表或者包
表为
select b.SID,b.SERIAL#,c.SQL_TEXT
from v$locked_object a, v$session b, v$sqlarea c
where a.SESSION_ID = b.SID
and b.SQL_ADDRESS = c.ADDRESS
and c.sql_text like '%table_name%'
包为
select B.SID,b.USERNAME,b.MACHINE FROM V$ACCESS A, V$SESSION B WHERE A.SID=B.SID and a.OBJECT = '％PACKAGE_name％' and a.TYPE = 'PACKAGE'
找出这个session
然后 kill
alter system kill session 'sid,serial#'

[13]
oracle使用分区后，可以删除单个分区的数据，具体操作为：
alter table tablename truncate partition partition_name;
 
[14] 索引表的建立方法：
create table test(id number primary key) organization index;

[15]查询各个表空间占有率
SELECT   UPPER(F.TABLESPACE_NAME)   "表空间名",
 D.TOT_GROOTTE_MB   "表空间大小(M)",
 D.TOT_GROOTTE_MB   -   F.TOTAL_BYTES   "已使用空间(M)",
 TO_CHAR(ROUND((D.TOT_GROOTTE_MB   -   F.TOTAL_BYTES)   / 
D.TOT_GROOTTE_MB   *   100,
 2),
 '990.99')   "使用比",
 F.TOTAL_BYTES   "空闲空间(M)",
 F.MAX_BYTES   "最大块(M)"
 FROM   (SELECT   TABLESPACE_NAME,
 ROUND(SUM(BYTES)   /   (1024   *   1024),   2)   TOTAL_BYTES,
 ROUND(MAX(BYTES)   /   (1024   *   1024),   2)   MAX_BYTES
 FROM   SYS.DBA_FREE_SPACE
 GROUP   BY   TABLESPACE_NAME)   F,
 (SELECT   DD.TABLESPACE_NAME,
 ROUND(SUM(DD.BYTES)   /   (1024   *   1024),   2)   TOT_GROOTTE_MB
 FROM   SYS.DBA_DATA_FILES   DD
 GROUP   BY   DD.TABLESPACE_NAME)   D
 WHERE   D.TABLESPACE_NAME   =   F.TABLESPACE_NAME
 ORDER   BY   4   DESC

 

 
 [16]
 
Hint全集
174. /*+ALL_ROWS*/

　　表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　175. /*+FIRST_ROWS*/

　　表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　176. /*+CHOOSE*/

　　表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　177. /*+ RULE*/

　　表明对语句块选择基于规则的优化方法.例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP'; 

　　178. /*+ FULL(TABLE)*/

　　表明对表选择全局扫描的方法.例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';

　　179. /*+ROWID(TABLE)*/

　　提示明确表明对指定表根据ROWID进行访问.例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';

　　180. /*+CLUSTER(TABLE)*/
　
　　提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

181. /*+ INDEX(TABLE   INDEX_NAME)*/
/*+index(table ind_name) index(table ind_name)*/
表明对表选择索引的扫描方法.例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

　　182. /*+INDEX_ASC(TABLE INDEX_NAME)*/

　　表明对表选择索引升序的扫描方法.例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';

　　183. /*+INDEX_COMBINE*/

　　为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE

　　184. /*+INDEX_JOIN(TABLE INDEX_NAME)*/

　　提示明确命令优化器使用索引作为访问路径.例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;

　　185. /*+INDEX_DESC(TABLE INDEX_NAME)*/

　　表明对表选择索引降序的扫描方法.例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';

　　186. /*+INDEX_FFS(TABLE INDEX_NAME)*/

　　对指定的表执行快速全索引扫描,而不是全表扫描的办法.例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TE垃圾产品';

　　187. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/

　　提示明确进行执行规划的选择,将几个单列索引的扫描合起来.例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';

　　188. /*+USE_CONCAT*/

　　对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

　　189. /*+NO_EXPAND*/

　　对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

　　190. /*+NOWRITE*/

　　禁止对查询块的查询重写操作.

191. /*+REWRITE*/

　　可以将视图作为参数.

　　192. /*+MERGE(TABLE)*/

　　能够对视图的各个查询进行相应的合并.例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) Va WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;

　　193. /*+NO_MERGE(TABLE)*/

　　对于有可合并的视图不再合并.例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;

　　194. /*+ORDERED*/

　　根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C
WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

　　195. /*+USE_NL(TABLE)*/

　　将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　196. /*+USE_MERGE(TABLE)*/

　　将指定的表与其他行源通过合并排序连接方式连接起来.例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　197. /*+USE_HASH(TABLE)*/

　　将指定的表与其他行源通过哈希连接方式连接起来.例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　198. /*+DRIVING_SITE(TABLE)*/

　　强制与ORACLE所选择的位置不同的表进行查询执行.例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

　　199. /*+LEADING(TABLE)*/

　　将指定的表作为连接次序中的首表.

200. /*+CACHE(TABLE)*/

　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

　　201. /*+NOCACHE(TABLE)*/

　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端，例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

　　202. /*+APPEND*/

　　直接插入到表的最后,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;

　　203. /*+NOAPPEND*/

　　通过在插入语句生存期内停止并行模式来启动常规插入.
insert /*+noappend*/ into test1 select * from test4;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16704904/viewspace-625484/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16704904/viewspace-625484/