This article is based on Oracle 11g

Count (*) = count(*) = count(*) = count(*) = count(*)

A table is not indexed

1. First, we execute the following script from SQLPlus:
DROP TABLE TEST_TABLE; CREATE TABLE TEST_TABLE AS (SELECT * FROM DBA_OBJECTS); SELECT * FROM DBA_OBJECTS;Copy the code

We get a TEST_TABLE for our own testing, derived from the DBA_OBJECTS table. Note that only sys users can use this table, please log in using sys.

2. We execute the following script twice to compare the two corresponding indicators:

SELECT COUNT(*) FROM TEST_TABLE;

For the first time:

CPU overhead: 281, recursive calls: 28, consistent reads: 1097

The second:

CPU overhead: 281, recursive calls: 0, consistent reads: 1031

Here you can see that the overhead is the same, but the recursive calls are zero, and the consistent reads are much less. Why? After oracle completes the request, the data is cached in the cache, which corresponds to the Oracle Share pool

3. We execute the following script twice to compare the two corresponding indicators:

SELECT COUNT(OBJECT_ID) FROM TEST_TABLE;

For the first time:

CPU overhead: 281, recursive calls: 27, consistent reads: 1097

The second:

CPU overhead: 281, recursive calls: 0, consistent reads: 1031

You can see that COUNT(*) is just as efficient as COUNT(OBJECT_ID). OBJECT_ID = OBJECT_ID; OBJECT_ID = OBJECT_ID; OBJECT_ID = OBJECT_ID; OBJECT_ID = OBJECT_ID;

SELECT COUNT(STATUS) FROM TEST_TABLE;

For the first time:

CPU overhead: 281, recursive calls: 4, consistent reads: 1095

The second:

CPU overhead: 281, recursive calls: 0, consistent reads: 1031

SUBOBJECT_NAME = 1031; SUBOBJECT_NAME = 1031; SUBOBJECT_NAME = 1031 SELECT COUNT(SUBOBJECT_NAME) FROM TEST_TABLE;

For the first time:

CPU overhead: 281, recursive calls: 4, consistent reads: 1095

The second:

CPU overhead: 281, recursive calls: 0, consistent reads: 1031

Isn’t it! Again, the consistency read is 1031, there is no difference in efficiency. However, did you notice that I made a small circle in the last two pictures? How did the total number become 357? It shouldn’t be 72056, okay? SELECT COUNT(*) FROM TEST_TABLE; SELECT COUNT(*) FROM TEST_TABLE; SELECT COUNT(*) FROM TEST_TABLE; If you take SELECT COUNT(column) FROM XXX to fulfill a statistical requirement, then GG.

When a table is indexed

The above experiment proves that COUNT(*) and COUNT(column) are indistinctive without an index. CREATE INDEX OBJECT_ID_INDEX ON TEST_TABLE(OBJECT_ID);

Then we will execute the following script:

SELECT COUNT(*) FROM TEST_TABLE;

The second time:

CPU overhead: 281, recursive calls: 0, consistent reads: 1031

Select COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*), COUNT(*)

So what happens to COUNT(OBJECT_ID)? SELECT COUNT(OBJECT_ID) FROM TEST_TABLE;

CPU overhead: 45, recursive calls: 0, consistent reads: 168

The second time, we found that the CPU cost and consistency read are reduced a lot, and also go INDEX FAST FULL SCAN, performance is really good. Of course, this is the COUNT for the indexed column, but what about the non-indexed column? Execute the following script:

SELECT COUNT(SUBOBJECT_NAME) FROM TEST_TABLE;

CPU overhead: 281, recursive calls: 0, consistent reads: 1031

Find that the COUNT(non-indexed column) has not changed much. There is a small problem. Our OBJECT_ID is allowed to be null. What if we assume that it is not null? Execute the following script:

ALTER TABLE TEST_TABLE MODIFY OBJECT_ID NOT NULL;

Execute COUNT(*) and COUNT(column) as follows:

SELECT COUNT(*) FROM TEST_TABLE;

CPU overhead: 45, recursive calls: 0, consistent reads: 168
COUNT(*)

SELECT COUNT(OBJECT_ID) FROM TEST_TABLE;

CPU overhead: 45, recursive calls: 0, consistent reads: 168
The COUNT (column)

3. Summary of features

conditions operation The results of
Not indexed COUNT(*) A full table scan
Not indexed The COUNT (column) A full table scan
Create index (index column can be null) COUNT(*) A full table scan
Create index (index column can be null) COUNT(indexed column) Index quick scan
Create index (index column can be null) COUNT(non-indexed column) A full table scan
Create index (index column cannot be empty) COUNT(*) Index quick scan
Create index (index column cannot be empty) COUNT(indexed column) Index quick scan

In summary, COUNT(*) is indexed only if the index is not empty. Note that COUNT(*) and COUNT(column) are not equivalent, and analyze business scenarios before using them.

If the table has an index and the index is not empty, COUNT(*) and COUNT(1) have the same effect as SELECT MAX(ROWNUM) FROM XXX.

Select * from oper_time where oper_time is the index column and id is not the index column.

select count(x.id) as countnum
  from iodso.A x
 where x.oper_time between 1 and 2;
Copy the code

SQL > select count(*) from ROWID, select count(*) from ROWID, select ROWID from ROWID So we want to avoid these mistakes.