The difference between the count (1) and COUNT (*) of Oracle Foundation

The Count (*) or COUNT (1) or count ([column]) in the database is probably the most commonly used aggregate function. A lot of people actually have a clear distinction between the three. This article will describe the roles, relationships, and principles behind the three.

I've seen some of the so-called optimizations on the web that it is recommended not to use COUNT (*) instead of count (1), which can improve performance, given the reason that count (*) brings a full table scan. And actually how to write count is no different.

Count (1) and COUNT (*) actually mean that the evaluation of the expression XXX in count (XXX) is NULL, if NULL is not counted, and not null. For example, we look at code 1 and specify NULL in count (the optimizer does not allow explicit null, so it needs to be assigned to a variable to specify).

Select count (null) from TableA t;

The value in table TableA that does not exist in a whole row is empty. So the results are not counted at 0.

Select COUNT (*) from TableA t;

Select COUNT (1) from TableA t;

The result will be the same regardless of count (*) or COUNT (1) or count (XXX), because these values are not NULL.

Therefore, themaximum difference between count (*) and COUNT (1) is:

1. Count (*) will result in full table scan (low efficiency)

2. Count (*) does not filter out a row with a full line value of NULL

3, COUNT (1) will filter out a whole line of behavior null

4, COUNT (1) and count (primary key) both scan the primary key index to get the data, or count (ROWID) which is also only scan index (high efficiency),

This question is asked when Oracle easy to walk table query, when Oracle easy to go index query.

In practical applications, you have to look at the actual situation, maybe this table does not have a unique key index it? In the case of the CBO (a SQL optimizer provided by Oracle), is the statistical information inaccurate?

Therefore, if the count (*) on a table is used for a long period of comparison, it can greatly improve performance by considering establishing a single-column index in the shortest column.

The difference between the count (1) and COUNT (*) of Oracle Foundation