Use of Oracle 11g hints to get desired execution Plan

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

You need more than a hint in this case. When you have a where clause such as column = ( query ),
the query is run once for every row. You would be better off moving the subquery to part of the inner
join rather than having it where you have it. Also you should use the 9i and above inner join rather than
a comma delimited list of tables. using inner join makes it clear how each table is joined to the previous
table. It makes it easier to see when you have a cartesean product. You should also alias the tables
so that it is clear which column comes from which table.

Finally, check all of the joins because I could have missed something.

Code:

---

SELECT s_acctbal,  s_name,    n_name,  p_partkey,
      p_mfgr,      s_address, s_phone,  s_comment
  FROM part
 INNER JOIN partsupp
    ON p_partkey    = ps_partkey
 INNER JOIN supplier
    ON ps_suppkey    = s_suppkey
 INNER JOIN nation
    ON s_nationkey  = n_nationkey
 INNER JOIN region
    ON n_regionkey  = r_regionkey
 INNER JOIN
    ( SELECT s_suppkey, ps_partkey, MIN(ps_supplycost) min_ps_supplycost
        FROM partsupp
      INNER JOIN supplier
          ON ps_partkey  = p_partkey
      INNER JOIN nation
          ON s_nationkey = n_nationkey
      INNER JOIN region
          ON n_regionkey = r_regionkey
      WHERE r_name      = 'ASIA' ) min_partsupp
    ON ps_suppkey = s_suppkey
  AND p_partkey  = min_partsupp.ps_partkey
WHERE p_size      = 50
  AND p_type LIKE '%COPPER'
  AND r_name        = 'ASIA'
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey;

---