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 🙂