Re: Using hints with Oracle9i MERGE

Bob Colombo wrote:

> 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

And the Oracle version is?
And the explain plan is?

Hints are just that hints ... not directives. Oracle may well be looking at your hint and deciding it doesn't like it.

Daniel Morgan Received on Thu Feb 13 2003 - 22:37:30 CST