ORACLE常用SQL优化hint语句.docx

ORACLE常用SQL优化hint语句.docx

《ORACLE常用SQL优化hint语句.docx》由会员分享，可在线阅读，更多相关《ORACLE常用SQL优化hint语句.docx（12页珍藏版）》请在冰豆网上搜索。

ORACLE常用SQL优化hint语句

在SQL语句优化过程中，我们经常会用到hint,现总结一下在SQL优化过程中常见OracleHINT的用法：

　　1./*+ALL_ROWS*/

　　表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.

　　例如:

　　SELECT/*+ALL+_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO=’SCOTT’;

　　2./*+FIRST_ROWS*/

　　表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

　　例如:

　　SELECT/*+FIRST_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO=’SCOTT’;

　　3./*+CHOOSE*/

　　表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;

　　表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;

　　例如:

　　SELECT/*+CHOOSE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO=’SCOTT’;

　　4./*+RULE*/

　　表明对语句块选择基于规则的优化方法.

　　例如:

　　SELECT/*+RULE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO=’SCOTT’;

　　5./*+FULL（TABLE）*/

　　表明对表选择全局扫描的方法.

　　例如:

　　SELECT/*+FULL（A）*/EMP_NO,EMP_NAMFROMBSEMPMSAWHEREEMP_NO=’SCOTT’;

　　6./*+ROWID（TABLE）*/

　　提示明确表明对指定表根据ROWID进行访问.

　　例如:

　　SELECT/*+ROWID（BSEMPMS）*/*FROMBSEMPMSWHEREROWID>=’AAAAAAAAAAAAAA’

　　ANDEMP_NO=’SCOTT’;

　　7./*+CLUSTER（TABLE）*/

　　提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.

　　例如:

　　SELECT/*+CLUSTER*/BSEMPMS.EMP_NO,DPT_NOFROMBSEMPMS,BSDPTMS

　　WHEREDPT_NO=’TEC304′ANDBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　8./*+INDEX（TABLEINDEX_NAME）*/

　　表明对表选择索引的扫描方法.

　　例如:

　　SELECT/*+INDEX（BSEMPMSSEX_INDEX）USESEX_INDEXBECAUSETHEREAREFEWMALEBSEMPMS*/FROMBSEMPMSWHERESEX=’M';

　　9./*+INDEX_ASC（TABLEINDEX_NAME）*/

　　表明对表选择索引升序的扫描方法.

　　例如:

　　SELECT/*+INDEX_ASC（BSEMPMSPK_BSEMPMS）*/FROMBSEMPMSWHEREDPT_NO=’SCOTT’;

　　10./*+INDEX_COMBINE*/

　　为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.

　　例如:

　　SELECT/*+INDEX_COMBINE（BSEMPMSSAL_BMIHIREDATE_BMI）*/*FROMBSEMPMS

　　WHERESAL<5000000ANDHIREDATE

　　11./*+INDEX_JOIN（TABLEINDEX_NAME）*/

　　提示明确命令优化器使用索引作为访问路径.

　　例如:

　　SELECT/*+INDEX_JOIN（BSEMPMSSAL_HMIHIREDATE_BMI）*/SAL,HIREDATE

　　FROMBSEMPMSWHERESAL<60000;

　　12./*+INDEX_DESC（TABLEINDEX_NAME）*/

　　表明对表选择索引降序的扫描方法.

　　例如:

　　SELECT/*+INDEX_DESC（BSEMPMSPK_BSEMPMS）*/FROMBSEMPMSWHEREDPT_NO='SCOTT';

　　13./*+INDEX_FFS（TABLEINDEX_NAME）*/

　　对指定的表执行快速全索引扫描,而不是全表扫描的办法.

　　例如:

　　SELECT/*+INDEX_FFS（BSEMPMSIN_EMPNAM）*/*FROMBSEMPMSWHEREDPT_NO='TEC305';

　　14./*+ADD_EQUALTABLEINDEX_NAM1,INDEX_NAM2,...*/

　　提示明确进行执行规划的选择,将几个单列索引的扫描合起来.

　　例如:

　　SELECT/*+INDEX_FFS（BSEMPMSIN_DPTNO,IN_EMPNO,IN_SEX）*/*FROMBSEMPMSWHEREEMP_NO='SCOTT'ANDDPT_NO='TDC306';

　　15./*+USE_CONCAT*/

　　对查询中的WHERE后面的OR条件进行转换为UNIONALL的组合查询.

　　例如:

　　SELECT/*+USE_CONCAT*/*FROMBSEMPMSWHEREDPT_NO='TDC506'ANDSEX='M';

　　16./*+NO_EXPAND*/

　　对于WHERE后面的OR或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.

