this is the other explain plan
Code:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2448 | 661 |
| 1 | SORT ORDER BY | | 12 | 2448 | 661 |
| 2 | CONCATENATION | | | | |
| 3 | NESTED LOOPS | | 9 | 1530 | 64 |
| 4 | NESTED LOOPS | | 6 | 1224 | 91 |
| 5 | NESTED LOOPS | | 76 | 14592 | 91 |
| 6 | NESTED LOOPS | | 9 | 1332 | 64 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|* 7 | TABLE ACCESS BY INDEX ROWID | PROMSKU | 48 | 1680 | 15 |
|* 8 | INDEX RANGE SCAN | PK_PROMSKU | 64 | | 3 |
|* 9 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 139K| 14M| 2 |
|* 10 | INDEX UNIQUE SCAN | PK_ITEM_MASTER | 1 | | 1 |
|* 11 | TABLE ACCESS BY INDEX ROWID| ITEM_MASTER | 1 | 26 | 3 |
|* 12 | INDEX UNIQUE SCAN | PK_ITEM_MASTER | 1252K| | 2 |
| 13 | TABLE ACCESS BY INDEX ROWID | ITEM_ZONE_PRICE | 8610 | 184K| 3 |
|* 14 | INDEX RANGE SCAN | PK_ITEM_ZONE_PRICE | 8 | | 2 |
|* 15 | INDEX UNIQUE SCAN | PK_PRICE_ZONE_GROUP_STORE | 61 | 732 | |
| 16 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 1252K| 26M| |
|* 17 | INDEX UNIQUE SCAN | PK_ITEM_MASTER | 1 | | 1 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|* 18 | FILTER | | | | |
| 19 | NESTED LOOPS | | 9 | 1530 | 64 |
| 20 | NESTED LOOPS | | 6 | 1224 | 91 |
| 21 | NESTED LOOPS | | 76 | 14592 | 91 |
| 22 | NESTED LOOPS | | 9 | 1332 | 64 |
|* 23 | TABLE ACCESS BY INDEX ROWID | PROMSKU | 48 | 1680 | 15 |
|* 24 | INDEX RANGE SCAN | PK_PROMSKU | 64 | | 3 |
|* 25 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 139K| 14M| 2 |
|* 26 | INDEX RANGE SCAN | ITEM_MASTER_I2 | 1 | | 1 |
| 27 | TABLE ACCESS BY INDEX ROWID | ITEM_ZONE_PRICE | 8610 | 184K| 3 |
|* 28 | INDEX RANGE SCAN | PK_ITEM_ZONE_PRICE | 8 | | 2 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|* 29 | INDEX UNIQUE SCAN | PK_PRICE_ZONE_GROUP_STORE | 61 | 732 | |
| 30 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 1252K| 26M| |
|* 31 | INDEX UNIQUE SCAN | PK_ITEM_MASTER | 1 | | 1 |
|* 32 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 1 | 26 | 3 |
|* 33 | INDEX UNIQUE SCAN | PK_ITEM_MASTER | 1252K| | 2 |
|* 34 | FILTER | | | | |
| 35 | NESTED LOOPS | | 9 | 1530 | 64 |
| 36 | NESTED LOOPS | | 6 | 1224 | 91 |
| 37 | NESTED LOOPS | | 76 | 14592 | 91 |
| 38 | NESTED LOOPS | | 9 | 1332 | 64 |
|* 39 | TABLE ACCESS BY INDEX ROWID | PROMSKU | 48 | 1680 | 15 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|* 40 | INDEX RANGE SCAN | PK_PROMSKU | 64 | | 3 |
|* 41 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 139K| 14M| 2 |
|* 42 | INDEX RANGE SCAN | ITEM_MASTER_I1 | 1 | | 1 |
| 43 | TABLE ACCESS BY INDEX ROWID | ITEM_ZONE_PRICE | 8610 | 184K| 3 |
|* 44 | INDEX RANGE SCAN | PK_ITEM_ZONE_PRICE | 8 | | 2 |
|* 45 | INDEX UNIQUE SCAN | PK_PRICE_ZONE_GROUP_STORE | 61 | 732 | |
| 46 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 1252K| 26M| |
|* 47 | INDEX UNIQUE SCAN | PK_ITEM_MASTER | 1 | | 1 |
|* 48 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 1 | 26 | 3 |
|* 49 | INDEX UNIQUE SCAN | PK_ITEM_MASTER | 1252K| | 2 |
| 50 | NESTED LOOPS | | 6 | 1224 | 91 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
| 51 | NESTED LOOPS | | 76 | 14592 | 91 |
| 52 | NESTED LOOPS | | 9 | 1530 | 64 |
| 53 | NESTED LOOPS | | 9 | 1332 | 64 |
|* 54 | TABLE ACCESS BY INDEX ROWID | PROMSKU | 48 | 1680 | 15 |
|* 55 | INDEX RANGE SCAN | PK_PROMSKU | 64 | | 3 |
|* 56 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 139K| 14M| 2 |
|* 57 | INDEX RANGE SCAN | PK_ITEM_MASTER | 1 | | 1 |
|* 58 | TABLE ACCESS BY INDEX ROWID| ITEM_MASTER | 1 | 26 | 3 |
|* 59 | INDEX UNIQUE SCAN | PK_ITEM_MASTER | 1252K| | 2 |
| 60 | TABLE ACCESS BY INDEX ROWID | ITEM_MASTER | 1252K| 26M| |
|* 61 | INDEX UNIQUE SCAN | PK_ITEM_MASTER | 1 | | 1 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
| 62 | TABLE ACCESS BY INDEX ROWID | ITEM_ZONE_PRICE | 8610 | 184K| 3 |
|* 63 | INDEX RANGE SCAN | PK_ITEM_ZONE_PRICE | 8 | | 2 |
|* 64 | INDEX UNIQUE SCAN | PK_PRICE_ZONE_GROUP_STORE | 61 | 732 | |
--------------------------------------------------------------------------------------------------