文章详情

登录

短信预约-IT技能 免费直播动态提醒

选择考试省份

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-04-02 19:55

关注

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

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

实现提示的语法：

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


Hints for Optimization Approaches and Goals

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

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

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

 语句块选择基于规则的优化方法.

实例：

SQL> select  * 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  * 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


全表扫描

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  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 |
--------------------------------------------------------------------------

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  * 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_NAME一定要大写

SQL> select  * 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 |
--------------------------------------------------------------------------------------

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

SQL> select  * 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 |
--------------------------------------------------------------------------------------

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

SQL> select  * 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降序排列的。


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  * 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)

 

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

SQL>  create index idx_emp_ename on emp(ename);

Index created.
SQL> select  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 |
---------------------------------------------------------------------------------------

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

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  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 |
--------------------------------------------------------------------------------

不使用索引

SQL> select  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 |
--------------------------------------------------------------------------

index最少两个，最多不超过5个。

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

SQL> CREATE INDEX IDX_EMP_JOB ON EMP(JOB);

Index created.

SQL> select  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

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

SQL> SELECT  *  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  |       |       |    |      |
-----------------------------------------------------------------------------------------------

与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  * 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 |
--------------------------------------------------------------------------

使用物化视图重写sql

不使用物化视图重写sql

对视图查询进行合并。

看如下例子：

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 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

与MERGE操作正好相反。


Hints for Join Orders

 根据表在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  * 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

使用循环嵌套进行连接,并把指定的第一个表作为驱动表.

SQL> select  * 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 |
---------------------------------------------------------------------------


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  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 |
---------------------------------------------------------------------------------------

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

SQL> select  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 |
---------------------------------------------------------------------------

此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 * 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 |      |
-----------------------------------------------------------------------------------------------

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

SQL> select * 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 |
---------------------------------------------------------------------------

, , and 将not in 改写成反连接。 AJ = anti-join

SQL> select * from emp where empno not in (select  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 |
---------------------------------------------------------------------------

, , and 将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 * 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

 

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

select  count(*)  from emp t;

 

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

select  count(*)  from emp t;

以直接加载的方式将数据加载入库

insert into t  select * from t;

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

select  * from t where id > 1234

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

select  last_name from employees


阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题

  • 2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）

    难度     813人已做
    查看

  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看

  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看

  • 2024年上半年软考高项第一、二批次真题考点汇总（完整版）

    难度     435人已做
    查看

  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机

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

数据库2024-04-02

Oracle 学习之性能优化（八）优化器

数据库2024-04-02

Oracle 学习之性能优化（二）游标

数据库2024-04-02

Oracle 学习之 性能优化(十四) 内存

数据库2024-04-02

Oracle 学习之性能优化（十一）物化视图

数据库2024-04-02

Oracle 学习之性能优化（三）绑定变量

数据库2024-04-02

Oracle 学习之 性能优化(十二) 分区表

数据库2024-04-02

Oracle 学习之 性能优化(十五） ASH、ADDM、AWR

数据库2024-04-02

Oracle 学习之性能优化（六）访问路径

数据库2024-04-02

Oracle 学习之性能优化（一）SQL语句处理

数据库2024-04-02

Oracle 学习之性能优化（四）收集统计信息

数据库2024-04-02

Oracle 学习之性能优化（七）join的实现方式

数据库2024-04-02

【oracle性能优化】- 使用AWR定位oracle性能瓶颈

数据库2024-04-02

C++技术中的机器学习：使用C++如何优化机器学习模型的性能？

C++技术中的机器学习：使用C++如何优化机器学习模型的性能？
数据库2024-05-12

Oracle性能优化使用度量和预警

数据库2024-04-02

Go语言中数组如何优化性能？学习笔记带你走进优化之路！

数据库2023-10-20

Ubuntu用户如何学习Pygame游戏性能优化技巧

Ubuntu用户如何学习Pygame游戏性能优化技巧
数据库2024-10-15

怎么优化使用NVARCHAR2列的Oracle查询性能

怎么优化使用NVARCHAR2列的Oracle查询性能
数据库2024-04-02

MySQL性能优化之如何高效正确的使用索引

数据库2022-05-14

Oracle学习之DATAGUARD(十) 在同台机器上使用11g rman新特性创建DG

数据库2024-04-02
位置：首页-资讯-数据库
咦！没有更多了？去看看其它编程学习网 内容吧
首页课程
资料下载问答资讯