Oracle Database Commands and Practical Examples

Thursday 2 April 2020

PT - Hints using Join Method examples

JOIN Methods

Joins Methods

USE_NL ( Nested loop)
USE_HASH (hash join methods)
USE_MERGE (merge join methods)

/*+ USE_NL(EMP) */

SQL> SELECT /*+ USE_NL(EMP) */ EMP_NO,ENAME,DNAME FROM EMP, DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND EMP_NO=630;

EMP_NO ENAME DNAME
---------- -------------------- ----------------------
630 BHBBDMIC NREB Sales

Execution Plan
----------------------------------------------------------
Plan hash value: 4081233386

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 36 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 23 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

3 - access("EMP_NO"=630)
5 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

############################################################

/*+ USE_NL(EMP) */

SQL> SELECT /*+ USE_NL(EMP) */ EMP_NO,ENAME,DNAME FROM EMP, DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_NO=10

Execution Plan
----------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 113 | 4068 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 113 | 4068 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 113 | 2599 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_IX | 113 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
4812 bytes sent via SQL*Net to client
685 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
113 rows processed

SQL>

#################################################################################

/*+ LEADING (DEPT) USE_NL(EMP) */

SQL> SELECT /*+ LEADING (DEPT) USE_NL(EMP) */ EMP_NO,ENAME,DNAME FROM EMP, DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND EMP_NO=630;

EMP_NO ENAME DNAME
---------- -------------------- ----------------------
630 BHBBDMIC NREB Sales

Execution Plan
----------------------------------------------------------
Plan hash value: 1668757792

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 36 | 10 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 7 | 36 | 10 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 7 | 91 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 23 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

4 - access("EMP_NO"=630)
5 - filter("DEPT"."DEPT_NO"="EMP"."DEPT_NO")

Statistics
----------------------------------------------------------
1 recursive calls
2 db block gets
24 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

#################################################################################

/*+ LEADING (EMP) USE_NL(EMP) */

SQL> SELECT /*+ LEADING (EMP) USE_NL(EMP) */ EMP_NO,ENAME,DNAME FROM EMP, DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_NO=10
113 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2375772551

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 113 | 4068 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 113 | 4068 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 113 | 2599 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_IX | 113 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

1 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")
3 - access("EMP"."DEPT_NO"=10)
5 - access("DEPT"."DEPT_NO"=10)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
4812 bytes sent via SQL*Net to client
685 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
113 rows processed

SQL>

#################################################################################

/*+ USE_HASH(DEPT) */

SQL> SELECT /*+ USE_HASH(DEPT) */ EMP_NO,ENAME,DNAME FROM EMP, DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND EMP_NO=630;

EMP_NO ENAME DNAME
---------- -------------------- ----------------------
630 BHBBDMIC NREB Sales

Execution Plan
----------------------------------------------------------
Plan hash value: 300184293

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 5 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 36 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 23 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEPT | 7 | 91 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

1 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")
3 - access("EMP_NO"=630)

Statistics
----------------------------------------------------------
1 recursive calls
2 db block gets
11 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

#################################################################################

/*+ USE_HASH(DEPT) */

SQL> SELECT /*+ USE_HASH(DEPT) */   EMP_NO,ENAME,DNAME FROM EMP, DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND EMP_NO BETWEEN 630 AND 649;

EMP_NO ENAME DNAME
---------- -------------------- ----------------------
630 BHBBDMIC NREB Sales
649 GFGGXWADB ERQPA Sales
632 ZAYEY MQKDN Sales
633 VYXNIUTPE SZABML Sales
634 ZRPLO WIIJCEE Sales
635 FNTAMOSA GKSDYNDFH Sales
636 UGVRWYU JIFNV Sales
637 ZDZYA ROKMN Sales
638 QWFLXKS TXAFNBXQ Sales
639 CDIJZAFS IELKDO Sales
640 OHRDAIMZYU MBOQ Sales

EMP_NO ENAME DNAME
---------- -------------------- ----------------------
641 QBYUFOE WMTHK Sales
642 MYAOCG ZNGHA Sales
643 AFMNHMSMN TQXPDCN Sales
644 TGVLD FUNKBF Sales
645 PXIRVC QLSKHNX Sales
646 ORIEORIVX RQOBFUYV Sales
647 SOMQC BRPOBCJJQ Sales
648 VCJMRT GQGFCABY Sales
631 VQILI ACXTZ Sales

20 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 963559778

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 612 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 17 | 612 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 7 | 91 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 17 | 391 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 17 | 391 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_PK | 17 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

