Hints to control Join Operations 11g

This article presents hints which can be used to force Oracle to use specific join operation between two tables. This article is not trying to prove which joins is better but just try to show how to force Oracle database to “play game” as we want even if our tactic is wrong.

It’s recommended to read following article before it:

Join operation in Oracle

Hints to control HASH join

Let’s prepare our data model and feed it with data

DROP TABLE master_tbl;
DROP TABLE child_tbl;
 
CREATE TABLE master_tbl
(master_id NUMBER);
 
CREATE TABLE child_tbl
(child_id NUMBER,
 master_id NUMBER);
 
BEGIN
  FOR i IN 1..1000
  LOOP
    INSERT INTO master_tbl VALUES(i);
  END LOOP;
  COMMIT;
END;
/
 
BEGIN
  FOR i IN 1..1000
  LOOP
    FOR j IN 1..2
    LOOP
      INSERT INTO child_tbl(child_id, master_id) VALUES(j, i);
    END LOOP;
  END LOOP;
  COMMIT;
END;
/
 
exec DBMS_STATS.gather_table_stats(USER, 'MASTER_TBL', cascade=>TRUE);
exec DBMS_STATS.gather_table_stats(USER, 'CHILD_TBL', cascade=>TRUE);
 
SELECT COUNT(*) FROM master_tbl;
SELECT COUNT(*) FROM child_tbl;

USE_HASH – this hints forces to use HASH join between master_tbl and child_tbl

/*+ USE_HASH([@queryblock] <tablespec> <tablespec>) */

explain plan FOR
SELECT /*+ USE_HASH(m c) */ * 
FROM master_tbl m, child_tbl c
WHERE m.master_id=c.master_id;
 
SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic'));
 
Plan hash VALUE: 1625664568 
-----------------------------------------
| Id  | Operation          | Name       |
-----------------------------------------
|   0 | SELECT STATEMENT   |            |
|   1 |  HASH JOIN         |            |
|   2 |   TABLE ACCESS FULL| MASTER_TBL |
|   3 |   TABLE ACCESS FULL| CHILD_TBL  |
----------------------------------------

NO_USE_HASH – this hints blocks using HASH join between master_tbl and child_tbl. So only option now is MERGE or NESTED join.

/*+ NO_USE_HASH([@queryblock] <tablespec> <tablespec>) */

explain plan FOR
SELECT /*+ NO_USE_HASH(m c) */ * 
FROM master_tbl m, child_tbl c
WHERE m.master_id=c.master_id
  AND c.master_id=10;
 
SELECT * FROM TABLE(DBMS_XPLAN.display);
 
Plan hash VALUE: 2178449094 
------------------------------------------
| Id  | Operation           | Name       |
------------------------------------------
|   0 | SELECT STATEMENT    |            |
|   1 |  MERGE JOIN         |            |
|   2 |   SORT JOIN         |            |
|   3 |    TABLE ACCESS FULL| MASTER_TBL |
|   4 |   SORT JOIN         |            |
|   5 |    TABLE ACCESS FULL| CHILD_TBL  |
------------------------------------------

Hints to control NESTED join

USE_NL – this hint enforces to use NESTED join between master_tbl and child_tbl

/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */

explain plan FOR
SELECT /*+ USE_NL(m c) */ * 
FROM master_tbl m, child_tbl c
WHERE m.master_id=c.master_id;
 
SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic'));
 
Plan hash VALUE: 428765321 
-----------------------------------------
| Id  | Operation          | Name       |
-----------------------------------------
|   0 | SELECT STATEMENT   |            |
|   1 |  NESTED LOOPS      |            |
|   2 |   TABLE ACCESS FULL| MASTER_TBL |
|   3 |   TABLE ACCESS FULL| CHILD_TBL  |
-----------------------------------------

NO_USE_NL – this hint prevents to use NESTED join between master_tbl and child_tbl so the only option for Oracle is MERGE or HASH join

/*+ NO_USE_NL([@queryblock] <tablespec> <tablespec>) */

explain plan FOR
SELECT /*+ NO_USE_NL(m c) */ * 
FROM master_tbl m, child_tbl c
WHERE m.master_id=c.master_id;
 
SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic'));
 
Plan hash VALUE: 1625664568
-----------------------------------------
| Id  | Operation          | Name       |
-----------------------------------------
|   0 | SELECT STATEMENT   |            |
|   1 |  HASH JOIN         |            |
|   2 |   TABLE ACCESS FULL| MASTER_TBL |
|   3 |   TABLE ACCESS FULL| CHILD_TBL  |
-----------------------------------------

Hints to control MERGE join

USE_MERGE – this hint enforces to use MERGE join between master_tbl and child_tbl

/*+ USE_MERGE([@queryblock] <tablespec> <tablespec>) */

explain plan FOR
SELECT /*+ USE_MERGE(m c) */ * 
FROM master_tbl m, child_tbl c
WHERE m.master_id=c.master_id;
 
SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic'));
 
Plan hash VALUE: 2178449094
 
------------------------------------------
| Id  | Operation           | Name       |
------------------------------------------
|   0 | SELECT STATEMENT    |            |
|   1 |  MERGE JOIN         |            |
|   2 |   SORT JOIN         |            |
|   3 |    TABLE ACCESS FULL| MASTER_TBL |
|   4 |   SORT JOIN         |            |
|   5 |    TABLE ACCESS FULL| CHILD_TBL  |
------------------------------------------

NO_USE_MERGE – this hint prevents to use MERGE join between master_tbl and child_tbl so the only option for Oracle is MERGE or HASH join

/*+ NO_USE_MERGE([@queryblock] <tablespec> <tablespec>) */

explain plan FOR
SELECT /*+ USE_NO_MERGE(m c) */ * 
FROM master_tbl m, child_tbl c
WHERE m.master_id=c.master_id;
 
SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic'));
 
Plan hash VALUE: 1625664568
 
-----------------------------------------
| Id  | Operation          | Name       |
-----------------------------------------
|   0 | SELECT STATEMENT   |            |
|   1 |  HASH JOIN         |            |
|   2 |   TABLE ACCESS FULL| MASTER_TBL |
|   3 |   TABLE ACCESS FULL| CHILD_TBL  |
-----------------------------------------

Extra hint for NESTED join however it just instructs and don’t force NESTED join

USE_NL_WITH_INDEXInstructs the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table

/*+ USE_NL_WITH_INDEX([@queryblock] <tablespec> <index_name>) */

The following conditions apply:

  • If no index is specified, then the optimizer must be able to use some index with at least one join predicate as the index key.

  • If an index is specified, then the optimizer must be able to use that index with at least one join predicate as the index key.

Have a fun 🙂

Tomasz

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.