Re: Using hints with Oracle9i MERGE

From: Bob Colombo <bob.colombo_at_fmr.com>
Date: 19 Feb 2003 18:09:26 -0800
Message-ID: <396c450.0302191809.728cb9f3@posting.google.com>

>
> 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).

RDBMS version: 9.2.0.2 on Solaris
None of the columns being joined or filtered upon permit NULL values.
The destination / aggregate table (r_txn_ret_nff_dy) is partitioned by range on date (date_sid) column. Ideally, only 1 date (therefore 1 partition) would be affected by this MERGE, so I may resort to adding a date filter on the destination / aggregate table (r_txn_ret_nff_dy.date_sid) to achieve partition elimination.

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.

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 Received on Wed Feb 19 2003 - 20:09:26 CST