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

look this

the query select all rows and show to you the record whanted more rows in the table more slow will it.
The better and faster way is

   set pages 25
   set pause on
   set pause ">>> "

   and now do your query

  if you do not need more do ctrl-c to stop your query

Gilbert Bernard
Oracle DBA Paris france

<strepxe_at_yahoo.co.uk> a écrit dans le message de news: 1121242844.899220.274510_at_g43g2000cwa.googlegroups.com...
> 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 - 05:24:18 CDT