Re: count(1) vs. count(*)

Alas, just like the phoenix, Oracle myths always seem to come back from the flames.

I think the nicest proof of the identity is an example I happened to run up this evening for a presentation related Oracle 9.

From memory:

create table t1 (

    id number primary key,
    v1 varchar2(32),
    pad varchar2(100)
);
insert into t1 select rownum, owner, rpad('x',100) from dba_objects
where rownum <= 10000;

create bitmap index t_bx on t1(v1);

analyze table t1 compute statistics;

select count(ID) from t1;

How many people would guess that Oracle
would do a fast full scan of the BITMAP index - which doesn't contain the ID column at all - because the number of entries in the table has to match the number of entries in the primary key which has to match the number of entries in any bitmap index on the table.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Daniel Morgan wrote in message <3CC5B6A7.CEE028B2_at_exesolutions.com>...


>Great. Another myth goes down in flames.

>

>And I've seen this one repeated in numerous places. Last time, I think, in

an


>OCP prep book for 9i.

>