4 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")
filter("DEPT"."DEPT_NO"="EMP"."DEPT_NO")
6 - access("EMP_NO">=630 AND "EMP_NO"<=649)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1398 bytes sent via SQL*Net to client
619 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed

SQL>

#################################################################################

/*+ LEADING(EMP) USE_HASH(DEPT) */

SQL> SELECT /*+ LEADING(EMP) USE_HASH(DEPT) */ EMP_NO,ENAME,DNAME FROM EMP, DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND EMP_NO BETWEEN 630 AND 649;

EMP_NO ENAME DNAME
---------- -------------------- ----------------------
630 BHBBDMIC NREB Sales
631 VQILI ACXTZ Sales
632 ZAYEY MQKDN Sales
633 VYXNIUTPE SZABML Sales
634 ZRPLO WIIJCEE Sales
635 FNTAMOSA GKSDYNDFH Sales
636 UGVRWYU JIFNV Sales
637 ZDZYA ROKMN Sales
638 QWFLXKS TXAFNBXQ Sales
639 CDIJZAFS IELKDO Sales
640 OHRDAIMZYU MBOQ Sales

EMP_NO ENAME DNAME
---------- -------------------- ----------------------
641 QBYUFOE WMTHK Sales
642 MYAOCG ZNGHA Sales
643 AFMNHMSMN TQXPDCN Sales
644 TGVLD FUNKBF Sales
645 PXIRVC QLSKHNX Sales
646 ORIEORIVX RQOBFUYV Sales
647 SOMQC BRPOBCJJQ Sales
648 VCJMRT GQGFCABY Sales
649 GFGGXWADB ERQPA Sales

20 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3860232458

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 612 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 612 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 17 | 391 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_PK | 17 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEPT | 7 | 91 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

1 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")
3 - access("EMP_NO">=630 AND "EMP_NO"<=649)

Statistics
----------------------------------------------------------
1 recursive calls
2 db block gets
12 consistent gets
0 physical reads
0 redo size
1398 bytes sent via SQL*Net to client
619 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed

SQL>

######################################################################

/*+ USE_MERGE(JOBS) */

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

879 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2759725659

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 879 | 51861 | 11 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 879 | 51861 | 11 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 7 | 91 | 3 (0)| 00:00:01 |
| 3 | MERGE JOIN | | 879 | 40434 | 8 (13)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 23 | 414 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | JOBS_PK | 23 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 879 | 24612 | 6 (17)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 879 | 24612 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

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

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

SQL>

PT - Hints Using ORDERED and LEADING Examples

Using ORDERED and LEADING Hints

Join Order

Ordered
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

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

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

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

PT - OPTIMIZER Hints using Table and Index Access

Table and Index Access Path Hints

Table and Index Access Path

Full
Index
Index_FFS
Index_SS
No_Index

SET LINESIZE 180
SET AUTOTRACE ON ( PLUSTRACE ALREADY ENABLED)

FULL Tables Scan Hints

SQL> SELECT /*+ FULL(D) */ DEPT_NO, DNAME FROM DEPT D WHERE DEPT_NO=10;

DEPT_NO DNAME
---------- ----------------------
10 Administration

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("DEPT_NO"=10)

Statistics
----------------------------------------------------------
80 recursive calls
2 db block gets
81 consistent gets
0 physical reads
0 redo size
623 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SELECT /*+ FULL(DEPT) */ DEPT_NO, DNAME FROM DEPT D WHERE DEPT_NO=10;

DEPT_NO DNAME
---------- ----------------------
10 Administration

Execution Plan
----------------------------------------------------------
Plan hash value: 602043285

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - access("DEPT_NO"=10)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
597 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

SQL>

NOTE : Do not use Alias dept d instead give table name

