Hi I have a complex query for which I am trying to figure out the optimal tuning order . I am proceeding with the following assumption
start with the table and condition which has the least number of rows and keep the large tables for join at the end.
So I have decided an order for my first four tables as follows .
select /*+gather_plan_statistics leading( A D C E) */ A.bsid,C.trqty AS S_QTY,
C.CR AS EX_CM from tbl1 A INNER JOIN tbl2 D
ON D.EID = A.EID AND D.INID = A.bsid AND D.NUMBEME = 'QUCD'
INNER JOIN tbl3 C ON C.SID = A.bsid AND C.EID = A.EID
INNER JOIN tbl4 E ON E.EID = A.EID AND E.ACNO = (CASE WHEN C.SECMOVTYPE = 'DELI' THEN C.SACC ELSE C.CTRSECACC END) AND
E.ACERID = (CASE WHEN C.STYPE = 'DELI' THEN C.ENTTRD ELSE C.ENTCTID END)
Plan
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |
1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 483K|00:00:01.35 | 520K| 25480 | |
| 1 | NESTED LOOPS | | 1 | 18 | 483K|00:00:01.35 | 520K| 25480 | |
|* 2 | HASH JOIN | | 1 | 241K| 617K|00:00:00.53 | 216K| 25449 | 1035K| 1035K| 1651K (0)|
| 3 | NESTED LOOPS | | 1 | 5843 | 6166 |00:00:00.02 | 5844 | 108 | |
| 4 | INDEX FAST FULL SCAN| IDX_tbl1_02 | 1 | 9393 | 9432 |00:00:00.01 | 95 | 87 | |
|* 5 | INDEX UNIQUE SCAN | IDX_tbl2_01 | 9432 | 1 | 6166 |00:00:00.01 | 5749 | 21 | |
| 6 | TABLE ACCESS FULL | tbl3 | 1 | 120K| 121K|00:00:00.33 | 210K| 25341 | |
|* 7 | INDEX UNIQUE SCAN | IDX_tbl4 | 617K| 1 | 483K|00:00:00.73 | 304K| 31 | |
--------------------------------------------------------------------------------------------------------------------------
Then I tried adding a fifth table G
select /*+gather_plan_statistics leading( A D C E) */ A.bsid,C.trqty AS S_QTY,
C.CR AS EX_CM from tbl1 A INNER JOIN tbl2 D
ON D.EID = A.EID AND D.INID = A.bsid AND D.NUMBEME = 'QUCD'
INNER JOIN tbl3 C ON C.SID = A.bsid AND C.EID = A.EID
INNER JOIN tbl4 E ON E.EID = A.EID AND E.ACNO = (CASE WHEN C.SECMOVTYPE = 'DELI' THEN C.SACC ELSE C.CTRSECACC END) AND
E.ACERID = (CASE WHEN C.STYPE = 'DELI' THEN C.ENTTRD ELSE C.ENTCTID END)
INNER JOIN tbl5 G ON G.LANG_ID = 1 and G.NAME = 'd_TrdStatus' AND G.VALUE = C.CURUS and G.CODE_ID = 2000000110
Line number 07 is the fifth table G which has been newly added.
Plan
---------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
---------------------------
| 0 | SELECT STATEMENT | | 1 | | 483K|00:00:01.35 | 474K| 25471 |
|* 1 | HASH JOIN | | 1 | 12 | 483K|00:00:01.35 | 474K| 25471 | 24M| 5228K| 39M (0)|
| 2 | NESTED LOOPS | | 1 | 18 | 483K|00:00:01.13 | 474K| 25471 |
|* 3 | HASH JOIN | | 1 | 241K| 617K|00:00:00.43 | 211K| 25448 | 1035K| 1035K| 1632K (0)|
| 4 | NESTED LOOPS | | 1 | 5843 | 6166 |00:00:00.02 | 5844 | 107 |
| 5 | INDEX FAST FULL SCAN | IDX_tbl1_02 | 1 | 9393 | 9432 |00:00:00.01 | 95 | 87 |
|* 6 | INDEX UNIQUE SCAN | IDX_tbl2_01 | 9432 | 1 | 6166 |00:00:00.01 | 5749 | 20 |
| 7 | TABLE ACCESS FULL | tbl3 | 1 | 120K| 121K|00:00:00.26 | 205K| 25341 |
|* 8 | INDEX UNIQUE SCAN | IDX_tbl4 | 617K| 1 | 483K|00:00:00.60 | 263K| 23 |
|* 9 | TABLE ACCESS BY INDEX ROWID| tbl5 | 1 | 1 | 94 |00:00:00.01 | 56 | 0 |
|* 10 | INDEX RANGE SCAN | IDX_tbl5_01 | 1 | 111 | 94 |00:00:00.01 | 15 | 0 |
--------------------------------------------------------------------------------------------------------------------------
AS you can see the plan is initially the same but a new hash join is added to join the table tbl5 with the output of four tables . The table tbl5 returns only 94 rows and so I believe hashing the table tbl5 in memory would be beneficial for the hash join .
According to oracle documentation
Because the orders table is small relative to the order_items table, which is 6 times larger, the database hashes orders. In a hash join, the data set for the build table always appears first in the list of operations (Step 2). In Step 3, the database performs a full scan of the larger order_items later, probing the hash table for each row.
https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm#TGSQL245
I also tried writing the query as a subquery in the below form
select /*+ leading (G adce) */ adce.bsid,adce.col2,adce.col3 from tbl5 G,(query joinin the tables adce) adce where G.LANG_ID = 1 and G.NAME = 'd_TrdStatus' AND G.VALUE = C.CURUS and G.CODE_ID = 2000000110
but after rewriting in the format the varies completely and it starts joining tbl5 and tbl1 at the start.
Why is the join order not picking tbl5 to hash in memory ? How can I rewrite the query to make sute it joins tbl5 with the result of other 4 tables.
Thanks