| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Using hints with Oracle9i MERGE
A MERGE operates by doing an outer join
from the new INTO the old data, so if you
want to control the join method, you have
to specify the old data table in the hint.
In your original post you had:
MERGE /*+ USE_HASH(src_query) */
INTO dest_tab
This should have been:
MERGE /*+ USE_HASH(dest_tab) */
INTO dest_tab
A problem you may find here, though, is
that Oracle is not always very good at
doing partition elimination when hash joins
are involved. But in your case, where the
partitions are identified by literal values
in the where clause, I think you should
be okay.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______March 19th ____USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Bob Colombo wrote in message <396c450.0302191809.728cb9f3_at_posting.google.com>...Received on Thu Feb 20 2003 - 03:33:07 CST
>=====================================================================
====
>Here's the original SQL (minus any hints). Please note: Commented
>filter on dest_tab.date_sid which affects full table scan vs
partition
>elimination:
>
>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
> FROM i_f_txn fact_table,
> d_fund,
> d_org
> WHERE fact_table.fund_sid = d_fund.fund_sid
> AND fact_table.org_sid = d_org.org_sid
> AND d_fund.ind1 = 'Y'
> AND d_fund.ind2 = 'N'
> AND d_org.org IN ('ABC', 'XYZ')
> GROUP BY fact_table.date_sid,
> 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
> ) src_query
> ON ( dest_tab.date_sid = src_query.date_sid
>
>/*
> 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_sid
> AND dest_tab.core_id = src_query.core_id
> AND dest_tab.source_system_id = src_query.source_system_id
> )
> WHEN MATCHED THEN
> UPDATE
> SET
> dest_tab.cnt = dest_tab.cnt + src_query.cnt
> WHEN NOT MATCHED THEN
> INSERT (
> 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
> )
> VALUES (
> 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
 |
 |