如何改变oracle的执行计划（HINT）

如何改变oracle的执行计划？

1）/*+ALL_ROWS*/

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

Eg: SELECT /*+ALL_ROWS*/ t.empno,t.ename FROM lpx_emp t WHERE t.empno >7000

2）/*+FIRST_ROWS*/

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

Eg: SELECT /*+FIRST_ROWS*/ t.empno,t.ename FROM lpx_emp t WHERE t.empno >7000

3）/*+CHOOSE*/

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

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

Eg:

SELECT /*+CHOOSE*/ t.empno,t.ename FROM lpx_emp t WHERE t.empno >7000

4）/*+RULE*/

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

SELECT /*+RULE*/ t.empno,t.ename FROM lpx_emp t WHERE t.empno >100

5）/*+FULL(TABLE)*/

表明对表选择全局扫描的方法，eg:

 SELECT /*+FULL(TABLE)*/ t.empno,t.ename FROM lpx_emp t WHERE t.empno >100 SQL> explain plan for SELECT /*+FULL(TABLE)*/ t.empno,t.ename FROM lpx_emp t WHERE t.empno >100; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------Plan hash value: 3042463726 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8925 | 98175 | 15 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| LPX_EMP | 8925 | 98175 | 15 (0)| 00:00:01 | --------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."EMPNO">100) 13 rows selected

6）/*+ROWID(TABLE)*/

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

SELECT /*+ROWID(t)*/ t.empno,t.ename FROM lpx_emp t WHERE t.empno >100 SQL> explain plan for SELECT /*+ROWID(t)*/ t.empno,t.ename,ROWID FROM lpx_emp t WHERE ROWID>='AAAOGnAAEAAAAGsAAA' ; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3127940875 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 451 | 10373 | 15 (0)| 00: |* 1 | TABLE ACCESS BY ROWID RANGE| LPX_EMP | 451 | 10373 | 15 (0)| 00: -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access(ROWID>='AAAOGnAAEAAAAGsAAA') 13 rows selected  

7）/*+CLUSTER(TABLE)*/

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

SELECT /*+CLUSTER(t1) */ t1.empno,t1.ename,t2.dname

FROM lpx_emp t1,lpx_dept t2

WHERE t1.deptno = t2.deptno AND t2.deptno = 10

8）/*+INDEX(TABLE INDEX_NAME)*/

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

CREATE UNIQUE INDEX lpx_emp_ind ON lpx_emp(empno); SELECT /*+index(t empno)*/ t.empno,t.ename,t.ename FROM lpx_emp t WHERE empno = 7000; SQL> explain plan for SELECT /*+index(t empno)*/ t.empno,t.ename,t.ename FROM lpx_emp t WHERE empno = 7000 2 / Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 77789650 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| | 1 | TABLE ACCESS BY INDEX ROWID| LPX_EMP | 1 | 11 | 2 (0)| |* 2 | INDEX UNIQUE SCAN | LPX_EMP_IND | 1 | | 1 (0)| -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7000) 14 rows selected  

9）/*+INDEX_ASC(TABLE INDEX_NAME)*/

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

SELECT /*+index_asc(t job)*/ t.empno,t.ename,t.ename FROM lpx_emp t WHERE job = 'etl';

10）/*+INDEX_COMBINE*/

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

SELECT /*+/*+INDEX_COMBINE(t job,ename)*/ t.empno,t.ename,t.ename FROM lpx_emp t WHERE job = 'etl' AND ename = 'test10';

11）/*+INDEX_JOIN(TABLE INDEX_NAME)*/

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

12）/*+INDEX_DESC(TABLE INDEX_NAME)*/

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

SELECT /*+index_desc(t job)*/ t.empno,t.ename,t.ename FROM lpx_emp t WHERE job = 'etl';

13） /*+INDEX_FFS(TABLE INDEX_NAME)*/

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

例如:

SELECT /*+index_ffs(t job)*/ t.empno,t.ename,t.ename FROM lpx_emp t WHERE job = 'etl';

14） /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/

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