SQL> SELECT /*+ FULL(E) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO=635;

EMP_NO ENAME SALARY

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

635 FNTAMOSA GKSDYNDFH 60200

Execution Plan

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

Plan hash value: 3956160932

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

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

| 0 | SELECT STATEMENT | | 1 | 24 | 5 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 24 | 5 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("EMP_NO"=635)

Statistics

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

200 recursive calls

9 db block gets

393 consistent gets

0 physical reads

1076 redo size

699 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

36 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> SELECT /*+ FULL(EMP) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO=635;

EMP_NO ENAME SALARY

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

635 FNTAMOSA GKSDYNDFH 60200

Execution Plan

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

Plan hash value: 4024650034

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

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

| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("EMP_NO"=635)

Statistics

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

1 recursive calls

0 db block gets

3 consistent gets

3 physical reads

0 redo size

699 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

Index Hints

SQL> SELECT /*+ INDEX(E INDEX_PK) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO=635;

EMP_NO ENAME SALARY

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

635 FNTAMOSA GKSDYNDFH 60200

Execution Plan

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

Plan hash value: 4024650034

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

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

| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("EMP_NO"=635)

Statistics

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

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

699 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

( This is the example using multiple Index)

SQL> SELECT /*+ INDEX (E EMP_PK EMP_DEPTNO_IX ) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610 AND DEPT_NO=60;

EMP_NO ENAME SALARY

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

600 AHYVFGNLRS AOVXOCDJH 56700

601 GBUKTVDY TVTQ 78300

602 RQHW SECLVR 80900

603 BFBCIPA LNKORQ 72200

604 GTKGSMJURW VQIFED 55800

605 QBGOCE NQJDIEFXD 81000

606 XXNEFJPPQ SAHGX 61800

607 KTIWMDWCIZ GBBB 61300

608 AQWFHXZ WGQRDQQM 62800

609 UAWDGPRRSO PHXT 72500

610 JNECDUASZ GPBWBE 78700

11 rows selected.

Execution Plan

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

Plan hash value: 2288396553

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

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

| 0 | SELECT STATEMENT | | 8 | 216 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 8 | 216 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | EMP_PK | 10 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("DEPT_NO"=60)

2 - access("EMP_NO">=600 AND "EMP_NO"<=610)

Statistics

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

4 recursive calls

0 db block gets

16 consistent gets

0 physical reads

0 redo size

1034 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

11 rows processed

SQL>

Index_FFS ( Fast Full Scan hints)

SQL> CREATE INDEX EMP_ENAME_JC_IX ON EMP(ENAME, JOB_CODE) COMPUTE STATISTICS;

Index created.

SQL> SELECT /*+ INDEX_FFS(E EMP_ENAME_JC_IX) */ ENAME, JOB_CODE FROM EMP E WHERE ENAME LIKE 'Z%';

ENAME JOB_

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

ZAPSU SSRLIG SL02

ZATDCTVSSA TUWQY SL02

ZAYEY MQKDN SL02

ZBOTTAPH CESYWCQB SL02

ZDCYJZRIE GSGDKQF SL02

ZDFLKXK SGIPR MG03

ZDMIWBZZN ABZMWMOCR SL02

ZDUT UEQTZ SL02

ZDVDBEMSA MOAZKC SL02

ZDZYA ROKMN SL02

ZELDVW JXIMCUJ SL02

ENAME JOB_

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

ZERDPOLO RTVDO SL02

ZGBYOSAW GZYQB HR02

ZGDEYEFD NXMAQDBD SL02

ZGGKHUS URMPHH SL02

ZHPH XWSWJH SL02

ZHWIIXH QJQKKYS IT05

ZILVN QQBCCDMN MG02

ZIUBBOMPE MZUQWAO AD06

ZIUOQP PSUJZ SL02

ZIVXT CPIR SL02

ZJSGY ZJELUQ SL02

ENAME JOB_

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

ZKCH OACWGHOGY ST02

ZKTTV KFXDFAL SL02

ZKXXQADJL BASDCVQT SL02

ZLJAVNC GHGUUO SL02

ZLXCPCPLW WNRPZT SL02

ZNII KDGQWLXB SL02

ZOAKRCS MYOHKZP SL02

ZOLIEFVJ JOHEUHXMK SL02

ZQCES OGEKAQZ SL02

ZQGNJLDAK ALKPGIG SL02

ZRNQRSDNVH BSFTIWHPQ SL02

ENAME JOB_

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

ZRPLO WIIJCEE SL02

ZTHXNXRPM BSBB FN03

ZUZQJO PSRIWT SL02

ZVMTJUWTNO KQLSJRMN SL02

ZWHBK WQNWNTI SL02

ZWKTN WVPAQBW AD06

ZWXRXB VSVVEJED SL02

ZXATQK DYDNVFKT SL02

ZXHTHFIAR PJNVYW SL02

ZYCWR UAQGDWND SL02

ZYIGXJV BZLJO SL02

ENAME JOB_

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

ZZROO ILREINIMQ SL02

45 rows selected.

Execution Plan

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

Plan hash value: 1191882812

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

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

| 0 | SELECT STATEMENT | | 42 | 882 | 3 (0)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| EMP_ENAME_JC_IX | 42 | 882 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("ENAME" LIKE 'Z%')

Statistics

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

1 recursive calls

0 db block gets

11 consistent gets

4 physical reads

0 redo size

2064 bytes sent via SQL*Net to client

630 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

45 rows processed

SQL>

SQL> SELECT /*+ INDEX_FFS(E EMP_ENAME_JC_IX) */ ENAME, JOB_CODE FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610;

