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

Re 2), how about:

select 'any_column has at least one non-null value' from dual where exists (select 'x' from my_table where any_column is not null)

I don't know if this is the fastest way, but the semantics of the query at least give Oracle the opportunity to locate any single row with a non-null value for that column by any means available, and then immediately return true for the EXISTS predicate.

Martin

Le JeanMimi wrote:

>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 - 14:03:27 CST