I don't need a solution at all - it was not my original question.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Biss Is Ignorance" <bliss_is_ignorance_at_hotmail.com> wrote in message
news:3a8820f6.0_at_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 Wed Feb 14 2001 - 04:14:16 CST
