Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using hints with Oracle9i MERGE
>
> I'd like to see the explain plan as well, but the version is 9i as per the
> subject line.
Thanks for the offer to peak at this and sorry for taking so long to respond. Between a holiday and a blizzard, these last few days have been distracting :)
Here's some more info, as well as the "less sanitized" SQL that I'm running. First, some more details which I should have provided in my first post (sorry).
I seem to be able to get query plans which use a HASH JOIN (OUTER) only when a **full table scan** is performed on the destination / aggregate table (r_txn_ret_nff_dy). If I add a date range filter on the destination / aggregate table (r_txn_ret_nff_dy) I see partition elimination but it resorts to NESTED LOOP (OUTER) joins, which would be painfully slow.
MERGE
INTO r_txn_ret_nff_dy dest_tab
USING (
SELECT fact_table.date_sid date_sid,
fact_table.fund_sid fund_sid, fact_table.location_sid location_sid, fact_table.prog_stat_sid prog_stat_sid, fact_table.org_sid org_sid, fact_table.product_sid product_sid, fact_table.registration_sid registration_sid, fact_table.sched_sid sched_sid, fact_table.sec_class_sid sec_class_sid, fact_table.txn_type_sid txn_type_sid, fact_table.core_id core_id, fact_table.source_system_id source_system_id,SUM(fact_table.cnt) cnt
AND d_fund.ind1 = 'Y' AND d_fund.ind2 = 'N' AND d_org.org IN ('ABC', 'XYZ')
fact_table.fund_sid, fact_table.location_sid, fact_table.prog_stat_sid, fact_table.org_sid, fact_table.product_sid, fact_table.registration_sid, fact_table.sched_sid, fact_table.sec_class_sid, fact_table.txn_type_sid, fact_table.core_id, fact_table.source_system_id
/*
This next filter on the destination (aggregate) table is intended to force partition elimination to only those 1-or-2 partition(s) affected by the range of dates being input. */
--> AND dest_tab.date_sid in ( --> to_date('2002-08-12', 'YYYY-MM-DD'), --> to_date('2002-08-13', 'YYYY-MM-DD') --> ) AND dest_tab.fund_sid = src_query.fund_sid AND dest_tab.location_sid = src_query.location_sid AND dest_tab.prog_stat_sid = src_query.prog_stat_sid AND dest_tab.org_sid = src_query.org_sid AND dest_tab.product_sid = src_query.product_sid AND dest_tab.registration_sid = src_query.registration_sid AND dest_tab.sched_sid = src_query.sched_sid AND dest_tab.sec_class_sid = src_query.sec_class_sid AND dest_tab.txn_type_sid = src_query.txn_type_sidAND dest_tab.core_id = src_query.core_id AND dest_tab.source_system_id = src_query.source_system_id )
dest_tab.date_sid, dest_tab.fund_sid, dest_tab.location_sid, dest_tab.prog_stat_sid, dest_tab.org_sid, dest_tab.product_sid, dest_tab.registration_sid, dest_tab.sched_sid, dest_tab.sec_class_sid, dest_tab.txn_type_sid, dest_tab.core_id, dest_tab.source_system_id, dest_tab.cnt
src_query.date_sid, src_query.fund_sid, src_query.location_sid, src_query.prog_stat_sid, src_query.org_sid, src_query.product_sid, src_query.registration_sid, src_query.sched_sid, src_query.sec_class_sid, src_query.txn_type_sid, src_query.core_id, src_query.source_system_id, src_query.cnt
And, here are the 2 query plans (top would be from querying without the date filter on dest_tab.date_sid, bottom with filter on dest_tab.date_sid:
Without date filter (performs HASH JOIN but no partition elimination):
MERGE STATEMENT
MERGE -- R_TXN_RET_NFF_DY
VIEW HASH JOIN (OUTER) VIEW SORT (GROUP BY) HASH JOIN HASH JOIN TABLE ACCESS (FULL) -- D_ORG TABLE ACCESS (FULL) -- I_F_TXN TABLE ACCESS (FULL) -- D_FUND PARTITION RANGE (ALL) TABLE ACCESS (FULL) -- R_TXN_RET_NFF_DY -------------------------------------------------------------------------
With date filter (uses partition elimination but with NESTED LOOPS
JOIN):
MERGE STATEMENT
MERGE -- R_TXN_RET_NFF_DY
VIEW NESTED LOOPS (OUTER) VIEW SORT (GROUP BY) HASH JOIN HASH JOIN TABLE ACCESS (FULL) -- D_ORG TABLE ACCESS (FULL) -- I_F_TXN TABLE ACCESS (FULL) -- D_FUND PARTITION RANGE (INLIST) VIEW TABLE ACCESS (FULL) -- R_TXN_RET_NFF_DY -------------------------------------------------------------------------
Thanks again & regards,
Bob Colombo Received on Wed Feb 19 2003 - 20:09:26 CST