leading--Oracle hint

SQL>  explain plan for select rowid rid from
  2   scott.emp e where e.empno >100 and e.empno < 1000 and e.deptno in (select deptno from scott.dept where dname like 'a%');

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1465018025

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    32 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |         |       |       |            |          |
|   2 |   NESTED LOOPS                |         |     1 |    32 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    19 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | PK_EMP  |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / E@SEL$1
   4 - SEL$5DA710D3 / E@SEL$1
   5 - SEL$5DA710D3 / DEPT@SEL$2
   6 - SEL$5DA710D3 / DEPT@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$5DA710D3" "DEPT"@"SEL$2")
      USE_NL(@"SEL$5DA710D3" "DEPT"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "E"@"SEL$1" "DEPT"@"SEL$2")
      INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO"))
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      UNNEST(@"SEL$2")
      OUTLINE_LEAF(@"SEL$5DA710D3")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   4 - access("E"."EMPNO">100 AND "E"."EMPNO"<1000)
   5 - access("E"."DEPTNO"="DEPTNO")
   6 - filter("DNAME" LIKE 'a%')

 

已选择50行。

SQL>  explain plan for select /*+leading(DEPT@SEL$2)*/rowid rid from
  2   scott.emp e where e.empno >100 and e.empno < 1000 and e.deptno in (select deptno from scott.dept where dname like 'a%');

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 817787759

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    32 |     5  (20)| 00:00:01 |
|   1 |  MERGE JOIN                   |         |     1 |    32 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID | DEPT    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN            | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                   |         |     1 |    19 |     3  (34)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    19 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | PK_EMP  |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / DEPT@SEL$2
   3 - SEL$5DA710D3 / DEPT@SEL$2
   5 - SEL$5DA710D3 / E@SEL$1
   6 - SEL$5DA710D3 / E@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_MERGE(@"SEL$5DA710D3" "E"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$2" "E"@"SEL$1")
      INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO"))
      INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      UNNEST(@"SEL$2")
      OUTLINE_LEAF(@"SEL$5DA710D3")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - filter("DNAME" LIKE 'a%')
   4 - access("E"."DEPTNO"="DEPTNO")
       filter("E"."DEPTNO"="DEPTNO")
   6 - access("E"."EMPNO">100 AND "E"."EMPNO"<1000)

已选择50行。

 

SQL>  explain plan for select /*+leading(DEPT)*/rowid rid from
  2   scott.emp e where e.empno >100 and e.empno < 1000 and e.deptno in (select deptno from scott.dept where dname like 'a%');

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 817787759

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    32 |     5  (20)| 00:00:01 |
|   1 |  MERGE JOIN                   |         |     1 |    32 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID | DEPT    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN            | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                   |         |     1 |    19 |     3  (34)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    19 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | PK_EMP  |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / DEPT@SEL$2
   3 - SEL$5DA710D3 / DEPT@SEL$2
   5 - SEL$5DA710D3 / E@SEL$1
   6 - SEL$5DA710D3 / E@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_MERGE(@"SEL$5DA710D3" "E"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$2" "E"@"SEL$1")
      INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO"))
      INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      UNNEST(@"SEL$2")
      OUTLINE_LEAF(@"SEL$5DA710D3")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - filter("DNAME" LIKE 'a%')
   4 - access("E"."DEPTNO"="DEPTNO")
       filter("E"."DEPTNO"="DEPTNO")
   6 - access("E"."EMPNO">100 AND "E"."EMPNO"<1000)

已选择50行。