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

Your WHERE clause explicitly limits the number of rows over which aggregate function count() have to run. Thus,
> select count(*) from my_table where rownum = 1;
will always return 0 or 1, regardless actual number of rows in the table (0 if table is empty, 1 otherwise. count() over the whole table will have to scan the whole table, visiting every single block of it, so for really large tables (which are usually not cached in memory) Oracle will have to physically read nearly every table block to return you number of rows in it (some may already be cached). So, the more rows you count, the more time it will take.

As to why your first query ran for 8 seconds - did the table undergo any massive deletes in the past? If it did, this could leave a lot of unused blocks in the beginning which Oracle had to scan anyway because you asked it to find out if there is at least one row in the table and it had to visit all blocks from the first block of the table to the first non-empty block to figure it out. If this is the case, your table is target for reorganization.

Also note, that if you have query like this:

> select 'x' from any_table
> where any_column is not null and rownum = 1;

and any_column has an index on it, then the query can be satisfied by that index alone (NULLs are not stored in indexes, so if index has just one entry then the answer is 1, else it's 0), but Oracle isn't smart enough to figure it out without your help. You either need to ensure that statistics on the table and all of its indexes are current, or you have to hint Oracle to use the index for this query, otherwise it will do FTS and visit every block it has to, including empty/irrelevant ones. The following simple test will demonstrate what I said:

create table x ( n int, v varchar2(2000) );

declare
  i int;
begin
  for i in 1..50000 loop
   insert into x values(i, rpad('x',2000,'x'));   end loop;
  commit;
end;
/

select count(*) from x where rownum=1;
  will return pretty fast this time.

update x set n = null where n < 50000;
commit;

select count(*) from x where n is not null and rownum=1;  will work considerably slower this time - it will have to hit  the whole table since we artificially left only one row in the  last block of the table. First run will probably run even  slower than all subsequent runs due to delayed block cleanout  that is likely to happen after the update, so run this query two  or more times to get steady run time.

create index x_idx on x(n);

select count(*) from x where n is not null and rownum=1;  will still work slowly and won't use the index.

analyze table x compute statistics;

select count(*) from x where n is not null and rownum=1;  will now return fast again and it will do index full scan this time.

if you create unique index instead of ordinary index, and compute statistics afterwards, Oracle will do index fast full scan to satisfy your query.

If you won't compute statistics, and hint the query with /*+ INDEX(x) */ or /*+ INDEX_FFS(x) */ then the query will again run fast and use the index - you specifically told Oracle that it's ok to use the index to satisfy your query.

• Corrections and additions are welcome.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"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