Oracle 学习之性能优化（九）使用hint

2025-04-16 技术教程 151

基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在Oracle 中，是通过为语句添加 Hints(提示)来实现干预优化器优化的目的。

不建议在代码中使用hint，在代码使用hint使得CBO无法根据实际的数据状态选择正确的执行计划。毕竟数据是不断变化的，10g以后的CBO也越来越完善，大多数情况下我们该让Oracle自行决定采用什么执行计划。Oracle Hints是一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用Oracle Hints来实现：

Hints for Optimization Approaches and Goals

Hints for Access Paths

Hints for Query Transformations

Hints for Join Orders

Hints for Join Operations

Hints for Parallel Execution

Additional Hints

实现提示的语法：

{DELETE|INSERT|SELECT|UPDATE}/*+hint[text][hint[text]]...*/or{DELETE|INSERT|SELECT|UPDATE}--+hint[text][hint[text]]...

Hints for Optimization Approaches and Goals

/*+ ALL_ROWS*/语句块选择基于成本的优化方法,并获得最佳吞吐量,使资源消耗最小化.

/*+ FIRST_ROWS(n)*/语句块选择基于成本的优化方法,并获得最佳响应时间,使资源消耗最小化.

/*+ CHOOSE*/ 语句块依赖统计信息来决定选择CBO还是RBO

/*+ RULE*/语句块选择基于规则的优化方法.

实例：

SQL>select/*+ALL_ROWS*/*fromemp,deptwhereemp.deptno=dept.deptno;ExecutionPlan----------------------------------------------------------Planhashvalue:844388907----------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------|0|SELECTSTATEMENT||14|812|6(17)|00:00:01||1|MERGEJOIN||14|812|6(17)|00:00:01||2|TABLEACCESSBYINDEXROWID|DEPT|4|80|2(0)|00:00:01||3|INDEXFULLSCAN|PK_DEPT|4||1(0)|00:00:01||*4|SORTJOIN||14|532|4(25)|00:00:01||5|TABLEACCESSFULL|EMP|14|532|3(0)|00:00:01|----------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------4-access("EMP"."DEPTNO"="DEPT"."DEPTNO")filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

SQL>select/*+FIRST_ROWS(1)*/*fromemp,deptwhereemp.deptno=dept.deptno;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3625962092----------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|58|3(0)|00:00:01||1|NESTEDLOOPS|||||||2|NESTEDLOOPS||1|58|3(0)|00:00:01||3|TABLEACCESSFULL|EMP|1|38|2(0)|00:00:01||*4|INDEXUNIQUESCAN|PK_DEPT|1||0(0)|00:00:01||5|TABLEACCESSBYINDEXROWID|DEPT|1|20|1(0)|00:00:01|----------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------4-access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hints for Access Paths

/*+ FULL(TABLE)*/ 全表扫描

SQL>selectempnofromemp;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:179099197---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||1|4|1(0)|00:00:01||1|INDEXFULLSCAN|PK_EMP|1|4|1(0)|00:00:01|---------------------------------------------------------------------------

加hint后

SQL>select/*+FULL(emp)*/enamefromemp;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3956160932--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||1|6|2(0)|00:00:01||1|TABLEACCESSFULL|EMP|1|6|2(0)|00:00:01|--------------------------------------------------------------------------

/*+ROWID(TABLE)*/

SQL>SELECTROWID,EMPNOFROMEMP;ROWIDEMPNO----------------------------AAASZHAAEAAAACXAAA7369AAASZHAAEAAAACXAAB7499AAASZHAAEAAAACXAAC7521AAASZHAAEAAAACXAAD7566AAASZHAAEAAAACXAAE7654AAASZHAAEAAAACXAAF7698AAASZHAAEAAAACXAAG7782AAASZHAAEAAAACXAAH7788AAASZHAAEAAAACXAAI7839AAASZHAAEAAAACXAAJ7844AAASZHAAEAAAACXAAK7876ROWIDEMPNO----------------------------AAASZHAAEAAAACXAAL7900AAASZHAAEAAAACXAAM7902AAASZHAAEAAAACXAAN793414rowsselected.

从上面的结果集中选取一个rowid，不加hint

