欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle入门教程:leading vs ordered hint oraclecuugocpocmdba 

程序员文章站 2022-05-08 16:29:42
...

Oracle入门教程:leading vs ordered hint
            
    
    
        oraclecuugocpocmdba 
leading hint 可以指示Oracle使用leading 中指定的表作为驱动表,
比如 正常的访问计划如下
SCOTT@www.linuxidc.com> select e.ename, hiredate, b.comm
  2   from emp e, bonus b
  3   where e.ename = b.ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 1125985041
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    34 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN         |       |     1 |    34 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| BONUS |     1 |    20 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP   |    14 |   196 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."ENAME"="B"."ENAME")


我们在leading 提示中指定 emp 表为驱动表
SCOTT@www.linuxidc.com> select /*+ leading(e b) */ e.ename,hiredate,b.comm

  2   from emp e, bonus b
  3   where e.ename = b.ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 1842254584
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    34 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN         |       |     1 |    34 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP   |    14 |   196 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| BONUS |     1 |    20 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."ENAME"="B"."ENAME")
如结果执行计划中将emp 作为驱动表!
1 在leading 提示同时使用ordered hint,则leading hint无效
SCOTT@www.linuxidc.com> select /*+ leading(b e)  ordered */  e.ename,hiredate, b.comm

  2   from emp e, bonus b
  3   where e.ename = b.ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 1842254584
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    34 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN         |       |     1 |    34 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP   |    14 |   196 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| BONUS |     1 |    20 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."ENAME"="B"."ENAME")
2 使用两个冲突的leading hint ,则oracle cbo会忽略所有的leading 提示!
SCOTT@www.linuxidc.com> select /*+ leading(b e) leading(e b) */e.ename,hiredate, b.comm

  2   from emp e, bonus b
  3   where e.ename = b.ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 1125985041
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    34 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN         |       |     1 |    34 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| BONUS |     1 |    20 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP   |    14 |   196 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."ENAME"="B"."ENAME")
  • Oracle入门教程:leading vs ordered hint
            
    
    
        oraclecuugocpocmdba 
  • 大小: 51.6 KB