SELECT /*+INDEX_FFS(t empno,job)*/ t.empno,t.ename,t.ename FROM lpx_emp t WHERE empno= 7009 AND job = 'etl';

15） /*+USE_CONCAT*/

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

SELECT /*+USE_CONCAT*/ t.empno,t.ename,t.ename FROM lpx_emp t WHERE empno= 7009 OR job = 'etl';

16） /*+NO_EXPAND*/

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

SELECT /*+NO_EXPAND*/ t.empno,t.ename,t.ename FROM lpx_emp t WHERE empno= 7009 OR job = 'etl';

17）/*+NOWRITE*/

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

SELECT /*+NOWRITE*/  t.empno,t.ename,t.ename FROM lpx_emp t

18）/*+REWRITE*/

可以将视图作为参数.

19.）/*+MERGE(TABLE)*/

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

20） /*+NO_MERGE(TABLE)*/

对于有可合并的子查询不再合并.

SELECT /*+ NO_MERGE(A) NO_MERGE(B) */ COUNT(*)

FROM DBA_SEQUENCES A, DBA_OBJECTS B;

21）/*+ORDERED*/

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

22） /*+USE_NL(TABLE)*/

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

SQL> explain plan for SELECT /*+ordered use_nl(e,d) */e.ename,d.deptno 2 from lpx_emp e,lpx_dept d 3 WHERE e.deptno = d.deptno; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 322999853 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 462 | 17 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 462 | 17 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| LPX_EMP | 14 | 280 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| LPX_DEPT | 1 | 13 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."DEPTNO"="D"."DEPTNO") Note ----- - dynamic sampling used for this statement 19 rows selected

23） /*+USE_MERGE(TABLE)*/

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

SQL> explain plan for SELECT /*+ordered use_merge(e,d) */e.ename,d.deptno FROM lpx_emp e,lpx_dept d 2 WHERE e.deptno = d.deptno; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1209067579 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 462 | 8 (25)| 00:00:01 | | 1 | MERGE JOIN | | 14 | 462 | 8 (25)| 00:00:01 | | 2 | SORT JOIN | | 14 | 280 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| LPX_EMP | 14 | 280 | 3 (0)| 00:00:01 | |* 4 | SORT JOIN | | 4 | 52 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL| LPX_DEPT | 4 | 52 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."DEPTNO"="D"."DEPTNO") filter("E"."DEPTNO"="D"."DEPTNO") Note PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ----- - dynamic sampling used for this statement 22 rows selected

24）/*+USE_HASH(TABLE)*/

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

SQL> explain plan for SELECT /*+ordered use_hash(d,e) */e.ename,d.deptno 2 from lpx_emp e,lpx_dept d 3 WHERE e.deptno = d.deptno; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2846336464 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 462 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 462 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| LPX_EMP | 14 | 280 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| LPX_DEPT | 4 | 52 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="D"."DEPTNO") Note ----- - dynamic sampling used for this statement 19 rows selected

25）/*+DRIVING_SITE(TABLE)*/

强制数据在远端或是本地运算 .

SELECT /*+driving_site(r1)*/r1.* FROM tab_a@dl_rac r1, tab_b r2 WHERE r1.id = r2.id;

26） /*+LEADING(TABLE)*/

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

27） /*+CACHE(TABLE)*/

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

SELECT  /*+FULL(t) CACHE(t) */   t.empno,t.ename,t.ename FROM lpx_emp t

28.）/*+NOCACHE(TABLE)*/

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

例如:

SELECT  /*+FULL(t) NOCACHE(t) */   t.empno,t.ename,t.ename FROM lpx_emp t

29） /*+APPEND*/

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

INSERT /*+APPEND*/ INTO lpx_emp_test SELECT * FROM lpx_emp;

30） /*+NOAPPEND*/

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

INSERT /*+NOAPPEND*/ INTO lpx_emp_test SELECT * FROM lpx_emp;

31） /*+PARALLEL*/

将SQL语句并行执行，提高执行效率s

SELECT  /*+PARALLEL(t,4) */   t.empno,t.ename,t.ename FROM lpx_emp t