query "select * from table_name where rownum = 1" very slow

hi,
there is a query in our app:

"select * from table_name where rownum = 1"

that is very quick when run against a table with a small number of rows ( < 100 ) but very slow when run against a table with a large number (tens of millions). we are using oracle 9i enterprise edition.

i have briefly read about rownum but am interested to know more about the mechanics involved; i get the impression from what i have seen so far that there may be some feature of this pseudo-column which is resulting in a full table scan. but i am not sure.

there has been a suggestion that indexes on table_name could help but my understanding of indexes in the content of oracle rdbms would suggest that this will not result in an improvemnt (wrt the rule about the query containing a leading subset of the indexed columns).

any help/assistance greatly appreciated.

g Received on Wed Jul 13 2005 - 03:20:44 CDT