Re: Performance problem - help appreciated.

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Wed, 31 Mar 2004 09:43:48 +0200
Message-ID: <7etk60917a3o7avp5idmohbbaleman8b60@4ax.com>

On 30 Mar 2004 08:16:19 -0800, charlie3101_at_hotmail.com (Charlie Edwards) wrote:

(...)
>Woo-hoo - Problem solved, I thought - Ill just do my grouping at a
>higher level.
>
>SQL> select system_code,
> 2 cpc_id,
> 3 extraction_code,
> 4 sum(amount) tot_amount,
> 5 sum(bonus) tot_bonus,
> 6 max(contact_date) last_contact_date
> 7 from (select /*+ first_rows */ cpcm.system_code,
> 8 cpcm.cpc_id,
> 9 cpcm.extraction_code,
> 10 cpcd.amount,
> 11 cpcd.bonus,
> 12 contact_date
> 13 from cpc_details cpcd,
> 14 cpc_master cpcm
> 15 where cpcm.system_code = 'B'
> 16 and cpcm.cpc_id = cpcd.cpc_id(+)
> 17 and cpcm.system_code = cpcd.system_code(+)
> 18 and cpcm.extraction_code = '17')
> 19 group by system_code,cpc_id,extraction_code
> 20 /
>
>SY CPC_ID EXTRACTION_CODE TOT_AMOUNT TOT_BONUS LAST_CONTAC
>-- --------------- --------------- ---------- --------- -----------
>B 9097207 17 1496.76 26.67 01-MAR-2004
>B 9191237 17 13514 51.95 01-MAR-2004
>
> real: 5758
>SQL>
>
>Huh?????
>Just as bad as before.
>
>So can anyone help?
>Why does it suddenly ignore my first_rows hint?
>Has anyone got any other suggestions on how I can solve my problem.
>
>I can post plans / tkprof output etc but it isn't very enlightening.
>
>TIA
>
>CE

Apparently Oracle has merged your inline view with the rest of the statement. To prevent this, give the view a name and use the NO_MERGE hint, like this:

select /*+ NO_MERGE(INLINE_VIEW) */ system_code,

         cpc_id,
         extraction_code,
         sum(amount) tot_amount,
         sum(bonus) tot_bonus,
         max(contact_date) last_contact_date
  from   (select /*+ first_rows */ cpcm.system_code,
                 cpcm.cpc_id,
                 cpcm.extraction_code,
                 cpcd.amount,
                 cpcd.bonus,
                 contact_date
          from   cpc_details cpcd,
                 cpc_master cpcm
          where  cpcm.system_code = 'B'
          and    cpcm.cpc_id = cpcd.cpc_id(+)
          and    cpcm.system_code = cpcd.system_code(+)
          and    cpcm.extraction_code = '17') INLINE_VIEW

  group by system_code,cpc_id,extraction_code

Jaap. Received on Wed Mar 31 2004 - 01:43:48 CST