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

"Le JeanMimi" <scjm_at_noos.fr> wrote in message news:c7be5048.0211131002.37beecaa_at_posting.google.com...
> 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

1. Please always specify a version. count(*) without where will use either full table scan or index fast full scan. The rownum=1 is fully redundant, because rownum's are determined *after* the select. A full table scan will *ALWAYS* proceed until the High Water Mark of the table, *even* if the table is completely empty. So the 8s doesn't surprise me with 25000000 rows.

2
select 'x' from dual
where exists
(select 'x'
 from my_table
where column >0 (or > chr(0))
)
provided the column is indexed.
Non-indexed: full table scan

You might want to ask yourself whether you really need this type of test. In 13 years I never needed this kind of test.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address