This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Jun 30, 2007 1:01 AM by William Robertson Go to original post RSS
  • 15. Re: differenct between count(0), count(1) and count(*)...???
    511418 Newbie
    Currently Being Moderated
    No differences at all.

    But let me know why this question was posted....just out of curiosity..
  • 16. Re: differenct between count(0), count(1) and count(*)...???
    60660 Journeyer
    Currently Being Moderated
    Exactly my thoughts cd.
    Where's Billy with his trusty lead pipe, when you need him? ;-)

    C.
  • 17. Re: differenct between count(0), count(1) and count(*)...???
    577207 Newbie
    Currently Being Moderated
    I see no differences. Tired and Tested.

    Adith
  • 18. Re: differenct between count(0), count(1) and count(*)...???
    561093 Oracle ACE
    Currently Being Moderated
    Hi,

    A good discussion which has many myths attached to it.

    Well, count(*), count(1), count(0), count('FAST') are all the same performance wise. You can enable the autotrace and get the proof on screen.

    But the performance will differ under some situations. Like, I have a TEST table, when I use COUNT(*) you can notice the consistent gets, recursive calls are low when compared to COUNT(B).

    This is because when I issue COUNT(*), the CBO intelligently use the index on Primary key to count the rows whereas when I issue COUNT(B) the CBO has to count the rows using a Full table scan. ("B is a nullable column"). This brings the difference.


    SQL> desc test
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    A NUMBER
    B NUMBER

    SQL> alter table test add constraint test_pk primary key (a);

    Table altered.

    Elapsed: 00:00:00.25
    SQL> alter system flush shared_pool;

    System altered.

    Elapsed: 00:00:00.01
    SQL> set autotrace on
    SQL> select count(*) from test;

    COUNT(*)
    ----------
    3

    Elapsed: 00:00:00.06

    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------
    | Id | Operation | Name | Rows | Cost (%CPU)|
    ---------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 1 (0)|
    | 1 | SORT AGGREGATE | | 1 | |
    | 2 | INDEX FULL SCAN| TEST_PK | 3 | 1 (0)|
    ---------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    684 recursive calls
    0 db block gets
    112 consistent gets
    0 physical reads
    0 redo size
    411 bytes sent via SQL*Net to client
    384 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    9 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL> alter system flush shared_pool;

    System altered.

    Elapsed: 00:00:00.01
    SQL> select count(b) from test;

    COUNT(B)
    ----------
    3

    Elapsed: 00:00:00.07

    Execution Plan
    ----------------------------------------------------------

    ----------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    ----------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)|
    | 1 | SORT AGGREGATE | | 1 | 3 | |
    | 2 | TABLE ACCESS FULL| TEST | 3 | 9 | 2 (0)|
    ----------------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1101 recursive calls
    0 db block gets
    187 consistent gets
    0 physical reads
    0 redo size
    411 bytes sent via SQL*Net to client
    384 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    17 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL> set autotrace off


    Now let me make the column B as NOT NULL as see what happens

    SQL> set autotrace on
    SQL> alter table test modify (b number not null);

    Table altered.

    Elapsed: 00:00:00.15
    SQL> alter system flush shared_pool;

    System altered.

    Elapsed: 00:00:00.00
    SQL> select count(b) from test;

    COUNT(B)
    ----------
    3

    Elapsed: 00:00:00.11

    Execution Plan
    ----------------------------------------------------------

    ---------------------------------------------------------
    | Id | Operation | Name | Rows | Cost (%CPU)|
    ---------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 1 (0)|
    | 1 | SORT AGGREGATE | | 1 | |
    | 2 | INDEX FULL SCAN| TEST_PK | 3 | 1 (0)|
    ---------------------------------------------------------

    Note
    -----
    - 'PLAN_TABLE' is old version


    Statistics
    ----------------------------------------------------------
    1101 recursive calls
    0 db block gets
    191 consistent gets
    0 physical reads
    0 redo size
    411 bytes sent via SQL*Net to client
    384 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    17 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL>

    The CBO knowing that the column B is not null uses the PK index to count the number of rows.

    Regards
  • 19. Re: differenct between count(0), count(1) and count(*)...???
    ushitaki Newbie
    Currently Being Moderated
    That is good examples.

    On CBO, count(*) has performance's advantage from count(any_column),
    but there is no difference among count(*), count(constant) and count(rowid).

    I have forgotten how act on RBO. But I think also there is no difference between count(*) and count(constant). By the way, sometime count(primary_key) or count(indexed_column) has disadvantage from count(*).
  • 20. Re: differenct between count(0), count(1) and count(*)...???
    John Spencer Oracle ACE
    Currently Being Moderated
    Although changing b to not null in your example does allow the use of the PK index for COUNT(b), it is not absolutely necessary to have a not null constraint on the column to allow index usage. An index on the column, even if there are NULL values in the column is sufficient to allow index access. Since COUNT(expression) counts the non-null occurences of expression in the table, and Oracle does not index entirely NULL index keys, COUNT(indexed_column) will almost always use an index.
    SQL> CREATE TABLE t AS
      2  SELECT rownum id, TO_CHAR(TO_DATE(rownum, 'J'),'Jsp')
      3  FROM all_objects
      4  where rownum < 501;
     
    Table created.

    SQL> desc t;
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    ID                                                 NUMBER
    DESCR                                              VARCHAR2(78)
     
    SQL> CREATE INDEX t_desc on t(descr);

    Index created.

    SQL> analyze table t compute statistics for table for all indexes

    Table analyzed

    SQL> select count(*) from t;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1842905362

    -------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
    -------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| T    |   500 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------

    SQL> select count(descr) from t;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2760923846

    --------------------------------------------------------------------------------
    | Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |        |     1 |    20 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |        |     1 |    20 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_DESC |   500 | 10000 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    John
  • 21. Re: differenct between count(0), count(1) and count(*)...???
    561093 Oracle ACE
    Currently Being Moderated
    Hi,

    Actually, my post might mislead readers that its ALWAYS true if you have a not null column then your PK index would be used instead of Full table scan. It is not necessarily true.

    Thanks Spencer for continuing the case and completing it......
  • 22. Re: differenct between count(0), count(1) and count(*)...???
    William Robertson Oracle ACE
    Currently Being Moderated
    > Count(*) will try to access all DB blocks of the retrieved data before returning the Count.
    >
    Count(<Literal>) will not do so. Hence it is faster than count(*).

    Regards
    K.Rajkumar

    What is strange is that such an obvious myth that is so easy to prove wrong (explain plan, autotrace, tkprof etc, as posted above) should have stayed around for so long. I remember being told that COUNT(primary key column) was faster way back in Oracle 6 days, and I actually went around believing it for a couple of weeks until I happened to try it on a large table and noticed it made no difference.

    The real difference is that using COUNT(1) makes you look like an idiot.
1 2 Previous Next