ENAME JOB_

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

AHYVFGNLRS AOVXOCDJH SL02

GBUKTVDY TVTQ SL02

RQHW SECLVR SL02

BFBCIPA LNKORQ SL02

GTKGSMJURW VQIFED SL02

QBGOCE NQJDIEFXD SL02

XXNEFJPPQ SAHGX SL02

KTIWMDWCIZ GBBB SL02

AQWFHXZ WGQRDQQM SL02

UAWDGPRRSO PHXT SL02

JNECDUASZ GPBWBE SL02

11 rows selected.

Execution Plan

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

Plan hash value: 2288396553

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

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

| 0 | SELECT STATEMENT | | 10 | 250 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 10 | 250 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | EMP_PK | 10 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("EMP_NO">=600 AND "EMP_NO"<=610)

Statistics

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

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

936 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11 rows processed

Index_SS (Skip Scan hints)

SQL> SELECT /* INDEX_SS ( C CUST_CLASS_EMAIL_IX ) */ * FROM CUSTOMERS C WHERE CUST_EMAIL = 'lyndia.bangle@oracle.com';

CUSTOMER_ID CUST_FIRST_NAME CUST_LAST_NAME NLS NLS_TERRITORY CREDIT_LIMIT

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

CUST_EMAIL ACCOUNT_MGR_ID CUSTOMER_

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

CUSTOMER_CLASS SUGGESTIONS DOB M P PREFERRED_ADDRESS PREFERRED_CARD

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

45550 lyndia bangle th THAILAND 1392

lyndia.bangle@oracle.com 154 02-OCT-18

Occasional Music 06-NOV-71 Y N 55550 55550

Execution Plan

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

Plan hash value: 3336747554

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

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

| 0 | SELECT STATEMENT | | 1 | 114 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 1 | 114 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | CUST_EMAIL_IX | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("CUST_EMAIL"='lyndia.bangle@oracle.com')

Statistics

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

61 recursive calls

52 db block gets

124 consistent gets

1 physical reads

9804 redo size

1770 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

14 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

No_Index Hints

SQL> SELECT /*+ NO_INDEX (E EMP_PK ) */ EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610 AND DEPT_NO=60;

EMP_NO ENAME SALARY

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

600 AHYVFGNLRS AOVXOCDJH 56700

601 GBUKTVDY TVTQ 78300

602 RQHW SECLVR 80900

603 BFBCIPA LNKORQ 72200

604 GTKGSMJURW VQIFED 55800

605 QBGOCE NQJDIEFXD 81000

606 XXNEFJPPQ SAHGX 61800

607 KTIWMDWCIZ GBBB 61300

608 AQWFHXZ WGQRDQQM 62800

609 UAWDGPRRSO PHXT 72500

610 JNECDUASZ GPBWBE 78700

11 rows selected.

Execution Plan

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

Plan hash value: 3956160932

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

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

| 0 | SELECT STATEMENT | | 8 | 216 | 5 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 8 | 216 | 5 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("EMP_NO">=600 AND "EMP_NO"<=610 AND "DEPT_NO"=60)

Statistics

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

1 recursive calls

2 db block gets

14 consistent gets

0 physical reads

0 redo size

1034 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11 rows processed

SQL> SELECT EMP_NO, ENAME, SALARY FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610 AND DEPT_NO=60;

EMP_NO ENAME SALARY

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

600 AHYVFGNLRS AOVXOCDJH 56700

601 GBUKTVDY TVTQ 78300

602 RQHW SECLVR 80900

603 BFBCIPA LNKORQ 72200

604 GTKGSMJURW VQIFED 55800

605 QBGOCE NQJDIEFXD 81000

606 XXNEFJPPQ SAHGX 61800

607 KTIWMDWCIZ GBBB 61300

608 AQWFHXZ WGQRDQQM 62800

609 UAWDGPRRSO PHXT 72500

610 JNECDUASZ GPBWBE 78700

11 rows selected.

Execution Plan

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

Plan hash value: 2288396553

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

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

| 0 | SELECT STATEMENT | | 8 | 216 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 8 | 216 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | EMP_PK | 10 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("DEPT_NO"=60)

2 - access("EMP_NO">=600 AND "EMP_NO"<=610)

Statistics

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

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1034 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11 rows processed

HOME

Thursday 2 April 2020

PT - Hints using Join Method examples

PT - Hints Using ORDERED and LEADING Examples

PT - OPTIMIZER Hints using Table and Index Access

FULL Tables Scan Hints

Index Hints

Index_FFS ( Fast Full Scan hints)

Index_SS (Skip Scan hints)

No_Index Hints