Oracle Database Commands and Practical Examples: 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>

Oracle Database Commands and Practical Examples

HOME

Thursday 2 April 2020

PT - Hints using Join Method examples

No comments:

Post a Comment