　　例如:

　　SELECT/*+NO_EXPAND*/*FROMBSEMPMSWHEREDPT_NO='TDC506'ANDSEX='M';

　　17./*+NOWRITE*/

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

　　18./*+REWRITE*/

　　可以将视图作为参数.

　　19./*+MERGE（TABLE）*/

　　能够对视图的各个查询进行相应的合并.

　　例如:

　　SELECT/*+MERGE（V）*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMSA（SELETDPT_NO

　　,AVG（SAL）ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO）VWHEREA.DPT_NO=V.DPT_NO

　　ANDA.SAL>V.AVG_SAL;

　　20./*+NO_MERGE（TABLE）*/

　　对于有可合并的视图不再合并.

　　例如:

　　SELECT/*+NO_MERGE（V）*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMSA（SELECTDPT_NO,AVG（SAL）ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO）VWHEREA.DPT_NO=V.DPT_NOANDA.SAL>V.AVG_SAL;

　　21./*+ORDERED*/

　　根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.

　　例如:

　　SELECT/*+ORDERED*/A.COL1,B.COL2,C.COL3FROMTABLE1A,TABLE2B,TABLE3CWHEREA.COL1=B.COL1ANDB.COL1=C.COL1;

　　22./*+USE_NL（TABLE）*/

　　将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

　　例如:

　　SELECT/*+ORDEREDUSE_NL（BSEMPMS）*/BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAMFROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　23./*+USE_MERGE（TABLE）*/

　　将指定的表与其他行源通过合并排序连接方式连接起来.

　　例如:

　　SELECT/*+USE_MERGE（BSEMPMS,BSDPTMS）*/*FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　24./*+USE_HASH（TABLE）*/

　　将指定的表与其他行源通过哈希连接方式连接起来.

　　例如:

　　SELECT/*+USE_HASH（BSEMPMS,BSDPTMS）*/*FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　25./*+DRIVING_SITE（TABLE）*/

　　强制与ORACLE所选择的位置不同的表进行查询执行.

　　例如:

　　SELECT/*+DRIVING_SITE（DEPT）*/*FROMBSEMPMS,DEPT@BSDPTMSWHEREBSEMPMS.DPT_NO=DEPT.DPT_NO;

　　26./*+LEADING（TABLE）*/

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

　　27./*+CACHE（TABLE）*/

　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端

　　例如:

　　SELECT/*+FULL（BSEMPMS）CAHE（BSEMPMS）*/EMP_NAMFROMBSEMPMS;

　　28./*+NOCACHE（TABLE）*/

　　当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端

　　例如:

　　SELECT/*+FULL（BSEMPMS）NOCAHE（BSEMPMS）*/EMP_NAMFROMBSEMPMS;

　　29./*+APPEND*/

　　直接插入到表的最后,可以提高速度.

　　insert/*+append*/intotest1select*fromtest4;

　　30./*+NOAPPEND*/

　　通过在插入语句生存期内停止并行模式来启动常规插入.

　　insert/*+noappend*/intotest1select*fromtest4;

OptimizationApproaches

AccessMethods

ALL_ROWS

AND_EQUAL

CHOOSE

CLUSTER

FIRSTRULES

FULL

RULE

HASH

ParallelExecution

HASH_AJ

APPEND*ORDERED

HASH_SJ***

STAR**

INDEX

STAR_TRANSFORMATION*

INDEX_ASC

JoinOperations

INDEX_COMBINE*

DRIVING_SITE*

INDEX_DESC

USE_HASH**

INDEX_FFS*

USE_MERGE

MERGE_AJ**

USE_NL

MERGE_SJ***

AdditionalHints

ROW_ID

CACHE

USE_CONCAT

NOCACHE

NO_EXPAND***

PUSH_SUBQ

REWRITE***

MERGE***

NOREWRITE***

NO_MERGE*

JoinOrders

PUSH_JOIN_PRED***

NO_PUSH_JOIN_PRED***

NOAPPEND*

ORDEREDPREDICATES***

NOPARALLEL

PARALLEL

PARALLEL_INDEX*

NO_PARALLEL_INDEX***----------------------------------------------------------------------------

　　提示（hint）从Oracle7中引入，目的是弥补基于成本优化器的缺陷。

提示通常用来改变SQL执行计划，提高执行效率。

　　1.使用提示需要遵循的原则

　　1）仔细检查提示语法。

尽量使用完整注释语法/*+hint*/

　　2）使用表别名。

如果在查询中指定了表别名，那么提示必须也使用表别名。

例如：

select/*+index（e,dept_idx）*/*fromempe;

　　3）不要在提示中使用模式名称：

如果在提示中指定了模式的所有者，那么提示将被忽略。

