Skip to Main Content
  • Questions
  • COUNT(*) Vs COUNT(1) on tables with CLOBS and etc

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Satish.

Asked: May 04, 2005 - 2:38 pm UTC

Last updated: May 05, 2005 - 1:33 pm UTC

Version: 9.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I know you have stated so many many many times that COUNT(*) and COUNT(1) are the same.

I was told that when we use that with Tables that have special data types like CLOBS and etc COUNT(1) will be significantly faster than COUNT(*) as it queries the other table space where the CLOB is stored.

Are the following statements True?

Count(*) is NOT the same as count(1).
Count(*) uses dba_tab_columns to identify the execution plan.
If the table contains new types like lobs, long, xml, object etc.it is significantly slower.


Thanks,
SATISH

and Tom said...

<quote>
count(*) is not the same as count(1)
</quote>

count(*) is the correct way to write it and count(1) is OPTIMIZED TO BE count(*) internally -- since

a) count the rows where 1 is not null is less efficient than
b) count the rows

<quote>
count(*) uses dba_tab_columns....
</quote>
no, nothing uses that. queries are parsed -- this is not "*" like select *, count(*) says COUNT ROWS, period. count(1) says count rows where 1 is not null.

count(*) is the ONLY thing to consider using.


if count(1) were infinitely more efficient, or even a tiny bit -- someone would be able to give you an example.

But they won't because none exist.


select count(*) from t; should use a primary key index (or an index on any column that is not nullable) -- it won't even touch the table in most cases.


count(*) -- it is it.
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1156151916789 <code>


I don't care how many or what type of columns are in this table, count(*) is it.




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here