Confused in using ROWNUM in SQL query

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
[Quoted] 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 Received on Thu Dec 07 2000 - 19:56:13 CET