SQL>SELECT*FROMEMPWHEREROWID>='AAASZHAAEAAAACXAAA'ANDEMPNOIN(7521,7654);ExecutionPlan----------------------------------------------------------Planhashvalue:2355049923---------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|2(0)|00:00:01||1|INLISTITERATOR|||||||2|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||*3|INDEXUNIQUESCAN|PK_EMP|1||1(0)|00:00:01|---------------------------------------------------------------------------------------

使用hint

SQL>SELECT/*+rowid(EMP)*/*FROMEMPWHEREROWID>='AAASZHAAEAAAACXAAA'ANDEMPNOIN(7521,7654);ExecutionPlan----------------------------------------------------------Planhashvalue:2267975152------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|3(0)|00:00:01||*1|TABLEACCESSBYROWIDRANGE|EMP|1|38|3(0)|00:00:01|------------------------------------------------------------------------------------

/*+ INDEX(TABLE INDEX_NAME) */对表选择索引的扫描方法.INDEX_NAME一定要大写

SQL>select/*+INDEX(empPK_EMP)*/*fromemp;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:4170700152--------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||2|INDEXFULLSCAN|PK_EMP|14||1(0)|00:00:01|--------------------------------------------------------------------------------------

/*+ INDEX_ASC(TABLE INDEX_NAME)*/表明对表选择索引升序的扫描方法. 建立索引时如果没有指定desc，那么INDEX_ASC和INDEX 提示表示相同意义。

SQL>select/*+INDEX_ASC(empPK_EMP)*/*fromemp;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:4170700152--------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||2|INDEXFULLSCAN|PK_EMP|14||1(0)|00:00:01|--------------------------------------------------------------------------------------

/*+ INDEX_DESC(TABLE INDEX_NAME)*/表明对表选择索引降序的扫描方法.

SQL>select/*+INDEX_DESC(empPK_EMP)*/*fromemp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------------------------------------------------------7934MILLERCLERK77821982/01/2300:00:001300107902FORDANALYST75661981/12/0300:00:003000207900JAMESCLERK76981981/12/0300:00:00950307876ADAMSCLERK77881987/05/2300:00:001100207844TURNERSALESMAN76981981/09/0800:00:0015000307839KINGPRESIDENT1981/11/1700:00:005000107788SCOTTANALYST75661987/04/1900:00:003000207782CLARKMANAGER78391981/06/0900:00:002450107698BLAKEMANAGER78391981/05/0100:00:002850307654MARTINSALESMAN76981981/09/2800:00:0012501400307566JONESMANAGER78391981/04/0200:00:002975207521WARDSALESMAN76981981/02/2200:00:001250500307499ALLENSALESMAN76981981/02/2000:00:001600300307369SMITHCLERK79021980/12/1700:00:008002014rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3088625055--------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||2|INDEXFULLSCANDESCENDING|PK_EMP|14||1(0)|00:00:01|--------------------------------------------------------------------------------------

上面的查询结果是按照empno降序排列的。

/*+INDEX_COMBINE(TABLE INDEX1 INDEX2 ...)*/

SQL>createbitmapindexbidx_emp_salonemp(sal);Indexcreated.SQL>createbitmapindexbidx_emp_hiredateonemp(hiredate);Indexcreated.

SQL>SELECT*FROMEMPWHERESAL<1500ANDHIREDATE<'1981/06/0900:00:00';EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------------------------------------------------------7369SMITHCLERK79021980/12/1700:00:00800207521WARDSALESMAN76981981/02/2200:00:00125050030ExecutionPlan----------------------------------------------------------Planhashvalue:1384570463--------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|2(0)|00:00:01||*1|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||2|BITMAPCONVERSIONTOROWIDS|||||||*3|BITMAPINDEXRANGESCAN|BIDX_EMP_HIREDATE|||||--------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("SAL"<1500)3-access("HIREDATE"<TO_DATE('1981-06-0900:00:00','syyyy-mm-ddhh34:mi:ss'))filter("HIREDATE"<TO_DATE('1981-06-0900:00:00','syyyy-mm-ddhh34:mi:ss'))

使用hint后

