I am using oracle 11g hints to get desired plan for one of the query in TPCH benchmark. Following is the query and the desired plan.
Query:
explain plan for select
s_acctbal, s_name, n_name, p_partkey,
p_mfgr, s_address, s_phone, s_comment
from
part, supplier, partsupp, nation, region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 50
and p_type like '%COPPER'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
)
order by
s_acctbal desc, n_name, s_name, p_partkey;

DESIRED PLAN:

Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19866 | 4074K| 199K (1)| 00:39:55 |
| 1 | SORT ORDER BY | | 19866 | 4074K| 199K (1)| 00:39:55 |
|* 2 | VIEW | VW_WIF_1 | 19866 | 4074K| 199K (1)| 00:39:55 |
| 3 | WINDOW SORT | | 19866 | 5645K| 199K (1)| 00:39:55 |
|* 4 | HASH JOIN | | 19866 | 5645K| 199K (1)| 00:39:55 |
|* 5 | TABLE ACCESS FULL | REGION | 1 | 29 | 3 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 6 | HASH JOIN | | 99328 | 24M| 199K (1)| 00:39:55 |
| 7 | TABLE ACCESS FULL | NATION | 25 | 800 | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 99328 | 21M| 199K (1)| 00:39:55 |
| 9 | NESTED LOOPS | | | | | |
| 10 | NESTED LOOPS | | 99328 | 8342K| 192K (1)| 00:38:27 |
|* 11 | TABLE ACCESS FULL | PART | 24615 | 1370K| 118K (1)| 00:23:43 |
|* 12 | INDEX RANGE SCAN | PK_PARTSUPP | 4 | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| PARTSUPP | 4 | 116 | 3 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SUPPLIER | 1280K| 175M| 7314 (1)| 00:01:28 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("VW_COL_9" IS NOT NULL)
4 - access("N_REGIONKEY"="R_REGIONKEY")
5 - filter("R_NAME"='ASIA')
6 - access("S_NATIONKEY"="N_NATIONKEY")
8 - access("S_SUPPKEY"="PS_SUPPKEY")
11 - filter("P_SIZE"=50 AND "P_TYPE" LIKE '%COPPER' AND "P_TYPE" IS NOT NULL)
12 - access("P_PARTKEY"="PS_PARTKEY")


I tried using ORDERED hint in outer select but no use. Kindly help me in getting the desired plan. I am working on Query ERT prediction model. This need is part of building my model. In fact, I need good documentation on hints which can be used to get desired plan for any complex query (involving 10-12 joins) like reporting queries etc.

I really appreciate any help in this regard.

Warm Regards

Rekha Singhal