Re: Performance problem - help appreciated.

If you posted the explain plan for these queries, we could decipher why it seems to work.

Personally, it's not clear to me why you would add rownum. I usually solve these type of problems with a hint. I'm curious as to what that does.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

charlie3101_at_hotmail.com (Charlie Edwards) wrote in message news:<217ac5a8.0403310159.6f58a4dc_at_posting.google.com>...
> "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 - 13:21:39 CST