SQL>SELECT/*+INDEX_COMBINE(EMPBIDX_EMP_HIREDATEBIDX_EMP_SAL)*/*FROMEMPWHERESAL<1500ANDHIREDATE<'1981/06/0900:00:00';EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------------------------------------------------------7369SMITHCLERK79021980/12/1700:00:00800207521WARDSALESMAN76981981/02/2200:00:00125050030ExecutionPlan----------------------------------------------------------Planhashvalue:1332639593--------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||2|BITMAPCONVERSIONTOROWIDS|||||||3|BITMAPAND|||||||4|BITMAPMERGE|||||||*5|BITMAPINDEXRANGESCAN|BIDX_EMP_HIREDATE||||||6|BITMAPMERGE|||||||*7|BITMAPINDEXRANGESCAN|BIDX_EMP_SAL|||||--------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------5-access("HIREDATE"<TO_DATE('1981-06-0900:00:00','syyyy-mm-ddhh34:mi:ss'))filter("HIREDATE"<TO_DATE('1981-06-0900:00:00','syyyy-mm-ddhh34:mi:ss'))7-access("SAL"<1500)filter("SAL"<1500)

/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */

当谓词中引用的列都有索引的时候，可以通过指定采用索引关联的方式，来访问数据.选择列只能是索引中的列。

SQL>createindexidx_emp_enameonemp(ename);Indexcreated.SQL>select/*+INDEX_JOIN(empPK_EMPIDX_EMP_ENAME)*/empno,enamefromempwhereename='KING'andempno=7839;EMPNOENAME----------------------------------------7839KINGExecutionPlan----------------------------------------------------------Planhashvalue:70197466---------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|10|2(50)|00:00:01||*1|VIEW|index$_join$_001|1|10|2(50)|00:00:01||*2|HASHJOIN|||||||*3|INDEXRANGESCAN|PK_EMP|1|10|0(0)|00:00:01||*4|INDEXRANGESCAN|IDX_EMP_ENAME|1|10|1(0)|00:00:01|---------------------------------------------------------------------------------------

/*+ INDEX_FFS(TABLE INDEX_NAME) */对指定的表执行快速全索引扫描,而不是全表扫描的办法

SQL>selectempnofromemp;ExecutionPlan----------------------------------------------------------Planhashvalue:179099197---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||1|4|1(0)|00:00:01||1|INDEXFULLSCAN|PK_EMP|1|4|1(0)|00:00:01|---------------------------------------------------------------------------

我们加上hint后

SQL>select/*+INDEX_FFS(empPK_EMP)*/empnofromemporderbyempno;ExecutionPlan----------------------------------------------------------Planhashvalue:3618959410--------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------|0|SELECTSTATEMENT||14|56|3(34)|00:00:01||1|SORTORDERBY||14|56|3(34)|00:00:01||2|INDEXFASTFULLSCAN|PK_EMP|14|56|2(0)|00:00:01|--------------------------------------------------------------------------------

/*+NO_INDEX(TABLE INDEX_NAME)*/ 不使用索引

SQL>select/*+NO_INDEX(empPK_EMP)*/empnofromemp;ExecutionPlan----------------------------------------------------------Planhashvalue:3956160932--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||1|4|2(0)|00:00:01||1|TABLEACCESSFULL|EMP|1|4|2(0)|00:00:01|--------------------------------------------------------------------------

/*+AND_EQUAL(TABLE INDEX1 INDEX2 ...)*/ index最少两个，最多不超过5个。

这个和INDEX_JOIN有点类似，但是INDEX_JOIN只能指定两个索引

SQL>CREATEINDEXIDX_EMP_JOBONEMP(JOB);Indexcreated.SQL>select/*+AND_EQUAL(empIDX_EMP_JOBIDX_EMP_ENAME)*/empno,enamefromempwhereENAME='KING'andJOB='SALESMAN';norowsselectedExecutionPlan----------------------------------------------------------Planhashvalue:1954919191---------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|18|2(0)|00:00:01||*1|TABLEACCESSBYINDEXROWID|EMP|1|18|2(0)|00:00:01||2|AND-EQUAL|||||||*3|INDEXRANGESCAN|IDX_EMP_ENAME|1||1(0)|00:00:01||*4|INDEXRANGESCAN|IDX_EMP_JOB|3||1(0)|00:00:01|---------------------------------------------------------------------------------------------

Hints for Query Transformations

/*+USE_CONCAT*/ 将WHERE 子句中的or或者in 查询转换成UNION ALL查询

