Re: confused in using ROWNUM in SQL query

In our last gripping episode Ezr Vinh <d_a_p_at_my-deja.com> wrote:
> Although it sounds like you are all set, I might suggest another
> possibility. You could write a PL/SQL block to create a cursor on the
> select statement using the ORDER BY as desired, use the FIRST_ROWS
 hint,
> and exit the cursor loop after you've returned the first five rows
 (just
> use a loop counter). You might find that it's considerably faster.
>
> -Dave
>
> In article <L2RX5.49$1%2.3650_at_sjc-read.news.verio.net>,
> "kush" <kdhakal_at_eline.com> wrote:
> > hi all, I have following sql query in my application which runs
 against
> > oracle 8.1.5 database.
> > The whole idea of query is to get only top 5 records out of hundreds
 of
> > records produced in
> > the inner select statement.
> > select * from (select distinct A.column1, B.column2, C.column1
> > from tableA A, tableB B, tableC C
> > where A.column3 between x and y
> > and A.column1 = B.column1
> > and C.column1(+) = A.column1
> > order by A.column4 desc)
> > where rownum < 6
> > This query works just fine, only problem is its taking lots of time,
 this
> > might be because first
> > of all hundreds of records has to be selected in the inner select
 statement
> > before selecting 5 out
> > of it. Now, in order to increase the performance I tried to avoid
 nested
> > select and put
> > rownum < 6 in the first where clause, like below
> >
> > select distinct A.column1, B.column2, C.column1
> > from tableA A, tableB B, tableC C
> > where rownum < 6 and A.column3 between x and y
> > and A.column1 = B.column1
> > and C.column1(+) = A.column1
> > order by A.column4 desc
> > Its a lot faster then the first one, and its giving same results as
 in
 first
> > one(I tried it
> > in couple of cases). But i believe there is something wrong, for
 example:
> > since we are using three
> > tables, which table's rownum is it referring to in where clause? and
 also
> > since we are using
> > order by condition, rownum supposed to be used against the final
 selected
> > rows after order by
> > condition have been made. Now, my question is, is the oracle smart
 enough to
> > know what I
> > wanted? or is the second statement correct and same as first
 statement? if
> > not where it went wrong
> > and how can I correct it and how does rownum works?.
> > Many thanks in advance.
> >
> > kush
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

And you may find the PL/SQL more reliable. You are fortunate that your second query returns the same result set as the first; I have known such query rewrites to return vastly different datasets. Check the query plan for the first query and see if there is anything you can do to make it more efficient. The first query is a far better 'top-N' query than the second.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.