Re: Using hints with Oracle9i MERGE

Thanks again for your help. Actually, one of my peers solved the problem. By replacing this:

    dest_tab.date_col IN (

        TO_DATE('2002-08-12', 'YYYY-MM-DD'),
        TO_DATE('2002-08-13', 'YYYY-MM-DD')

with this:

        dest_tab.date_col >= TO_DATE('2002-08-12', 'YYYY-MM-DD') AND
        dest_tab.date_col <= TO_DATE('2002-08-13', 'YYYY-MM-DD')

I see both hash joins and partition elimination (vs hash joins with a full-table scan or nested loop joins with partition elimination). So, this is what seems to work:

    MERGE /*+ NO_MERGE(src_query) USE_HASH(dest_tab) */     INTO dest_tab
    USING (
        SELECT {stuff}
    ) src_query
    ON ( dest_tab.col1 = src_query.col1 AND

        dest_tab.col1 = src_query.col1 AND
        {more stuff} AND
        dest_tab.date_col >= TO_DATE('2002-08-12', 'YYYY-MM-DD') AND
        dest_tab.date_col <= TO_DATE('2002-08-13', 'YYYY-MM-DD')
    WHEN MATCHED THEN
        UPDATE
        SET {more stuff}
    WHEN NOT MATCHED THEN
        INSERT {more stuff}

Thanks again & regards,

Bob Colombo

dyou98_at_aol.com (D.Y.) wrote in message news:<f369a0eb.0302141254.7b6fe7da_at_posting.google.com>...
> MERGE does an outer join and, in your SQL statement, src_query is the driving
> table so your hint won't work. Try /*+ use_hash(dest_tab) */.
>
> Are you missing statistics? Oracle would have normally used hash join on large
> tables.
>
>
> "Bob Colombo" <bob.colombo_at_fmr.com> wrote in message news:<GaV2a.44$Mn.30_at_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 Mon Feb 24 2003 - 15:18:50 CST