Using hints with Oracle9i MERGE

From: Bob Colombo <bob.colombo_at_fmr.com>
Date: Thu, 13 Feb 2003 17:46:00 -0500
Message-ID: <GaV2a.44$Mn.30@news-srv1.fmr.com>

Hello,

I'm trying to tune a MERGE statement in a data mart to stabilize the query plan and force hash join between the input select and the destination table. I've tried adding the HASH_JOINS hint, but it doesn't seem to have any effect. Here's what I've tried:

MERGE /*+ USE_HASH(src_query) */
INTO dest_tab

    USING (

        SELECT
            fact_table.date_key date_key,
            fact_table.key1 key1,
            SUM(fact_table.cnt) cnt
        FROM
            fact_table,
            dim1
        WHERE
            fact_table.key1 = dim1.key1
            AND dim1.ind = 'Y'
        GROUP BY
            fact_table.date_key,
            fact_table.key1,

    ) src_query
    ON (

        dest_tab.date_key = src_query.date_key
        AND dest_tab.key1 = src_query.key1

    )
    WHEN MATCHED THEN

        UPDATE
        SET
            dest_tab.cnt = dest_tab.cnt + src_query.cnt
    WHEN NOT MATCHED THEN
        INSERT (
            dest_tab.date_key,
            dest_tab.key1,
            dest_tab.cnt


        )

        VALUES (
            src_query.date_key,
            src_query.key1,
            src_query.cnt


        )

Any help / insight would be appreciated.

Thank you,

Bob Colombo Received on Thu Feb 13 2003 - 16:46:00 CST