This article presents how you can suggest join order for tables. It’s recommended to read following article:
Hints to control Join Operations 11g
Let’s prepare our data model and feed it with data
DROP TABLE master_tbl; DROP TABLE child_tbl; DROP TABLE child_tbl1; DROP TABLE child_tbl2; CREATE TABLE master_tbl (master_id NUMBER); CREATE TABLE child_tbl (child_id NUMBER, master_id NUMBER); CREATE TABLE child_tbl1 (child_id NUMBER, master_id NUMBER); CREATE TABLE child_tbl2 (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); INSERT INTO child_tbl1(child_id, master_id) VALUES(j, i); INSERT INTO child_tbl2(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); exec DBMS_STATS.gather_table_stats(USER, 'CHILD_TBL1', cascade=>TRUE); exec DBMS_STATS.gather_table_stats(USER, 'CHILD_TBL2', cascade=>TRUE); SELECT COUNT(*) FROM master_tbl; SELECT COUNT(*) FROM child_tbl; SELECT COUNT(*) FROM child_tbl1; SELECT COUNT(*) FROM child_tbl2; |
ORDERED – this hint instructs Oracle to join tables in the order in which they appear in the FROM clause.It’s very useful if you want to choose inner and outer table in explain plan.
In following example order of tables in FROM clause is following master_tbl, child_tbl so master_tbl is outer table and child_tbl is inner
explain plan FOR SELECT /*+ ORDERED */ * 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 | ----------------------------------------- |
to change it you need to change order in FROM clause to child_tbl, master_tbl. Now outer table is child_tbl and inner table is master_tbl
explain plan FOR SELECT /*+ ORDERED */ * FROM child_tbl c, master_tbl m WHERE m.master_id=c.master_id; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 30615395 ----------------------------------------- | Id | Operation | Name | ----------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| CHILD_TBL | | 3 | TABLE ACCESS FULL| MASTER_TBL | ----------------------------------------- |
Simple example for many tables. FROM together with ORDERED is forcing to make following order for joins:
1. child_tbl is joined with master_tbl
2. result from 1 step is joined with child_tbl2
3. result from step 2 is joined with child_tbl1
explain plan FOR SELECT /*+ ORDERED */ * FROM child_tbl c, master_tbl m, child_tbl2 c2, child_tbl1 c1 WHERE m.master_id=c.master_id AND m.master_id=c1.master_id AND m.master_id=c2.master_id; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 1057942285 ------------------------------------------- | Id | Operation | Name | ------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL | CHILD_TBL1 | | 3 | HASH JOIN | | | 4 | TABLE ACCESS FULL | CHILD_TBL2 | | 5 | HASH JOIN | | | 6 | TABLE ACCESS FULL| CHILD_TBL | | 7 | TABLE ACCESS FULL| MASTER_TBL | ------------------------------------------- |
Another example for many tables
1. child_tbl is joined with child_tbl2
2. result from step 1 is joined with child_tbl1
3. result from setp 2 is joined with master_tbl
explain plan FOR SELECT /*+ ORDERED */ * FROM child_tbl c, child_tbl2 c2, child_tbl1 c1, master_tbl m WHERE m.master_id=c.master_id AND m.master_id=c1.master_id AND m.master_id=c2.master_id; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 3081111749 --------------------------------------------- | Id | Operation | Name | --------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL | MASTER_TBL | | 3 | MERGE JOIN CARTESIAN | | | 4 | MERGE JOIN CARTESIAN| | | 5 | TABLE ACCESS FULL | CHILD_TBL | | 6 | BUFFER SORT | | | 7 | TABLE ACCESS FULL | CHILD_TBL2 | | 8 | BUFFER SORT | | | 9 | TABLE ACCESS FULL | CHILD_TBL1 | --------------------------------------------- |
Oracle recommends that you use the LEADING hint, which is more versatile than the ORDERED hint.
LEADING – this hints instructs Oracle to use specified set of tables as first to join in order specified by this hint.
In this example we are trying to force first master_tbl with child_tbl and rest of the joins depend on optimizer choice. It’s very nice hint comparing to ORDERED because we don’t need to modify our query.
explain plan FOR SELECT /*+ LEADING(m c) */ * FROM child_tbl c, child_tbl2 c2, child_tbl1 c1, master_tbl m WHERE m.master_id=c.master_id AND m.master_id=c1.master_id AND m.master_id=c2.master_id; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 588331670 ------------------------------------------- | Id | Operation | Name | ------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL | CHILD_TBL1 | | 3 | HASH JOIN | | | 4 | TABLE ACCESS FULL | CHILD_TBL2 | | 5 | HASH JOIN | | | 6 | TABLE ACCESS FULL| MASTER_TBL | | 7 | TABLE ACCESS FULL| CHILD_TBL | ------------------------------------------- |
This hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting LEADING hints, then all of them are ignored. If you specify the ORDERED hint, it overrides all LEADING hints.
Have a fun 🙂
Tomasz