Re: Using hints with Oracle9i MERGE

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 Fri Feb 14 2003 - 14:54:22 CST