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:
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_INDEX – Instructs 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