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 🙂
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
Yes, I heard that too, but as far as I know it is non-sense, even in Oracle 6 or 7. A common myth
select count(rowid) from some.table;
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.
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.
right, the difference is for index scan or table scan. An index cannot be used to count(*) if there is no NOT NULL constraint