push_pred, a powerful hint to speed up you inline view query 10X times

when we need to use it:

Instructs the optimizer to push a join predicate into the view.

condition: non-mergeable views like outer join

for 10g, another alternative hint you may try:

/*+ opt_param('_optimizer_cost_based_transformation','off') */

also note that you need to specify view name in 10g version.

[@more@]

without hint:

|* 28 | TABLE ACCESS FULL | ORDHEAD | 61850 | 1932K| | 401 | | |
|* 29 | HASH JOIN | | 15994 | 3717K| | 109K| | |
|* 30 | HASH JOIN | | 15994 | 3186K| | 107K| | |
| 31 | VIEW | | 190K| 12M| | 5296 | | |
| 32 | WINDOW BUFFER | | 190K| 11M| | 5296 | | |
| 33 | SORT GROUP BY | | 190K| 11M| 29M| 5296 | | |
|* 34 | HASH JOIN | | 190K| 11M| 5016K| 3328 | | |
|* 35 | TABLE ACCESS FULL | ITEM_SUPP_COUNTRY | 146K| 3293K| | 746 | | |
|* 36 | HASH JOIN | | 187K| 7332K| 2432K| 2355 | | |
|* 37 | TABLE ACCESS FULL| ITEM_MASTER | 75413 | 1546K| | 2076 | | |
| 38 | TABLE ACCESS FULL| PACKITEM_BREAKOUT | 187K| 3483K| | 152 | | |
|* 39 | HASH JOIN | | 823M| 105G| | 102K| | |
|* 40 | TABLE ACCESS FULL | ORDHEAD | 61850 | 1932K| | 401 | | |
|* 41 | HASH JOIN | | 823M| 80G| | 101K| | |
|* 42 | TABLE ACCESS FULL | WS_ROLLOUT | 4 | 104 | | 3 | | |
|* 43 | HASH JOIN | | 3594M| 264G| | 101K| | |
| 44 | VIEW | | 1028 | 29812 | | 6 | | |
| 45 | UNION-ALL | | | | | | | |
| 46 | TABLE ACCESS FULL| STORE | 889 | 10668 | | 4 | | |
| 47 | TABLE ACCESS FULL| WH | 139 | 1807 | | 2 | | |
|* 48 | HASH JOIN | | 1961M| 91G| | 101K| | |
|* 49 | TABLE ACCESS FULL | ORDLOC | 24202 | 803K| | 425 | | |
| 50 | PARTITION HASH ALL| | | | | | 1 | 16 |
| 51 | TABLE ACCESS FULL| ITEM_LOC | 78M| 1194M| | 101K| 1 | 16 |
| 52 | TABLE ACCESS FULL | ITEM_MASTER | 603K| 19M| | 2076 | | |
|* 53 | TABLE ACCESS FULL | CODE_DETAIL | 27 | 702 | | 7 | | |
| 54 | TABLE ACCESS BY INDEX ROWID | DEPS | 1 | 18 | | 1 | | |
|* 55 | INDEX UNIQUE SCAN | PK_DEPS | 1 | | | | | |
| 56 | TABLE ACCESS BY INDEX ROWID | CLASS | 1 | 18 | | 1 | | |
|* 57 | INDEX UNIQUE SCAN | PK_CLASS | 1 | | | | | |
| 58 | TABLE ACCESS BY INDEX ROWID | ORDLOC_EXP | 1 | 31 | | 3 | | |
|* 59 | INDEX RANGE SCAN | UK_ORDLOC_EXP | 1 | | | 2 | | |
| 60 | TABLE ACCESS BY INDEX ROWID | WSI_ORDHEAD_DEFAULT_DATES | 1 | 38 | | 1 | | |
|* 61 | INDEX UNIQUE SCAN | PK_WSI_ORDHEAD_DEFAULT_DATES | 1 | | | | | |
| 62 | VIEW | | 169K| 6613K| | 1799 | | |
| 63 | SORT GROUP BY | | 169K| 5786K| 15M| 1799 | | |
|* 64 | HASH JOIN | | 169K| 5786K| 4648K| 725 | |

after using hint:

|* 28 | HASH JOIN | | 12275 | 2517K| | 167K| | |
|* 29 | TABLE ACCESS FULL | ORDHEAD | 51812 | 1315K| | 242 | | |
|* 30 | HASH JOIN | | 12275 | 2205K| | 167K| | |
| 31 | NESTED LOOPS | | 12275 | 1846K| | 165K| | |
|* 32 | HASH JOIN | | 80838 | 10M| | 4168 | | |
|* 33 | HASH JOIN | | 8313 | 576K| | 263 | | |
|* 34 | HASH JOIN | | 234 | 10296 | | 5 | | |
|* 35 | TABLE ACCESS FULL | WS_ROLLOUT | 4 | 96 | | 1 | | |
| 36 | VIEW | | 1028 | 20560 | | 3 | | |
| 37 | UNION-ALL | | | | | | | |
| 38 | TABLE ACCESS FULL | STORE | 889 | 8001 | | 2 | | |
| 39 | TABLE ACCESS FULL | WH | 139 | 1390 | | 1 | | |
|* 40 | TABLE ACCESS FULL | ORDLOC | 23095 | 608K| | 257 | | |
| 41 | VIEW | | 190K| 12M| | 3823 | | |
| 42 | WINDOW BUFFER | | 190K| 9663K| | 3823 | | |
| 43 | SORT GROUP BY | | 190K| 9663K| 24M| 3823 | | |
|* 44 | HASH JOIN | | 190K| 9663K| 4456K| 2183 | | |
|* 45 | TABLE ACCESS FULL | ITEM_SUPP_COUNTRY | 147K| 2728K| | 476 | | |
|* 46 | HASH JOIN | | 187K| 6039K| 2128K| 1509 | | |
|* 47 | TABLE ACCESS FULL | ITEM_MASTER | 74968 | 1244K| | 1300 | | |
| 48 | TABLE ACCESS FULL | PACKITEM_BREAKOUT | 187K| 2928K| | 96 | | |
| 49 | PARTITION HASH ITERATOR | | | | | | KEY | KEY |
| 50 | TABLE ACCESS BY LOCAL INDEX ROWID| ITEM_LOC | 1 | 16 | | 2 | KEY | KEY |
|* 51 | INDEX UNIQUE SCAN | PK_ITEM_LOC | 1 | | | 1 | KEY | KEY |
| 52 | TABLE ACCESS FULL | ITEM_MASTER | 599K| 17M| | 1300 | | |
| 53 | TABLE ACCESS FULL | CHANNELS | 15 | 120 | | 1 | | |
|* 54 | TABLE ACCESS FULL | CODE_DETAIL | 15 | 345 | | 4 | | |
| 55 | TABLE ACCESS FULL | DEPS | 244 | 3904 | | 1 | | |
| 56 | TABLE ACCESS FULL | CLASS | 1948 | 29220 | | 1 | | |
| 57 | TABLE ACCESS BY INDEX ROWID | SUPS | 1 | 22 | | 1 | | |

suspect a bug issue: 6791891