SQL>SELECT/*+USE_CONCAT*/*fromempwhereename='KING'ORSAL>5000;ExecutionPlan----------------------------------------------------------Planhashvalue:1292243969-----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|4(0)|00:00:01||1|CONCATENATION|||||||2|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||*3|INDEXRANGESCAN|IDX_EMP_ENAME|1||1(0)|00:00:01||*4|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||5|BITMAPCONVERSIONTOROWIDS|||||||*6|BITMAPINDEXRANGESCAN|BIDX_EMP_SAL|||||-----------------------------------------------------------------------------------------------

/*+NO_EXPAND*/ 与USE_CONCAT正好相反，就是阻止优化器将条件中带or或者in查询转换成UNION ALL

SQL>select*fromempwhereempno=7840orename='SCOTT';ExecutionPlan----------------------------------------------------------Planhashvalue:2037299637----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|3(0)|00:00:01||1|CONCATENATION|||||||2|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||*3|INDEXRANGESCAN|IDX_EMP_ENAME|1||1(0)|00:00:01||*4|TABLEACCESSBYINDEXROWID|EMP|1|38|1(0)|00:00:01||*5|INDEXUNIQUESCAN|PK_EMP|1||0(0)|00:00:01|----------------------------------------------------------------------------------------------

加hint后

SQL>select/*+NO_EXPAND*/*fromempwhereempno=7840orename='SCOTT';ExecutionPlan----------------------------------------------------------Planhashvalue:3956160932--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||2|76|3(0)|00:00:01||*1|TABLEACCESSFULL|EMP|2|76|3(0)|00:00:01|--------------------------------------------------------------------------

/*+REWRITE（mview）*/ 使用物化视图重写sql

/*+NO_REWRITE*/ 不使用物化视图重写sql

/*+MERGE*/ 对视图查询进行合并。

看如下例子：

SQL>SELECTe1.ename,e1.sal,v.avg_salFROMempe1,(SELECTdeptno,avg(sal)avg_salFROMempe2GROUPBYdeptno)vWHEREe1.deptno=v.deptnoANDe1.sal>v.avg_sal;ExecutionPlan----------------------------------------------------------Planhashvalue:269884559-----------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-----------------------------------------------------------------------------|0|SELECTSTATEMENT||1|29|8(25)|00:00:01||*1|HASHJOIN||1|29|8(25)|00:00:01||2|VIEW||3|48|4(25)|00:00:01||3|HASHGROUPBY||3|21|4(25)|00:00:01||4|TABLEACCESSFULL|EMP|14|98|3(0)|00:00:01||5|TABLEACCESSFULL|EMP|14|182|3(0)|00:00:01|-----------------------------------------------------------------------------

先把v的结果集算出来，再和e1进行join运算。

如果使用hint呢。

SQL>SELECT/*+merge(v)*/e1.ename,e1.sal,v.avg_salFROMempe1,(SELECTdeptno,avg(sal)avg_salFROMempe2GROUPBYdeptno)vWHEREe1.deptno=v.deptnoANDe1.sal>v.avg_sal;ExecutionPlan----------------------------------------------------------Planhashvalue:2435006919-----------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-----------------------------------------------------------------------------|0|SELECTSTATEMENT||4|128|8(25)|00:00:01||*1|FILTER|||||||2|HASHGROUPBY||4|128|8(25)|00:00:01||*3|HASHJOIN||65|2080|7(15)|00:00:01||4|TABLEACCESSFULL|EMP|14|350|3(0)|00:00:01||5|TABLEACCESSFULL|EMP|14|98|3(0)|00:00:01|-----------------------------------------------------------------------------

先将两表进行关联，再进行group by

/*NO_MERGE(VIEW)*/ 与MERGE操作正好相反。

Hints for Join Orders

/*+ORDERED*/根据表在FROM子句中的顺序,依次对其连接.

SQL>select*fromempe,deptdwheree.deptno=d.deptno;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:844388907----------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------|0|SELECTSTATEMENT||14|812|6(17)|00:00:01||1|MERGEJOIN||14|812|6(17)|00:00:01||2|TABLEACCESSBYINDEXROWID|DEPT|4|80|2(0)|00:00:01||3|INDEXFULLSCAN|PK_DEPT|4||1(0)|00:00:01||*4|SORTJOIN||14|532|4(25)|00:00:01||5|TABLEACCESSFULL|EMP|14|532|3(0)|00:00:01|----------------------------------------------------------------------------------------

