PT - Hints Using ORDERED and LEADING Examples

Using ORDERED and LEADING Hints

• Join Order

1. Ordered
2. Leading

SQL>  SELECT /*+ FULL(E) FULL(D) */ E.EMP_NO, E.ENAME,  D.DNAME FROM EMP E, DEPT D WHERE E.DEPT_NO = D.DEPT_NO
879 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   879 | 31644 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   879 | 31644 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     7 |    91 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |   879 | 20217 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("E"."DEPT_NO"="D"."DEPT_NO")


Statistics
----------------------------------------------------------
        108  recursive calls
          4  db block gets
        191  consistent gets
          0  physical reads
          0  redo size
      33392  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
         60  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
        879  rows processed


ORDERED 

SQL>  SELECT /*+ ORDERED FULL(E) FULL(D) */ E.EMP_NO, E.ENAME,  D.DNAME FROM EMP E, DEPT D WHERE E.DEPT_NO = D.DEPT_NO
879 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   879 | 31644 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   879 | 31644 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |   879 | 20217 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     7 |    91 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("E"."DEPT_NO"="D"."DEPT_NO")


Statistics
----------------------------------------------------------
          5  recursive calls
          4  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
      33350  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
         60  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        879  rows processed



LEADING

Here Leading table is DEPT

SQL>  SELECT /*+ LEADING(DEPT) FULL(EMP) FULL(DEPT) FULL(JOBS) */        EMP_NO,ENAME,DNAME,JOB_NAME FROM   DEPT, EMP, JOBS WHERE  DEPT.DEPT_NO = EMP.DEPT_NO AND JOBS.JOB_CODE = EMP.JOB_CODE
879 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 371351835

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   879 | 51861 |    11   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |      |   879 | 51861 |    11   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | JOBS |    23 |   414 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |      |   879 | 36039 |     8   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPT |     7 |    91 |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |   879 | 24612 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("JOBS"."JOB_CODE"="EMP"."JOB_CODE")
   3 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")


Statistics
----------------------------------------------------------
         57  recursive calls
         17  db block gets
        174  consistent gets
          0  physical reads
       1988  redo size
      33815  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
         60  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
        879  rows processed


SQL>

LEADING

Here EMP table is leading

SQL> SELECT /*+ LEADING(EMP) FULL(EMP) FULL(DEPT) FULL(JOBS) */        EMP_NO,ENAME,DNAME,JOB_NAME FROM   DEPT, EMP, JOBS WHERE  DEPT.DEPT_NO = EMP.DEPT_NO AND JOBS.JOB_CODE = EMP.JOB_CODE
879 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 495423756

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   879 | 51861 |    11   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |      |   879 | 51861 |    11   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | DEPT |     7 |    91 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |      |   879 | 40434 |     8   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |   879 | 24612 |     5   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| JOBS |    23 |   414 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")
   3 - access("JOBS"."JOB_CODE"="EMP"."JOB_CODE")


Statistics
----------------------------------------------------------
          9  recursive calls
          6  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
      33715  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
         60  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        879  rows processed

LEADING

Leading tables are EMPDEPT and JOBS

SQL> SELECT /*+ LEADING(EMP DEPT JOBS) FULL(EMP) FULL(DEPT) FULL(JOBS) */        EMP_NO,ENAME,DNAME,JOB_NAME   FROM DEPT, EMP, JOBS WHERE  DEPT.DEPT_NO = EMP.DEPT_NO AND JOBS.JOB_CODE = EMP.JOB_CODE

SQL>

879 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2955934646

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |   879 | 51861 |    11   (0)| 00:00:01 |

|*  1 |  HASH JOIN          |      |   879 | 51861 |    11   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL | JOBS |    23 |   414 |     3   (0)| 00:00:01 |

|*  3 |   HASH JOIN         |      |   879 | 36039 |     8   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| EMP  |   879 | 24612 |     5   (0)| 00:00:01 |

|   5 |    TABLE ACCESS FULL| DEPT |     7 |    91 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("JOBS"."JOB_CODE"="EMP"."JOB_CODE")

   3 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")

Statistics

----------------------------------------------------------

          9  recursive calls

          6  db block gets

         34  consistent gets

          0  physical reads

          0  redo size

      33718  bytes sent via SQL*Net to client

       1246  bytes received via SQL*Net from client

         60  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        879  rows processed

SQL>