-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|