rownum=1 slow + tell if a column has at least a non null value

Hello,

I run these queries with toad on my_table (25 millions rows) :

select count(*) from my_table where rownum = 1;
=> 8s

select count(*) from my_table where rownum < 3000000;
=> 30s

select count(*) from my_table where rownum < 30000000;
=> 4 min

1. Why does it take 8 sec for the first to complete ? I thought it would be instantaneous. (select * from my_table where key='value' is instantaneous)
2. By the way, what is the fastest method to tell if a column has at least one non null value ?

I was thinking about : select 'x' from any_table where any_column is not null and rownum = 1;

But with the previous results (8 sec), it may not be. Do i have to make a function which opens a cursor and loop until i find a non null value ?

Thanks a lot.

Jean-Michel Received on Wed Nov 13 2002 - 12:02:26 CST