绵阳梓潼网站制作公司

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

        基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了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"
使用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"
/*+ 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|
-----------------------------------------------------------------------------------------------
|            
            
                            

                文章标题：Oracle学习之性能优化（九）使用hint                

                链接URL：http://myzitong.com/article/gshiop.html

其他资讯