oracle count0 1,differenct between count(0), count(1) and count(*)...???

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