例如：

　　select/*+index（scott.emp,dept_idx）*/*fromemp;

　　4）检验提示。

如果提示指定了不可用的访问路径，那么这个提示将被忽略。

　　2.导致提示无效的条件：

提示

被忽略的条件

cluster

与非簇表一同使用

hash

与非簇表一同使用

hash_aj

不存在子查询

index

指定的索引不存在

index_combine

不存在位图索引

merge_aj

不存在子查询

parallel

调用的不是TABLEACCESSFULL计划

push_subq

不存在子查询

star

事实表中存在不恰当的索引

use_concat

在where子句中不存在多个or条件

use_nl

表中不存在索引

　　3几种主要的优化模式：

　　1）all_rows:

all_rows是基于成本的优化方法，目的是提供整体最佳的吞吐量和最小的资源消耗。

all_rows提示倾向使用全表扫描，而且不适用于OLTP数据库。

使用all_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料。

　　2）rule:

rule提示使Oracle为查询提供基于规则的优化模式。

在怀疑CBO生成了非优化的执行计划时，通常首先尝试使用rule提示。

Rule提示忽略表和索引的统计资料，并且使用基本的试探法生成执行计划。

　　3）first_rows：

这个提示是基于成本的优化方法，目的是提供最快的反应时间。

使用first_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料。

　　4.表的连接提示

　　1）use_hash提示

　　use_hash提示对指定的表进行散列连接。

散列连接是Oracle用以驱动表（最小的表）向RAM区中装载记录的方法，RAM区由HASH_AREA_SIZE定义。

散列连接适合中间结果比较大的情况。

使用散列连接时,HASH_AREA_SIZE对速度影响非常大，如果驱动表不能一次装入内存，那么需要使用TEMP表空间，这种情况下速度比较慢。

这个参数可以在session级别动态修改，需要进行散列连接时可以临时增大，速度可能显着增加。

　　2）use_merge提示

　　use_merge提示强制执行一个排序合并操作。

排序合并操作通常与并行查询结合使用，因为排序合并操作倾向于全表扫描。

该提示适合于生成大型结果集的查询。

　　3）use_nl：

　　use_nl提示将强制对目标表执行嵌套循环连接。

use_nl提示很少用于SQL调整，因为CBO和RBO更倾向于使用循环嵌套连接。

　　4）star提示

　　star提示强制使用星型查询计划。

前提是查询中至少三个表，而且在事实表中存在恰当的索引

　　5.表反连接提示

　　SQL反连接是指在语句中包含NOTIN或者NOTEXISTS子句时执行的操作。

　　1）merge_aj

　　在使用全表访问比索引访问更好的情况下，可以在NOTIN子查询中使用merge_aj提示以便执行反连接。

　　2）hash_aj

　　hash_aj提示放在NOTIN子查询中用来希望执行散列连接时，执行散列反连接。

　　hash_aj和merge_aj要求子查询列非空。

　　6INDEX提示

　　1）INDEX提示简介：

　　INDEX提示被用于显示指定表名或表名与索引。

如果只指定了表名，那么优化器将使用表中的"最优"索引。

在永久优化SQL语句中，建议指定表和索引。

　　2）index_join提示

　　index_join提示明确要求优化器使用索引连接来作为访问路径。

　　3）and_equal提示

　　and_equal提示可以使多个非唯一的索引合并索引，并且使这些索引操作时就象单个连续索引一样。

该提示如果被应用，在查询计划中显示的是AND-EQUAL

　　4）index_asc提示

　　index_asc提示使用升序索引。

这是默认的优化器行为

　　5）no_index提示

　　该提示忽略索引存在，类似full

　　6）index_combine提示

　　index_combine提示用来强制使用位图索引作为表的访问路径。

　　7）index_ffs提示

　　索引快速完全扫描可以在不访问任何记录的情况下完成查询。

　　8）use_concat提示

　　use_concat提示要求为所有的OR条件使用UNIONALL执行计划，并将这个查询重新书写为多个查询。

如果在WHERE子句中存在大量OR条件，可以考虑使用use_concat提示。

　　7.总结

　　1）因为提示放在注释中，所以如果提示通现存的执行计划不兼容，或者提示不正确，有可能被忽略。

　　2）在使用RBO时，可以通过提示将指定的查询更改为CBO。

切记要对查询中涉及的所有表和索引进行分析

　　3）在使用CBO的时候，可以通过添加RULE提示或者FIRST_ROWS提示来开始调整一个可以的SQL语句

　　4）提示可以在子查询中使用，但是外部查询的提示不会带入子查询。

　　5）如果在查询计划中发现卡笛尔积（CARTESIAN），则要尽量解决。