虽然emp表写在前面，但是优化器并没有先处理emp表。

添加hint后

SQL>select/*+ORDERED*/*fromempe,deptdwheree.deptno=d.deptno;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:1123238657---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||14|812|7(15)|00:00:01||*1|HASHJOIN||14|812|7(15)|00:00:01||2|TABLEACCESSFULL|EMP|14|532|3(0)|00:00:01||3|TABLEACCESSFULL|DEPT|4|80|3(0)|00:00:01|---------------------------------------------------------------------------

Hints for Join Operations

/*+USE_NL(TABLE1 TABLE2)*/ 使用循环嵌套进行连接,并把指定的第一个表作为驱动表.

SQL>select/*+USE_NL(de)*/*fromempe,deptdwheree.deptno=d.deptno;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:4192419542---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||14|812|10(0)|00:00:01||1|NESTEDLOOPS||14|812|10(0)|00:00:01||2|TABLEACCESSFULL|DEPT|4|80|3(0)|00:00:01||*3|TABLEACCESSFULL|EMP|4|152|2(0)|00:00:01|---------------------------------------------------------------------------

/*+USE_MERGE(table1 table2)*/

SQL>altersessionsetoptimizer_mode=first_rows_1;Sessionaltered.SQL>selecta.ename,b.enamefromempa,empbwherea.mgr=b.empno;13rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3355052392---------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|20|3(0)|00:00:01||1|NESTEDLOOPS|||||||2|NESTEDLOOPS||1|20|3(0)|00:00:01||*3|TABLEACCESSFULL|EMP|7|70|2(0)|00:00:01||*4|INDEXUNIQUESCAN|PK_EMP|1||0(0)|00:00:01||5|TABLEACCESSBYINDEXROWID|EMP|1|10|1(0)|00:00:01|---------------------------------------------------------------------------------------

SQL>select/*+USE_MERGE(ab)*/a.ename,b.enamefromempa,empbwherea.mgr=b.empno;13rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:992080948---------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|30|6(17)|00:00:01||1|MERGEJOIN||1|30|6(17)|00:00:01||2|TABLEACCESSBYINDEXROWID|EMP|14|140|2(0)|00:00:01||3|INDEXFULLSCAN|PK_EMP|14||1(0)|00:00:01||*4|SORTJOIN||13|130|4(25)|00:00:01||*5|TABLEACCESSFULL|EMP|13|130|3(0)|00:00:01|---------------------------------------------------------------------------------------

/*+USE_HASH(table1 table2)*/将指定的表与其他表通过哈希连接方式连接起来.

SQL>select/*+USE_HASH(ab)*/a.ename,b.enamefromempa,empbwherea.mgr=b.empno;13rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3638257876---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||1|20|7(15)|00:00:01||*1|HASHJOIN||1|20|7(15)|00:00:01||2|TABLEACCESSFULL|EMP|14|140|3(0)|00:00:01||*3|TABLEACCESSFULL|EMP|7|70|3(0)|00:00:01|---------------------------------------------------------------------------

/*+DRIVING_SITE(TABLE)*/ 此hint在使用dblink时有用。我们看如下例子

SQL>conn/assysdbaConnected.SQL>grantcreatedatabaselinktoscott;Grantsucceeded.SQL>connscott/tigerConnected.SQL>createshareddatabaselink"db1"authenticatedbySCOTTidentifiedby"tiger"using'192.168.199.216:1521/11GDG1';

进行如下查询

SQL>select*fromemp@db1e,deptdwheree.deptno=d.deptno;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:2705760024--------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|--------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||14|812|6(17)|00:00:01||||1|MERGEJOIN||14|812|6(17)|00:00:01||||2|TABLEACCESSBYINDEXROWID|DEPT|4|80|2(0)|00:00:01||||3|INDEXFULLSCAN|PK_DEPT|4||1(0)|00:00:01||||*4|SORTJOIN||14|532|4(25)|00:00:01||||5|REMOTE|EMP|14|532|3(0)|00:00:01|DB1|R->S|--------------------------------------------------------------------------------------------------------

