on Count

Hearing a lot about count(1) Vs count(*) misconception – so thought of writing this blog post to break all those misleading ideas.

 

Technically count(1) and count(*) are the same, nothing different.

 

Count(*) counts records in a table. (it does not  have to get the full record, it just need to know there is a record and increments a count)

 

Count(1) counts not null occurrence of the constant 1 in a table (it does not have to get the full record, it just need to know there is a record and increments a count)

 

They are same, identical, not different.

 

Tkprof shows the same.

 

select count(*)

from

 big_table

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.04          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.28       2.63      15204      15213          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.28       2.68      15204      15213          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 91 

Number of plan statistics captured: 1

 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

         1          1          1  SORT AGGREGATE (cr=15213 pr=15204 pw=0 time=0 us starts=2635299)

   1000000    1000000    1000000   TABLE ACCESS FULL BIG_TABLE (cr=15213 pr=15204 pw=0 time=4220 us starts=878850 cost=4220 size=0 card=1000000)

 

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  Disk file operations I/O                        1        0.07          0.07

  direct path read                              182        0.10          2.32

  SQL*Net message from client                     2        0.00          0.00

********************************************************************************

 

SQL ID: bua52c07ys6bc Plan Hash: 599409829

 

select count(1)

from

 big_table

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.20       2.55      15204      15209          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.20       2.56      15204      15209          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 91 

Number of plan statistics captured: 1

 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

         1          1          1  SORT AGGREGATE (cr=15209 pr=15204 pw=0 time=0 us starts=2559338)

   1000000    1000000    1000000   TABLE ACCESS FULL BIG_TABLE (cr=15209 pr=15204 pw=0 time=4220 us starts=812760 cost=4220 size=0 card=1000000)

 

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  direct path read                              164        0.18          2.31

  SQL*Net message from client                     2        0.00          0.00

 

 

 

********************************************************************************

 

 

Also 10053 trace file shows that count(1) is silently translated into count(*)

 

rajesh@ORA11G> @tkfilename

D:\APP\VNAMEIT\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_10680.trc

rajesh@ORA11G> @10053

 

Session altered.

 

rajesh@ORA11G>

rajesh@ORA11G> explain plan for

  2  select count(1)

  3  from demo.big_table;

 

Explained.

 

rajesh@ORA11G> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

C:\Users\179818>

 

 

Looking into 10053 trace show this.

 

*******************************************

Peeked values of the binds in SQL statement

*******************************************

 

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(1)" FROM "DEMO"."BIG_TABLE" "BIG_TABLE"

kkoqbc: optimizing query block SEL$1 (#0)

 

This confirms that count (1) silently turned into count (*).

 

Count(any_thing) is meaningless.

 

So the right way to count the number of rows in an object (table/materialized view/(sub)partition) is count(*), period .