Re: rownum
From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 12 Feb 2001 15:30:15 -0000
Message-ID: <968vig$86d$1_at_soap.pipex.net>
Date: Mon, 12 Feb 2001 15:30:15 -0000
Message-ID: <968vig$86d$1_at_soap.pipex.net>
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
-- 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...Received on Mon Feb 12 2001 - 16:30:15 CET
> 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
> >
> >
>
>