oracle hint_leading

oracle hint_leading_merge
1,leading
  1,比ordered功能更多
  2,如下明确看出leading可以发变首表
  3,如果指定了ordered,优先级高于leading
 
SQL> explain plan for select emp.empno,dept.dname from emp,dept where emp.deptno
=dept.deptno;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    12 |   240 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    12 |   240 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     8 |   104 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    12 |    84 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

已选择15行。

SQL> explain plan for select /*+ leading(emp dept) */ emp.empno,dept.dname from
emp,dept where emp.deptno=dept.deptno;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    12 |   240 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    12 |   240 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    12 |    84 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     8 |   104 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

已选择15行。

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-751379/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-751379/