Oracle是将db1上的emp的数据传到本地，然后排序合并。如果emp的数据量非常大时，这样无疑是非常耗时的。如果我们可以将dept传给远端，在远端执行，结果返回到本地，那么执行的速度会比较快。

SQL>select/*+DRIVING_SITE(e)*/*fromemp@db1e,deptdwheree.deptno=d.deptno;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:2412741621-----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|-----------------------------------------------------------------------------------------------|0|SELECTSTATEMENTREMOTE||14|812|7(15)|00:00:01||||*1|HASHJOIN||14|812|7(15)|00:00:01||||2|REMOTE|DEPT|4|80|3(0)|00:00:01|!|R->S||3|TABLEACCESSFULL|EMP|14|532|3(0)|00:00:01|DGTST||-----------------------------------------------------------------------------------------------

/*+LEADING(TABLE)*/将指定的表作为连接次序中的首表.

SQL>select/*+LEADING(e)*/*fromempe,deptdwheree.deptno=d.deptno;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:1123238657---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||14|812|7(15)|00:00:01||*1|HASHJOIN||14|812|7(15)|00:00:01||2|TABLEACCESSFULL|EMP|14|532|3(0)|00:00:01||3|TABLEACCESSFULL|DEPT|4|80|3(0)|00:00:01|---------------------------------------------------------------------------

/*+HASH_AJ*/ , /*+MERGE_AJ*/, and /*+NL_AJ*/ 将not in 改写成反连接。 AJ = anti-join

SQL>select*fromempwhereempnonotin(select/*+NL_AJ*/mgrfromempwheremgrisnotnull);8rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3509159946---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||8|336|24(0)|00:00:01||1|NESTEDLOOPSANTI||8|336|24(0)|00:00:01||2|TABLEACCESSFULL|EMP|14|532|3(0)|00:00:01||*3|TABLEACCESSFULL|EMP|6|24|2(0)|00:00:01|---------------------------------------------------------------------------

/*+HASH_SJ*/, /*+MERGE_SJ*/, and /*+NL_SJ*/ 将exists子句改写成半连接 SJ = semi-join

(一对多，只要有一个record 就 join成功)

SQL>select*fromdeptwhereexists(select*fromempwheredeptno=dept.deptnoandsal<1000);ExecutionPlan----------------------------------------------------------Planhashvalue:1946750470------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|27|4(25)|00:00:01||1|NESTEDLOOPS|||||||2|NESTEDLOOPS||1|27|4(25)|00:00:01||3|SORTUNIQUE||1|7|2(0)|00:00:01||4|TABLEACCESSBYINDEXROWID|EMP|1|7|2(0)|00:00:01||5|BITMAPCONVERSIONTOROWIDS|||||||*6|BITMAPINDEXRANGESCAN|BIDX_EMP_SAL||||||*7|INDEXUNIQUESCAN|PK_DEPT|1||0(0)|00:00:01||8|TABLEACCESSBYINDEXROWID|DEPT|1|20|1(0)|00:00:01|------------------------------------------------------------------------------------------------

添加hint

SQL>select*fromdeptwhereexists(select/*+HASH_SJ*/*fromempwheredeptno=dept.deptnoandsal<1000);ExecutionPlan----------------------------------------------------------Planhashvalue:944460660----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||2|54|6(17)|00:00:01||*1|HASHJOINSEMI||2|54|6(17)|00:00:01||2|TABLEACCESSFULL|DEPT|4|80|3(0)|00:00:01||3|TABLEACCESSBYINDEXROWID|EMP|1|7|2(0)|00:00:01||4|BITMAPCONVERSIONTOROWIDS|||||||*5|BITMAPINDEXRANGESCAN|BIDX_EMP_SAL|||||----------------------------------------------------------------------------------------------

其他常用的hint

/*+ parallel(table_name n) */

在sql中指定执行的并行度，这个值将会覆盖自身的并行度

select/*+parallel(t4)*/count(*)fromempt;

/*+ no_parallel(table_name) */

在sql中指定执行的不使用并行

select/*+no_parallel(t)*/count(*)fromempt;

/*+ append */以直接加载的方式将数据加载入库

insertintot/*+append*/select*fromt;

/*+ dynamic_sampling(table_name n) */

设置sql执行时动态采用的级别，这个级别为0~10

select/*+dynamic_sampling(t4)*/*fromtwhereid>1234