设为首页 收藏本站

运维网

 找回密码
 立即注册
查看: 2585|回复: 0

[经验分享] Oracle 学习之性能优化（九）使用hint

[复制链接]
peiyuan1030

尚未签到
发表于 2018-9-9 09:47:05 | 显示全部楼层 |阅读模式
　　基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了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;　　

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

　　
ROWIDEMPNO
　　
------------------ ----------
　　
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 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 select * from dept where exists (select /*+HASH_SJ*/* from emp where deptno=dept.deptno and sal 1234　　/*+ cache(table_name) */
　　进行全表扫描时将table置于LRU列表的最活跃端，类似于table的cache属性
select /*+ full(employees) cache(employees) */ last_name from employees


运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表，该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有，请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意，请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息，一经发现立即删除。若您因此触犯法律，一切后果自负，我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集，我们仅提供一个展示、介绍、观摩学习的平台，我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责，亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏，不得用于商业或者其他用途，否则，一切后果均由您自己承担，我们对此不承担任何法律责任
7、如涉及侵犯版权等问题，请您及时通知我们，我们将立即采取措施予以解决
8、联系人Email：admin@iyunv.com 网址：www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址：https://www.iyunv.com/thread-568548-1-1.html 上篇帖子： oracle移植至mysql相关sql语句 下篇帖子： Oracle中exp的使用2
回复

使用道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群，最新一手资源尽在官方微信交流群！快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:iyunvcom@gmail.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

豫ICP备20007574号-1 Copyright © 2012-2025

关于我们

发展历程

联系我们

关注我们

广告服务

广告案例

隐私条款

免责声明

用户登录

用户注册

版主守则

申请版主

手机版

金币获取

帮助中心

每日签到

Good Good

Study

Day Day

UP


客服E-mail:kefu@iyunv.com 客服QQ：1061981298


QQ群⑦：运维网交流群⑦ QQ群⑧：运维网交流群⑧ k8s群：运维网kubernetes交流群


提醒：禁止发布任何违反国家法律、法规的言论与图片等内容；本站内容均来自个人观点与网络等信息，非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来，其版权均归原作者及其网站所有，我们尊重他人的合法权益，如有内容侵犯您的合法权益，请及时与我们联系进行核实删除！



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表