Re: Performance problem - help appreciated.

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<k8mac.22140$u_2.11833_at_nwrddc01.gnilink.net>...
> "Charlie Edwards" <charlie3101_at_hotmail.com> wrote in message
> news:217ac5a8.0403300816.6f98df37_at_posting.google.com...
<snip>

> Try the following:
>
> * Make sure all tables and their indexes used in this query are analyzed
> * A 10053 event might be of help in finding the reson for the optimizer plan (If you know
> how to set it and use it).
> * Try the following:
>
> 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 */ rownum r1, 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 /
>
> ** Note: the rownum in the select clause.
>
>
> Anurag

I tried the rownum trick and it appears to work.

But please tell me why!!!!

CE Received on Wed Mar 31 2004 - 03:59:46 CST