Oracle入门教程：leading vs ordered hint -

哇哈哈852

浏览: 92844 次

最近访客更多访客>>

woimovie

lxheyu

xiangkun

fanan_666

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (130)

社区版块

存档分类

2014-07 ( 54)
2014-06 ( 45)
2014-05 ( 28)
更多存档...

Oracle入门教程：leading vs ordered hint

oracle cuug ocp ocm dba

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