oracle中的sql语句优化附件HINTSWord下载.docx
《oracle中的sql语句优化附件HINTSWord下载.docx》由会员分享，可在线阅读，更多相关《oracle中的sql语句优化附件HINTSWord下载.docx（13页珍藏版）》请在冰豆网上搜索。
例子：
尽快地显示前10行记录
select/*+first_rows（10）*/*fromempwheredeptno=10;
6、指定访问路径的HINT
FULL:
执行全表扫描
/*+FULL（table）*/
ROID:
根据ROWID进行扫描
/*+ROWID（table）*/
INDEX:
根据某个索引进行扫描
/*+INDEX（table[index[index]...]）*/
select/*+index（empind_emp_sal）*/*fromempwheredeptno=200andsal>
300;
如果写了多个，则ORACLE自动选择最优的哪个
select/*+index（empind_emp_salind_emp_deptno）*/*fromempwheredeptno=200andsal>
INDEX_JOIN:
如果所选的字段都是索引字段（是几个索引的），那么可以通过索引连接就可访问到数据，而不需要访问表的数据。
/*+INDEX_JOIN（table[index[index...]]）*/
select/*+index_join（empind_emp_salind_emp_deptno）*/deptno,salfromempwheredeptno=20;
INDEX_FFS:
执行快速全索引扫描
/*+INDEX_FFS（table[index[index]...]）*/
select/*+index_ffs（emppk_emp）*/count（*）fromemp;
NO_INDEX:
指定不使用哪些索引
/*+NO_INDEX（table[index[index]...]）*/
select/*+no_index（empind_emp_salind_emp_deptno）*/*fromempwheredeptno=200andsal>
AND_EQUAL:
指定合并两个或以上索引检索的结果（交集），最多不能超过5个
/*+AND_EQUAL（tableindexindex[index][index][index]）*/
7、指定表的连接顺序
ORDERED:
按表出现的顺序进行连接
/*+ORDERED*/
select/*+ordered*/emp.ename,dept.dnamefromdept,empwhereemp.deptno=dept.deptno;
select/*+ordered*/emp.ename,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;
8、指定表的连接操作
USE_NL:
按nestedloops方式连接
--默认hashjoin，获取所有数据的最快返回时间
selectemp.ename,dept.dnamefromdept,empwhereemp.deptno=dept.deptno;
--指定emp作为innertable,以获取最快的响应时间
select/*+ordereduse_nl（emp）togetfirstrowfaster*/emp.ename,dept.dnamefromdept,empwhereemp.deptno=dept.deptno;
select/*+ordereduse_nl（empdept）*/emp.ename,dept.dnamefromdept,empwhereemp.deptno=dept.deptno;
2.常见OracleHINT的用法
在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='
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='
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>
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）*/
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执行计划，提高执行效率。
3.使用提示需要遵循的原则
1）仔细检查提示语法。
尽量使用完整注释语法/*+hint*/
2）使用表别名。
如果在查询中指定了表别名，那么提示必须也使用表别名。
例如：
select/*+index（e,dept_idx）*/*fromempe;
3）不要在提示中使用模式名称：
如果在提示中指定了模式的所有者，那么提示将被忽略。
select/*+index（scott.emp,dept_idx）*/*fromemp;
4）检验提示。
如果提示指定了不可用的访问路径，那么这个提示将被忽略。
4.导致提示无效的条件：
提示
被忽略的条件
cluster
与非簇表一同使用
hash
hash_aj
不存在子查询
index
指定的索引不存在
index_combine
不存在位图索引
merge_aj
parallel
调用的不是TABLEACCESSFULL计划
push_subq
star
事实表中存在不恰当的索引
use_concat
在where子句中不存在多个or条件
use_nl
表中不存在索引
5.几种主要的优化模式：
1）all_rows:
all_rows是基于成本的优化方法，目的是提供整体最佳的吞吐量和最小的资源消耗。
all_rows提示倾向使用全表扫描，而且不适用于OLTP数据库。
使用all_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料。
2）rule:
rule提示使Oracle为查询提供基于规则的优化模式。
在怀疑CBO生成了非优化的执行计划时，通常首先尝试使用rule提示。
Rule提示忽略表和索引的统计资料，并且使用基本的试探法生成执行计划。
3）first_rows：
这个提示是基于成本的优化方法，目的是提供最快的反应时间。
使用first_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料。
6.表的连接提示
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提示强制使用星型查询计划。
前提是查询中至少三个表，而且在事实表中存在恰当的索引
7.表反连接提示
SQL反连接是指在语句中包含NOTIN或者NOTEXISTS子句时执行的操作。
1）merge_aj
在使用全表访问比索引访问更好的情况下，可以在NOTIN子查询中使用merge_aj提示以便执行反连接。
2）hash_aj
hash_aj提示放在NOTIN子查询中用来希望执行散列连接时，执行散列反连接。
hash_aj和merge_aj要求子查询列非空。
8.INDEX提示
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提示。
9.总结
1）因为提示放在注释中，所以如果提示通现存的执行计划不兼容，或者提示不正确，有可能被忽略。
2）在使用RBO时，可以通过提示将指定的查询更改为CBO。
切记要对查询中涉及的所有表和索引进行分析
3）在使用CBO的时候，可以通过添加RULE提示或者FIRST_ROWS提示来开始调整一个可以的SQL语句
4）提示可以在子查询中使用，但是外部查询的
升级会员 