oracle sql 优化53个规则(三)

首页日志相册音乐收藏博友关于我  Lowell's Space我的生活，我的家園  最新日志首页推荐体力超弱的仪仗队队员唐一菲新片肉搏激情照全球最离谱的愚人节恶搞可以和成龙勾肩搭背的…直击朝鲜《阿里郎》去越南看梁家辉拍《情…更多  Oracle sql语句优化５３个规则详解(二)Windows 批处理大全（附各种实例）   技術文章 2008­01­09 09:41:07 阅读175 评论0   字号：大中小 订阅Oracle sql语句优化５３个规则详解(三)38.       避免在索引列上使用IS NULL和IS NOT NULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。 对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。    举例：如果唯一性索引建立在表的A列和B列上， 并且表中存在一条记录的A，B值为（123，null） ， ORACLE将不接受下一条具有相同A，B值（123，null）的记录（插入）。 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空。 因此你可以插入1000条具有相同键值的记录，当然它们都是空！    因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。    举例：    低效： （索引失效）    SELECT …     FROM DEPARTMENT  WHERE DEPT_CODE IS NOT NULL；    高效： （索引有效）    SELECT …     FROM DEPARTMENT  WHERE DEPT_CODE =0；39.       总是使用索引的第一个列如果索引是建立在多个列上， 只有在它的第一个列（leading column）被where子句引用时，优化器才会选择使用该索引。    译者按：这也是一条简单而重要的规则。 见以下实例。    SQL create table multiindexusage （ inda number ， indb number ， descr varchar2（10））； Table created. SQL create index multindex on multiindexusage（inda，indb）； Index created.  SQL set autotrace traceonly     SQL select * from multiindexusage where inda = 1； Execution Plan­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­    0      SELECT STATEMENT Optimizer=CHOOSE  1    0   TABLE ACCESS （BY INDEX ROWID） OF 'MULTIINDEXUSAGE'  2    1     INDEX （RANGE SCAN） OF 'MULTINDEX' （NON­UNIQUE）日志  注册登录网易 博客 摄影 发现好博客群博客召集令DemoVersion-WinnovativeSoftwareComponents    SQL select * from multiindexusage where indb = 1； Execution Plan­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­    0      SELECT STATEMENT Optimizer=CHOOSE  1    0   TABLE ACCESS （FULL） OF 'MULTIINDEXUSAGE'    很明显， 当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引    40.       ORACLE内部操作当执行查询时，ORACLE采用了内部的操作。 下表显示了几种重要的内部操作。ORACLE Clause内部操作ORDER BYSORT ORDER BYUNIONUNION­ALLMINUSMINUSINTERSECTINTERSECTDISTINCT,MINUS,INTERSECT,UNIONSORT UNIQUEMIN,MAX,COUNTSORT AGGREGATEGROUP BYSORT GROUP BYROWNUMCOUNT or COUNT STOPKEYQueries involving JoinsSORT JOIN,MERGE JOIN,NESTED LOOPSCONNECT BYCONNECT BY    41.       用UNION­ALL 替换UNION （ 如果有可能的话）    当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION­ALL的方式被合并， 然后在输出最终结果前进行排序。    如果用UNION ALL替代UNION， 这样排序就不是必要了。 效率就会因此得到提高。    举例：低效：SELECT ACCT_NUM， BALANCE_AMT  FROM DEBIT_TRANSACTIONS  WHERE TRAN_DATE = ‘31­DEC­95’ UNION  SELECT ACCT_NUM， BALANCE_AMT  FROM DEBIT_TRANSACTIONS  WHERE TRAN_DATE = ‘31­DEC­95’高效：SELECT ACCT_NUM， BALANCE_AMT  FROM DEBIT_TRANSACTIONS  WHERE TRAN_DATE = ‘31­DEC­95’ UNION ALL  SELECT ACCT_NUM， BALANCE_AMT  FROM DEBIT_TRANSACTIONS  WHERE TRAN_DATE = ‘31­DEC­95’    译者按：需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。 因此各位还是要从业务需求分析使用UNION ALL的可行性。    UNION 将对结果集合排序，这个操作会使用到SORT_AREA_SIZE这块内存。 对于这块内存的优化也是相当重要的。 下面的SQL可以用来查询排序的消耗量    Select substr（name，1，25） Sort Area Name， substr（value，1，15）   Value  from v$sysstat  where name like 'sort%'42.       使用提示（Hints）    对于表的访问，可以使用两种Hints. FULL 和 ROWID    FULL hint 告诉ORACLE使用全表扫描的方式访问指定表。DemoVersion-WinnovativeSoftwareComponents    例如：SELECT /*+ FULL（EMP） */ *  FROM EMP  WHERE EMPNO = 7893；    ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID的操作访问表。    通常， 你需要采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候， 使用这种方式， 你需要知道ROIWD的值或者使用索引。    如果一个大表没有被设定为缓存（CACHED）表而你希望它的数据在查询结束是仍然停留在SGA中，你就可以使用CACHE hint 来告诉优化器把数据保留在SGA中。 通常CACHE hint 和 FULL hint 一起使用。    例如：SELECT /*+ FULL（WORKER） CACHE（WORKER）*/ *  FROM WORK；    索引hint 告诉ORACLE使用基于索引的扫描方式。 你不必说明具体的索引名称例如：SELECT /*+ INDEX（LODGING） */ LODGING  FROM LODGING  WHERE MANAGER = ‘BILL GATES’；    在不使用hint的情况下， 以上的查询应该也会使用索引，然而，如果该索引的重复值过多而你的优化器是CBO， 优化器就可能忽略索引。 在这种情况下， 你可以用INDEX hint强制ORACLE使用该索引。    ORACLE hints 还包括ALL_ROWS， FIRST_ROWS， RULE，USE_NL， USE_MERGE， USE_HASH 等等。    译者按：使用hint ， 表示我们对ORACLE优化器缺省的执行路径不满意，需要手工修改。这是一个很有技巧性的工作。 我建议只针对特定的，少数的SQL进行hint的优化。对ORACLE的优化器还是要有信心（特别是CBO）    43.       用WHERE替代ORDER BY ORDER BY 子句只在两种严格的条件下使用索引。    ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。    ORDER BY中所有的列必须定义为非空。    WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列。    例如：表DEPT包含以下列：    DEPT_CODE    PK    NOT NULL  DEPT_DESC           NOT NULL  DEPT_TYPE           NULL    非唯一性的索引（DEPT_TYPE）    低效： （索引不被使用）    SELECT DEPT_CODE  FROM DEPT  ORDER BY DEPT_TYPE    EXPLAIN PLAN： SORT ORDER BY  TABLE ACCESS FULL高效： （使用索引）    SELECT DEPT_CODE  FROM DEPT  WHERE DEPT_TYPE  0 EXPLAIN PLAN： TABLE ACCESS BY ROWID ON EMP  INDEX RANGE SCAN ON DEPT_IDX译者按：ORDER BY 也能使用索引！ 这的确是个容易被忽视的知识点。 我们来验证一下：SQL select * from emp order by empno； Execution PlanDemoVersion-WinnovativeSoftwareComponents­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­    0      SELECT STATEMENT Optimizer=CHOOSE  1    0   TABLE ACCESS （BY INDEX ROWID） OF 'EMP'  2    1     INDEX （FULL SCAN） OF 'EMPNO' （UNIQUE）    44.       避免改变索引列的类型。    当比较不同数据类型的数据时， ORACLE自动对列进行简单的类型转换。    假设 EMPNO是一个数值类型的索引列。    SELECT …     FROM EMP  WHERE EMPNO = ‘123’实际上，经过ORACLE类型转换， 语句转化为：SELECT …     FROM EMP  WHERE EMPNO = TO_NUMBER（‘123’）    幸运的是，类型转换没有发生在索引列上，索引的用途没有被改变。    现在，假设EMP_TYPE是一个字符类型的索引列。    SELECT …     FROM EMP  WHERE EMP_TYPE = 123这个语句被ORACLE转换为：SELECT …     FROM EMP  WHERE TO_N