what is faster, select count(*) or select count(pk) ?

Oh no, not another post about COUNT(*) and COUNT(1) :mrgreen:

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 :)

6 thoughts on “what is faster, select count(*) or select count(pk) ?”

  1. In your test case set autotrace on explain, or

    SQL> set autotrace on explain

    and then re-run your two tests. The execution plans will explain the difference.

  2. 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.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>