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

发布时间：2020-08-05 18:50:19 作者：lqding1980
来源：网络阅读：4959

基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了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*/ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    14 |   812 |     6(17)| 00:00:01 |
|   1 |  MERGE JOIN     |       |    14 |   812 |     6(17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |
|*  4 |   SORT JOIN     |       |    14 |   532 |     4(25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL     | EMP     |    14 |   532 |     3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

SQL>  select /*+ FIRST_ROWS(1)*/ * from emp,dept where emp.deptno=dept.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |     1 |    58 |     3	 (0)| 00:00:01 |
|   1 |  NESTED LOOPS		     |	       |       |       |	    |	       |
|   2 |   NESTED LOOPS		     |	       |     1 |    58 |     3	 (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL	     | EMP     |     1 |    38 |     2	 (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN	     | PK_DEPT |     1 |       |     0	 (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hints for Access Paths

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

SQL> select empno from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 179099197

---------------------------------------------------------------------------
| Id  | Operation	 | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	  |	1 |	4 |	1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |	1 |	4 |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------

加hint后

SQL> select /*+ FULL(emp)*/ ename from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |     1 |     6 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

/*+ROWID(TABLE)*/

SQL> SELECT ROWID,EMPNO FROM EMP;

ROWID			EMPNO
------------------ ----------
AAASZHAAEAAAACXAAA	 7369
AAASZHAAEAAAACXAAB	 7499
AAASZHAAEAAAACXAAC	 7521
AAASZHAAEAAAACXAAD	 7566
AAASZHAAEAAAACXAAE	 7654
AAASZHAAEAAAACXAAF	 7698
AAASZHAAEAAAACXAAG	 7782
AAASZHAAEAAAACXAAH	 7788
AAASZHAAEAAAACXAAI	 7839
AAASZHAAEAAAACXAAJ	 7844
AAASZHAAEAAAACXAAK	 7876

ROWID			EMPNO
------------------ ----------
AAASZHAAEAAAACXAAL	 7900
AAASZHAAEAAAACXAAM	 7902
AAASZHAAEAAAACXAAN	 7934

14 rows selected.

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

SQL> SELECT * FROM EMP WHERE ROWID>='AAASZHAAEAAAACXAAA' AND EMPNO IN(7521,7654);


Execution Plan
----------------------------------------------------------
Plan hash value: 2355049923

---------------------------------------------------------------------------------------
| Id  | Operation		     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	      |     1 |    38 |     2	(0)| 00:00:01 |
|   1 |  INLIST ITERATOR	     |	      |       |       | 	   |	      |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     2	(0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN	     | PK_EMP |     1 |       |     1	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

使用hint

SQL> SELECT /*+rowid(EMP)*/ * FROM EMP WHERE ROWID>='AAASZHAAEAAAACXAAA' AND EMPNO IN(7521,7654);


Execution Plan
----------------------------------------------------------
Plan hash value: 2267975152

------------------------------------------------------------------------------------
| Id  | Operation		    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	   |	 1 |	38 |	 3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY ROWID RANGE| EMP  |	 1 |	38 |	 3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

SQL> select /*+INDEX(emp PK_EMP)*/ * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4170700152

--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	   1 |	  38 |	   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |	   1 |	  38 |	   2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN	    | PK_EMP |	  14 |	     |	   1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

SQL> select /*+INDEX_ASC(emp PK_EMP)*/ * from emp;
14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4170700152

--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	   1 |	  38 |	   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |	   1 |	  38 |	   2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN	    | PK_EMP |	  14 |	     |	   1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

SQL> select /*+INDEX_DESC(emp PK_EMP)*/ * from emp;

     EMPNO ENAME			  JOB				     MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
      7934 MILLER			  CLERK 			    7782 1982/01/23 00:00:00	   1300 		   10
      7902 FORD 			  ANALYST			    7566 1981/12/03 00:00:00	   3000 		   20
      7900 JAMES			  CLERK 			    7698 1981/12/03 00:00:00	    950 		   30
      7876 ADAMS			  CLERK 			    7788 1987/05/23 00:00:00	   1100 		   20
      7844 TURNER			  SALESMAN			    7698 1981/09/08 00:00:00	   1500 	 0	   30
      7839 KING 			  PRESIDENT				 1981/11/17 00:00:00	   5000 		   10
      7788 SCOTT			  ANALYST			    7566 1987/04/19 00:00:00	   3000 		   20
      7782 CLARK			  MANAGER			    7839 1981/06/09 00:00:00	   2450 		   10
      7698 BLAKE			  MANAGER			    7839 1981/05/01 00:00:00	   2850 		   30
      7654 MARTIN			  SALESMAN			    7698 1981/09/28 00:00:00	   1250       1400	   30
      7566 JONES			  MANAGER			    7839 1981/04/02 00:00:00	   2975 		   20
      7521 WARD 			  SALESMAN			    7698 1981/02/22 00:00:00	   1250        500	   30
      7499 ALLEN			  SALESMAN			    7698 1981/02/20 00:00:00	   1600        300	   30
      7369 SMITH			  CLERK 			    7902 1980/12/17 00:00:00	    800 		   20

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3088625055

--------------------------------------------------------------------------------------
| Id  | Operation		    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	     |	   1 |	  38 |	   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |	   1 |	  38 |	   2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN DESCENDING| PK_EMP |	  14 |	     |	   1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

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

SQL> create bitmap index bidx_emp_sal on emp(sal);

Index created.

SQL> create bitmap index bidx_emp_hiredate on emp(hiredate);

Index created.

SQL> SELECT * FROM EMP WHERE SAL<1500 AND HIREDATE<'1981/06/09 00:00:00';

     EMPNO ENAME			  JOB				     MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH			  CLERK 			    7902 1980/12/17 00:00:00	    800 		   20
      7521 WARD 			  SALESMAN			    7698 1981/02/22 00:00:00	   1250        500	   30


Execution Plan
----------------------------------------------------------
Plan hash value: 1384570463

--------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name		 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |			 |     1 |    38 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | EMP		 |     1 |    38 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|			 |	 |	 |	      | 	 |
|*  3 |    BITMAP INDEX RANGE SCAN   | BIDX_EMP_HIREDATE |	 |	 |	      | 	 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL"<1500)
   3 - access("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
       filter("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))

使用hint后

SQL> SELECT /*+INDEX_COMBINE( EMP BIDX_EMP_HIREDATE BIDX_EMP_SAL)*/ * FROM EMP WHERE SAL<1500 AND HIREDATE<'1981/06/09 00:00:00';

     EMPNO ENAME			  JOB				     MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH			  CLERK 			    7902 1980/12/17 00:00:00	    800 		   20
      7521 WARD 			  SALESMAN			    7698 1981/02/22 00:00:00	   1250        500	   30


Execution Plan
----------------------------------------------------------
Plan hash value: 1332639593

--------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name		 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |			 |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMP		 |     1 |    38 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|			 |	 |	 |	      | 	 |
|   3 |    BITMAP AND		     |			 |	 |	 |	      | 	 |
|   4 |     BITMAP MERGE	     |			 |	 |	 |	      | 	 |
|*  5 |      BITMAP INDEX RANGE SCAN | BIDX_EMP_HIREDATE |	 |	 |	      | 	 |
|   6 |     BITMAP MERGE	     |			 |	 |	 |	      | 	 |
|*  7 |      BITMAP INDEX RANGE SCAN | BIDX_EMP_SAL	 |	 |	 |	      | 	 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
       filter("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
   7 - access("SAL"<1500)
       filter("SAL"<1500)

/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */

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

SQL>  create index idx_emp_ename on emp(ename);

Index created.
SQL> select /*+ INDEX_JOIN( emp PK_EMP IDX_EMP_ENAME)*/ empno,ename from emp where ename='KING' and empno=7839;

     EMPNO ENAME
---------- ------------------------------
      7839 KING


Execution Plan
----------------------------------------------------------
Plan hash value: 70197466

---------------------------------------------------------------------------------------
| Id  | Operation	   | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		      |     1 |    10 |     2  (50)| 00:00:01 |
|*  1 |  VIEW		   | index$_join$_001 |     1 |    10 |     2  (50)| 00:00:01 |
|*  2 |   HASH JOIN	   |		      |       |       | 	   |	      |
|*  3 |    INDEX RANGE SCAN| PK_EMP	      |     1 |    10 |     0	(0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| IDX_EMP_ENAME    |     1 |    10 |     1	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

SQL> select empno from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197

---------------------------------------------------------------------------
| Id  | Operation	 | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	  |	1 |	4 |	1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |	1 |	4 |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------

我们加上hint后

SQL> select /*+INDEX_FFS(emp PK_EMP)*/ empno from emp order by empno;

Execution Plan
----------------------------------------------------------
Plan hash value: 3618959410

--------------------------------------------------------------------------------
| Id  | Operation	      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    14 |    56 |     3	(34)| 00:00:01 |
|   1 |  SORT ORDER BY	      |        |    14 |    56 |     3	(34)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| PK_EMP |    14 |    56 |     2	 (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

SQL> select /*+NO_INDEX(emp PK_EMP)*/ empno from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |     4 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |     1 |     4 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

SQL> CREATE INDEX IDX_EMP_JOB ON EMP(JOB);

Index created.

SQL> select /*+ AND_EQUAL(emp IDX_EMP_JOB IDX_EMP_ENAME)*/ empno,ename from emp where ENAME='KING' and JOB='SALESMAN';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1954919191

---------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		    |	  1 |	 18 |	  2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP	    |	  1 |	 18 |	  2   (0)| 00:00:01 |
|   2 |   AND-EQUAL		    |		    |	    |	    |		 |	    |
|*  3 |    INDEX RANGE SCAN	    | IDX_EMP_ENAME |	  1 |	    |	  1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN	    | IDX_EMP_JOB   |	  3 |	    |	  1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Hints for Query Transformations

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

SQL> SELECT /*+USE_CONCAT*/ *  from emp where ename='KING' OR SAL>5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1292243969
-----------------------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    38 |     4(0)| 00:00:01 |
|   1 |  CONCATENATION      |       |       |       |    |      |
|   2 |   TABLE ACCESS BY INDEX ROWID | EMP      |     1 |    38 |     2(0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN      | IDX_EMP_ENAME |     1 |       |     1(0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID | EMP      |     1 |    38 |     2(0)| 00:00:01 |
|   5 |    BITMAP CONVERSION TO ROWIDS|       |       |       |    |      |
|*  6 |     BITMAP INDEX RANGE SCAN   | BIDX_EMP_SAL  |       |       |    |      |
-----------------------------------------------------------------------------------------------

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

SQL> select * from emp where empno=7840 or ename='SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 2037299637

----------------------------------------------------------------------------------------------
| Id  | Operation		     | Name	     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		     |	   1 |	  38 |	   3   (0)| 00:00:01 |
|   1 |  CONCATENATION		     |		     |	     |	     |		  |	     |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP	     |	   1 |	  38 |	   2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN	     | IDX_EMP_ENAME |	   1 |	     |	   1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP	     |	   1 |	  38 |	   1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN	     | PK_EMP	     |	   1 |	     |	   0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

加hint后

SQL> select /*+NO_EXPAND*/ * from emp where empno=7840 or ename='SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     2 |    76 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    76 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

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

看如下例子：

SQL> SELECT e1.ename, e1.sal, v.avg_sal
       FROM emp e1,
         (SELECT deptno, avg(sal) avg_sal
            FROM emp e2
         GROUP BY deptno) v
     WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal; 

Execution Plan
----------------------------------------------------------
Plan hash value: 269884559

-----------------------------------------------------------------------------
| Id  | Operation	     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	    |	  1 |	 29 |	  8  (25)| 00:00:01 |
|*  1 |  HASH JOIN	     |	    |	  1 |	 29 |	  8  (25)| 00:00:01 |
|   2 |   VIEW		     |	    |	  3 |	 48 |	  4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |	    |	  3 |	 21 |	  4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |	 14 |	 98 |	  3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP  |	 14 |	182 |	  3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

如果使用hint呢。

SQL> SELECT /*+merge(v)*/e1.ename, e1.sal, v.avg_sal
      FROM emp e1,
        (SELECT deptno, avg(sal) avg_sal
           FROM emp e2
         GROUP BY deptno) v
      WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal; 
      
Execution Plan
----------------------------------------------------------
Plan hash value: 2435006919

-----------------------------------------------------------------------------
| Id  | Operation	     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	    |	  4 |	128 |	  8  (25)| 00:00:01 |
|*  1 |  FILTER 	     |	    |	    |	    |		 |	    |
|   2 |   HASH GROUP BY      |	    |	  4 |	128 |	  8  (25)| 00:00:01 |
|*  3 |    HASH JOIN	     |	    |	 65 |  2080 |	  7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |	 14 |	350 |	  3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP  |	 14 |	 98 |	  3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

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

Hints for Join Orders

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

SQL>  select * from emp e,dept d where e.deptno=d.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |    14 |   812 |     6	(17)| 00:00:01 |
|   1 |  MERGE JOIN		     |	       |    14 |   812 |     6	(17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2	 (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN	     | PK_DEPT |     4 |       |     1	 (0)| 00:00:01 |
|*  4 |   SORT JOIN		     |	       |    14 |   532 |     4	(25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL	     | EMP     |    14 |   532 |     3	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

添加hint后

SQL> select /*+ORDERED*/ * from emp e,dept d where e.deptno=d.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |    14 |   812 |	7  (15)| 00:00:01 |
|*  1 |  HASH JOIN	   |	  |    14 |   812 |	7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |	3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |	4 |    80 |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Hints for Join Operations

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

SQL> select /*+USE_NL(d e)*/ * from emp e,dept d where e.deptno=d.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |    14 |   812 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS	   |	  |    14 |   812 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |	4 |    80 |	3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |	4 |   152 |	2   (0)| 00:00:01 |
---------------------------------------------------------------------------

/*+USE_MERGE(table1 table2)*/

SQL> alter session set optimizer_mode=first_rows_1;

Session altered.
SQL> select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

13 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3355052392

---------------------------------------------------------------------------------------
| Id  | Operation		     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	      |     1 |    20 |     3	(0)| 00:00:01 |
|   1 |  NESTED LOOPS		     |	      |       |       | 	   |	      |
|   2 |   NESTED LOOPS		     |	      |     1 |    20 |     3	(0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL	     | EMP    |     7 |    70 |     2	(0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN	     | PK_EMP |     1 |       |     0	(0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL> select /*+USE_MERGE(a b)*/ a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

13 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 992080948

---------------------------------------------------------------------------------------
| Id  | Operation		     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	      |     1 |    30 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN		     |	      |     1 |    30 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   140 |     2	(0)| 00:00:01 |
|   3 |    INDEX FULL SCAN	     | PK_EMP |    14 |       |     1	(0)| 00:00:01 |
|*  4 |   SORT JOIN		     |	      |    13 |   130 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL	     | EMP    |    13 |   130 |     3	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

SQL> select /*+USE_HASH(a b)*/ a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

13 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3638257876

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    20 |	7  (15)| 00:00:01 |
|*  1 |  HASH JOIN	   |	  |	1 |    20 |	7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |	3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |	7 |    70 |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

SQL> conn / as sysdba
Connected.
SQL> grant create database link to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> create shared database link "db1"
authenticated by SCOTT
identified by "tiger"
using '192.168.199.216:1521/11GDG1';

进行如下查询

SQL> select * from emp@db1 e,dept d where e.deptno=d.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2705760024

--------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Inst	|IN-OUT|
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |    14 |   812 |     6	(17)| 00:00:01 |	|      |
|   1 |  MERGE JOIN		     |	       |    14 |   812 |     6	(17)| 00:00:01 |	|      |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2	 (0)| 00:00:01 |	|      |
|   3 |    INDEX FULL SCAN	     | PK_DEPT |     4 |       |     1	 (0)| 00:00:01 |	|      |
|*  4 |   SORT JOIN		     |	       |    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)*/* from emp@db1 e,dept d where e.deptno=d.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2412741621

-----------------------------------------------------------------------------------------------
| Id  | Operation	       | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |    14 |   812 |     7  (15)| 00:00:01 |        |      |
|*  1 |  HASH JOIN	       |      |    14 |   812 |     7  (15)| 00:00:01 |        |      |
|   2 |   REMOTE	       | DEPT |     4 |    80 |     3	(0)| 00:00:01 |      ! | R->S |
|   3 |   TABLE ACCESS FULL    | EMP  |    14 |   532 |     3	(0)| 00:00:01 |  DGTST |      |
-----------------------------------------------------------------------------------------------

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

SQL> select /*+LEADING(e)*/* from emp e,dept d where e.deptno=d.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |    14 |   812 |	7  (15)| 00:00:01 |
|*  1 |  HASH JOIN	   |	  |    14 |   812 |	7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |	3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |	4 |    80 |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

SQL> select * from emp where empno not in (select /*+NL_AJ*/ mgr from emp where mgr is not null) ;

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3509159946

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	8 |   336 |    24   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |	  |	8 |   336 |    24   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |	3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |	6 |    24 |	2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

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

SQL> select * from dept where exists (select * from emp where deptno=dept.deptno and sal<1000);


Execution Plan
----------------------------------------------------------
Plan hash value: 1946750470

------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	       |     1 |    27 |     4	(25)| 00:00:01 |
|   1 |  NESTED LOOPS			|	       |       |       |	    |	       |
|   2 |   NESTED LOOPS			|	       |     1 |    27 |     4	(25)| 00:00:01 |
|   3 |    SORT UNIQUE			|	       |     1 |     7 |     2	 (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | EMP	       |     1 |     7 |     2	 (0)| 00:00:01 |
|   5 |      BITMAP CONVERSION TO ROWIDS|	       |       |       |	    |	       |
|*  6 |       BITMAP INDEX RANGE SCAN	| BIDX_EMP_SAL |       |       |	    |	       |
|*  7 |    INDEX UNIQUE SCAN		| PK_DEPT      |     1 |       |     0	 (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID	| DEPT	       |     1 |    20 |     1	 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

添加hint

SQL> select * from dept where exists (select /*+HASH_SJ*/* from emp where deptno=dept.deptno and sal<1000);


Execution Plan
----------------------------------------------------------
Plan hash value: 944460660

----------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	     |	   2 |	  54 |	   6  (17)| 00:00:01 |
|*  1 |  HASH JOIN SEMI 	      | 	     |	   2 |	  54 |	   6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL	      | DEPT	     |	   4 |	  80 |	   3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID | EMP	     |	   1 |	   7 |	   2   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS| 	     |	     |	     |		  |	     |
|*  5 |     BITMAP INDEX RANGE SCAN   | BIDX_EMP_SAL |	     |	     |		  |	     |
----------------------------------------------------------------------------------------------

其他常用的hint

/*+ parallel(table_name n) */

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

select /*+ parallel(t 4) */ count(*)  from emp t;

/*+ no_parallel(table_name) */

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

select /*+ no_parallel(t) */ count(*)  from emp t;

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

insert into t /*+ append */ select * from t;

/*+ dynamic_sampling(table_name n) */

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

select /*+ dynamic_sampling(t 4) */ * from t where id > 1234

/*+ cache(table_name) */
进行全表扫描时将table置于LRU列表的最活跃端，类似于table的cache属性

select /*+ full(employees) cache(employees) */ last_name from employees

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

相关阅读