Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem - help appreciated.
"Charlie Edwards" <charlie3101_at_hotmail.com> 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
The inclusion of rownum in the select clause forces oracle to execute the inner query first. Otherwise it tends to rewrite the query.
Tom Kyte gives a good explanation of this in his book .. and maybe
he might have explained this in his website also asktom.oracle.com
..
Anurag
Received on Wed Mar 31 2004 - 08:22:52 CST
![]() |
![]() |