Hi Gurus,
Oracle version 11.1.0.7
Please see the below two ..execution plans .
both are queried to the same tables in two ways. In the First plan TAB_ABC table joined for 16 times and in the second plan joined only once.
which plan is the better? The one with lower cost with more joins OR the one with higher cost with only one join.
FIRST PLAN-1
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3733 | 3762 (1)| 00:00:46 |
| 1 | NESTED LOOPS | | 1 | 42 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | RFXYZ | 1 | 17 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_RFXYZ | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | TAB_LKP | 32 | 800 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_LKP | 1 | | 0 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 42 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | RFXYZ | 1 | 17 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_RFXYZ | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | LOOKUP | 32 | 800 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_LOOKUP | 1 | | 0 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | | | | |
| 12 | NESTED LOOPS | | 1 | 27 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | PK_MEMXYZ | 1 | 14 | 3 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_MEMABC | 1 | | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | MEMXYZ | 1 | 13 | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | TAB_LKP | 1 | 22 | 2 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_TAB_LKP | 1 | | 1 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | | | | |
| 19 | NESTED LOOPS | | 1 | 27 | 4 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_MEMXYZ | 1 | 14 | 3 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_MEMABC | 1 | | 0 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | MEMXYZ | 1 | 13 | 1 (0)| 00:00:01 |
|* 23 | COUNT STOPKEY | | | | | |
| 24 | VIEW | | 1 | 3733 | 3762 (1)| 00:00:46 |
|* 25 | SORT GROUP BY STOPKEY | | 1 | 554 | 3762 (1)| 00:00:46 |
|* 26 | COUNT STOPKEY | | | | | |
|* 27 | HASH JOIN RIGHT OUTER | | 49 | 27146 | 2295 (2)| 00:00:28 |
| 28 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 29 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 32 | HASH JOIN RIGHT OUTER | | 49 | 25921 | 2292 (2)| 00:00:28 |
| 33 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 34 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 37 | HASH JOIN RIGHT OUTER | | 49 | 24696 | 2288 (2)| 00:00:28 |
| 38 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 39 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 40 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 42 | HASH JOIN RIGHT OUTER | | 49 | 23471 | 2285 (1)| 00:00:28 |
| 43 | VIEW | | 1 | 13 | 3 (34)| 00:00:01 |
| 44 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 47 | HASH JOIN RIGHT OUTER | | 49 | 22834 | 2281 (1)| 00:00:28 |
| 48 | VIEW | | 1 | 13 | 3 (34)| 00:00:01 |
| 49 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 51 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 52 | HASH JOIN RIGHT OUTER | | 49 | 22197 | 2278 (1)| 00:00:28 |
| 53 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 54 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 55 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 56 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 57 | HASH JOIN RIGHT OUTER | | 49 | 20972 | 2274 (1)| 00:00:28 |
| 58 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 59 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 60 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 61 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 62 | HASH JOIN RIGHT OUTER | | 49 | 19747 | 2271 (1)| 00:00:28 |
| 63 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 64 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 65 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 66 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 67 | HASH JOIN RIGHT OUTER | | 49 | 18522 | 2267 (1)| 00:00:28 |
| 68 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 69 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 70 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 71 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 72 | HASH JOIN RIGHT OUTER | | 49 | 17297 | 2264 (1)| 00:00:28 |
| 73 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 74 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 75 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 76 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 77 | HASH JOIN RIGHT OUTER | | 49 | 16072 | 2260 (1)| 00:00:28 |
| 78 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 79 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 80 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 81 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 82 | HASH JOIN RIGHT OUTER | | 49 | 14847 | 2257 (1)| 00:00:28 |
| 83 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 84 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 85 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 86 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 87 | HASH JOIN RIGHT OUTER | | 49 | 13622 | 2253 (1)| 00:00:28 |
| 88 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 89 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 90 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 91 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 92 | HASH JOIN RIGHT OUTER | | 49 | 12397 | 2250 (1)| 00:00:27 |
| 93 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 94 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|* 95 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 96 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|* 97 | HASH JOIN RIGHT OUTER | | 49 | 11172 | 2246 (1)| 00:00:27 |
| 98 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 99 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|*100 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|*101 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|*102 | HASH JOIN RIGHT OUTER | | 49 | 9947 | 2243 (1)| 00:00:27 |
| 103 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 104 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|*105 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|*106 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
|*107 | HASH JOIN OUTER | | 49 | 8722 | 2239 (1)| 00:00:27 |
| 108 | NESTED LOOPS | | | | | |
| 109 | NESTED LOOPS | | 49 | 7497 | 2236 (1)| 00:00:27 |
| 110 | NESTED LOOPS | | 49 | 6321 | 2138 (1)| 00:00:26 |
| 111 | NESTED LOOPS | | 977 | 59597 | 183 (0)| 00:00:03 |
| 112 | TABLE ACCESS BY INDEX ROWID | TAB_FEA | 1 | 11 | 2 (0)| 00:00:01 |
|*113 | INDEX RANGE SCAN | PK_TAB_FEA | 1 | | 1 (0)| 00:00:01 |
|*114 | TABLE ACCESS BY INDEX ROWID | TAB_PRG | 977 | 48850 | 181 (0)| 00:00:03 |
|*115 | INDEX RANGE SCAN | IDX_TAB_PRG_01 | 3650 | | 13 (0)| 00:00:01 |
| 116 | TABLE ACCESS BY INDEX ROWID | TAB_MEM | 1 | 68 | 2 (0)| 00:00:01 |
|*117 | INDEX UNIQUE SCAN | IDX_TAB_MEM_01 | 1 | | 1 (0)| 00:00:01 |
|*118 | FILTER | | | | | |
| 119 | WINDOW BUFFER | | 1 | 48 | 3 (0)| 00:00:01 |
| 120 | TABLE ACCESS BY INDEX ROWID| TAB_MEM | 1 | 48 | 3 (0)| 00:00:01 |
|*121 | INDEX UNIQUE SCAN | IDX_TAB_MEM_01 | 1 | | 2 (0)| 00:00:01 |
|*122 | INDEX UNIQUE SCAN | PK_MS | 1 | | 1 (0)| 00:00:01 |
| 123 | TABLE ACCESS BY INDEX ROWID | TAB_MS | 1 | 24 | 2 (0)| 00:00:01 |
| 124 | VIEW | | 1 | 25 | 3 (34)| 00:00:01 |
| 125 | HASH UNIQUE | | 1 | 25 | 3 (34)| 00:00:01 |
|*126 | TABLE ACCESS BY INDEX ROWID | TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|*127 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------
SECOND PLAN-2
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 977 | 3289K| 7027 (14)| 00:01:25 |
| 1 | NESTED LOOPS | | 1 | 42 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | RFXYZ | 1 | 17 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_RFXYZ | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | TAB_LKP | 32 | 800 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_TAB_LKP | 1 | | 0 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 42 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | RFXYZ | 1 | 17 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_RFXYZ | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | TAB_LKP | 32 | 800 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_TAB_LKP | 1 | | 0 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | | | | |
| 12 | NESTED LOOPS | | 1 | 27 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | PK_MEMXYZ | 1 | 14 | 3 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_MEMABC | 1 | | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | MEMXYZ | 1 | 13 | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | TAB_LKP | 1 | 22 | 2 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_TAB_LKP | 1 | | 1 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | | | | |
| 19 | NESTED LOOPS | | 1 | 27 | 4 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_MEMXYZ | 1 | 14 | 3 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_MEMABC | 1 | | 0 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | MEMXYZ | 1 | 13 | 1 (0)| 00:00:01 |
|* 23 | COUNT STOPKEY | | | | | |
| 24 | VIEW | | 977 | 3289K| 7027 (14)| 00:01:25 |
|* 25 | SORT GROUP BY STOPKEY | | 977 | 335K| 7027 (14)| 00:01:25 |
|* 26 | COUNT STOPKEY | | | | | |
| 27 | NESTED LOOPS | | | | | |
| 28 | NESTED LOOPS | | 977 | 335K| 7026 (14)| 00:01:25 |
| 29 | NESTED LOOPS | | 977 | 312K| 5072 (20)| 00:01:01 |
|* 30 | HASH JOIN RIGHT OUTER | | 977 | 183K| 186 (1)| 00:00:03 |
| 31 | VIEW | | 1 | 118 | 2 (0)| 00:00:01 |
| 32 | HASH GROUP BY | | 1 | 25 | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID| TAB_ABC | 1 | 25 | 2 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | IDX_TAB_ABC_02 | 1 | | 1 (0)| 00:00:01 |
| 35 | NESTED LOOPS | | | | | |
| 36 | NESTED LOOPS | | 977 | 72298 | 183 (0)| 00:00:03 |
| 37 | TABLE ACCESS BY INDEX ROWID| TAB_FEA | 1 | 11 | 2 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | PK_TAB_FEA | 1 | | 1 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | IDX_TAB_PRG_01 | 3650 | | 13 (0)| 00:00:01 |
|* 40 | TABLE ACCESS BY INDEX ROWID | TAB_PRG | 977 | 61551 | 181 (0)| 00:00:03 |
|* 41 | VIEW PUSHED PREDICATE | | 1 | 136 | 5 (20)| 00:00:01 |
|* 42 | WINDOW SORT PUSHED RANK | | 1 | 72 | 5 (20)| 00:00:01 |
|* 43 | FILTER | | | | | |
| 44 | TABLE ACCESS BY INDEX ROWID| TAB_MEM | 1 | 72 | 4 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | IDX_TAB_MEM_01 | 1 | | 3 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | PK_MS | 1 | | 1 (0)| 00:00:01 |
| 47 | TABLE ACCESS BY INDEX ROWID | TAB_MS | 1 | 24 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Please help me in this... whether the COST is the only thing we need to consider while tuning or COST doesn't matter. For example i have two different SQLs with different costs the one which has higher cost has better response than the other. why?
Appreciate your help.
Regards,
Mike