what is faster, select count(*) or select count(pk) ?
Oh no, not another post about COUNT(*) and COUNT(1) 
Well, it is not exactly the case. I just had the issue this morning that count(*) was too slow.
SQL> create table t as select rownum id,
2 lpad('a',4000,'a') a,
3 lpad('b',4000,'b') b,
4 lpad('c',4000,'c') c,
5 lpad('d',4000,'d') d
6 from dual connect by level<10000;
Table created.
SQL> create index i on t(id);
Index created.
SQL> alter table t add primary key (id)
2 using index i disable novalidate;
Table altered.
SQL> exec dbms_stats.gather_table_stats(user,'T')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_index_stats(user,'I')
PL/SQL procedure successfully completed.
SQL> set timi on
SQL> select count(id) from t;
COUNT(ID)
----------
9999
Elapsed: 00:00:00.01
SQL> select count(*) from t;
COUNT(*)
----------
9999
Elapsed: 00:00:01.43
My count(*) just takes too long… Why that?
I have no index on a not null column. The primary key is disabled.
Of course the count(pk) does not count the rows with pk=null, but it is faster 
August 29th, 2008 at 19:57
I remember at one point (i.e. like Oracle 6 or Oracle7) that I was told to change COUNT(*) to count a constant like COUNT(1).
Mike
August 29th, 2008 at 23:24
Yes, I heard that too, but as far as I know it is non-sense, even in Oracle 6 or 7. A common myth
August 30th, 2008 at 02:38
select count(rowid) from some.table;
August 30th, 2008 at 06:10
In your test case set autotrace on explain, or
SQL> set autotrace on explainand then re-run your two tests. The execution plans will explain the difference.
August 30th, 2008 at 13:54
So, this is the difference between FULL TABLE SCAN and FAST FULL SCAN on index. And of course, the index is most likely cached.
On my humble laptop with XE, my first run has 63ms on count(*) and 125ms on count(id). my second run has 46ms on count(*) and 0ms on count(id).
YMMV, as usual.
August 30th, 2008 at 18:30
right, the difference is for index scan or table scan. An index cannot be used to count(*) if there is no NOT NULL constraint