Re: ROWNUM. can anybody clarify this to me.

scthomp_at_ibm.net (Simon Thompson) wrote:

>It probably depends on the statement. A simple:
 

> select * from table where rownum <= 10;
 

>probably would not scan the whole table. On the other hand:
 

> select *
> from table
> where rownum <= 10
> order by SomeField;
 

>would scan the whole table. It has to sort it (assuming there is no index on
>SomeField) to know which are the first 10 records. And an index may change
>the behaviour.

Neither will do a full table scan. Both will get the 1st 10 rows from the database. The 2nd select will sort them but it'll be exactly the same 10 rows as the 1st select. This is why you can't use ROWNUM as a simple solution to the classic "top N items" problem.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Incoming fire has the right of way!