Re: rownum

ah but

Select sales_amt from sales where rownum <= 10 order by sales_amt;

will not order then filter, it will filter then order. Thus your result may not be the top 10 sales. Have a look at the explainplans for the two queries. On the statement above you'll see sort(order by) being performed after count(stopkey). On the inline view version you'll see that the plan is different according to the plan a view gets created ordered by the stopkey and the filter is done against the view. This was why I was saying that the above statement could return incorrect results.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Biss Is Ignorance" <bliss_is_ignorance_at_hotmail.com> wrote in message
news:3a825a3a.0_at_news.tm.net.my...


> hi,

>

> by 'performance' i mean speed of query, the resources it utilises, number

 of


> hits on server / DB etc.... this query will fetch all the data from the

> table, then order the whole lot, then filter it and lastly display the

> result... waste of resources if u ask me, specially for a large DB.  and

 if


> this can be devised using a sub-query that will retrieve the same

 recordset


> faster utilising less resources, wouldn't that be better?

>

> if 'performance' meant 'doesn't return the correct results' .... well that

> would not be a 'performance' issue would it???

>

>

> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message

> news:95r9qd$ou7$1_at_soap.pipex.net...

> > "Biss Is Ignorance" <bliss_is_ignorance_at_hotmail.com> wrote in message

> > news:3a7fd18c.0_at_news2.tm.net.my...

> > > hi,

> > >

> > > i think i don't understand what you want.... but from what i can

 figure
 out


> > > ur problem seems for example

> > > Select sales_amt from sales;

> > > now u want to display the top 10 records

> > >

> > > if that's the case u could try

> > > Select sales_amt from sales where rownum <= 10 order by sales_amt;

> > >

> > > however this is not the most effective method in terms of performance

> >

> > Well I guess it depends on what you mean by 'performance'. If you mean

 what


> > I generally regard as 'performance' viz working faster then I don't see

 your


> > argument. Why is this statement slower than an ordered inline view? If

> > however by 'performance' you mean 'doesn't return the correct results'

 then


> > you've hit the nail on the head.

> >

> >

> > --

> > Niall Litchfield

> > Oracle DBA

> > Audit Commission UK

> >

> >

>

>