Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: rownum

Re: rownum

From: Biss Is Ignorance <bliss_is_ignorance_at_hotmail.com>
Date: Tue, 13 Feb 2001 01:43:13 +0800
Message-ID: <3a8820f6.0@news2.tm.net.my>

true... very true... gimme some time i'll give u the soln... BTW how soon do u need it???

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news: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
> 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
> > >
> > >
> >
> >
>
>
Received on Mon Feb 12 2001 - 11:43:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US