Oracle in Count (1), COUNT (*) and COUNT (primary key) which is faster

After listening to nearly 20 speeches in the last two days, I felt a lot of harvest, and the deepest feeling was that I still had a long way to go. There are several points to note:

Listening to the old cat yesterday, it is a common problem that Oracle has a problem with count (*), COUNT (1), and count (primary key). This question seems very simple, everyone will have their own answers, to Baidu on the search will come out a lot of posts in terms of which faster. But the old cat said that they are three actually the same, I heard after also feel very surprised, because I remember someone told me that count (primary key) will be quick, and then think of their own simple, feel as if it is so that the matter is not in-depth to pursue. Then the old cat said the official saying that the three are actually equivalent. After coming back in the evening and checking the MoS, I found it. How the Oracle CBO chooses a Path for the SELECT COUNT (*) Command (document ID 124717.1). This document is about how Oracle evaluates the optimal path without the Where condition select count (*) and select COUNT (colum) statements in the CBO optimizer mode.

1. Create a test table and design a test scenario:

--Create a test table [email protected]>create table journal_entries   2   (id_je  number (8)  ,  3  date_je date not null,  4   Balanced number ,  5  constraint indx_ecr_id_je primary key (Id_je )   6  ); table created.--Creating an Index [Email protected]>create index indx_ecr_date_je_balanced on  journal_entries (date_je,balanced); index created. [Email protected]>create index indx_ecr_balanced_date_je on journal_entries ( Balanced,date_je); index created. [Email protected]>create index indx_ecr_balanced on journal_entries (balanced); ndex created.--inserting test data [Email protected]>insert into journal_entries values (1, sysdate,11); 1 row created. [Email protected]>insert into journal_entries values (2,sysdate,21); 1 row created. [Email protected]>insert into journal_entries values (3,sysdate,31);1 row  Created. [Email protected]>insert into journal_entries values (4,sysdate,41);1 row  Created. [Email protected]>insert into journal_entries values (5,sysdate,51);1 row  Created. [Email protected]>insert into journal_entries values (6,sysdate,61);1 row  Created. [Email protected]>insert into journal_entries values (7,sysdate,71);1 row  Created. [Email protected]>insert into journal_entries values (8,sysdate,81);1 row  Created. [Email protected]>insert into journal_entries values (9,sysdate,91);1 row  Created. [email protected]>commit; commit complete.--collecting statistics [Email protected]>exec dbms_stats.gather_table_stats (ownname=> User,tabname=> ' journal_entries ', CASCade=>true);P l/sql procedure successfully completed. 

Design four scenarios for comparison:

Sel1:select Count (*) from journal_entries;
Sel2:select count (1) from journal_entries;
Sel3:select count (id_je) from Journal_entries;

Sel4:select count (balanced) from journal_entries;

1, Scene 1 and Scene 2 equivalent

For CBO, Sel1 and Sel2 is strictly equivalent

[email protected]>alter session set statistics_level=all; Session altered. [Email protected]>select count (*)  from journal_entries;  count (*)----------          9[email protected]>select * from  Table (Dbms_xplan.display_cursor (null,null, ' runstats_last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------Sql_id  5ja3ukp4wd73p, child  number 0-------------------------------------Select count (*)  from journal_ entriesplan hash value: 42135099--------------------------------------------------------------- ------------------------------| id  | operation         | name           | starts | e-rows | a-rows |   a-time    | buffers |----------------------------------------------------------------------------- ----------------|   0 | select statement |                 |      1  |        |      1 |00:00:00.01  |       1 | |    1 |  SORT AGGREGATE  |                 |      1 |       1 |      1 |00:00:00.01 |        1 | |    2 |   index full scan| indx_ecr_id_je |      1 |       9 |      9 |00:00:00.01 |        1 |------------------------------------------------------------ ---------------------------------14 rows selected. [Email protected]>select count (1)  from journal_entries;  count (1)----------          9[email protected]>select * from  Table (Dbms_xplan.display_cursor (null,null, ' runstats_last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------Sql_id  gbxjjuqj9j7ww, child  number 0-------------------------------------select count (1)  from journal_entriesplan hash value: 42135099------------------------------------------------------------- --------------------------------| id  | operation         | name           | starts |  e-rows | a-rows |   a-time   | buffers |--------- ------------------------------------------------------------------------------------|   0  | select statement |                 |      1 |         |      1 |00:00:00.01 |        1 | |    1 |  SORT AGGREGATE  |                 |      1 |       1 |      1 |00:00:00.01 |        1 | |    2 |   INDEX FULL SCAN| INDX_ECR_ID_JE |       1 |      9 |       9 |00:00:00.01 |       1 |---------------------------- -----------------------------------------------------------------14 rows selected.

You can see that the execution plan for the two statements is exactly the same.

2, Scene 3 is also equivalent to the first two scenarios, because the id_je has a NOT NULL constraint

For Sel3, CBO does the same as for Sel1 and Sel2 since "Id_je" have a not NULL constraint.

[Email protected]>select count (Id_je)  from journal_entries; COUNT (id_je)------------           9[email  Protected]>select * from table (Dbms_xplan.display_cursor (null,null, ' runstats_last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------Sql_id  b1p4v15dwx7hs, child  number 0-------------------------------------Select count (id_je)  from journal_ entriesplan hash value: 42135099--------------------------------------------------------------- ------------------------------| id  | operation         | Name           | Starts |  E-rows | a-rows |   a-time   | buffers |------------------------------------------------------- --------------------------------------|   0 | select statement |                 |       1 |        |       1 |00:00:00.01 |       1 | |    1 |  SORT AGGREGATE  |                 |      1 |       1 |      1 |00:00:00.01 |        1 | |    2 |   INDEX FULL SCAN| INDX_ECR_ID_JE |        1 |      9 |      9 | 00:00:00.01 |       1 |------------------------------------------ ---------------------------------------------------14 rows selected.

You can see that the execution plan is exactly the same as the first two.

4, Scene 4 The front edge 3 is different, because there is no NOT NULL constraint on the balanced column, but there is an index on the balanced column, that will go to the index on this column? Let's take a look at the execution plan:

[Email protected]>select count (balanced)  from journal_entries; COUNT (BALANCED)---------------               9[email protected]>select * from table (Dbms_xplan.display_cursor (Null,null, ' Runstats_last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------Sql_id  bc3bc8c0fg14z, child  number 0-------------------------------------select count (balanced)  from journal_ entriesplan hash value: 3638043346------------------------------------------------------------- -------------------------------------------| id  | operation         | Name                       | starts | e-rows | a-rows |    a-time   | buffers |----------------------------------------------------- ---------------------------------------------------|   0 | select statement  |                            |      1 |         |      1 |00:00:00.01  |       1 | |    1 |  SORT AGGREGATE  |                             |      1 |      1 |      1 |00:00:00.01 |        1 | |    2 |   index full scan| indx_ecr_date_je_balanced |       1 |      9 |       9 |00:00:00.01 |       1 |---------------- ----------------------------------------------------------------------------------------14 rows  Selected.

We saw that the execution plan did not go through the index on the balanced column, but instead walked the combined index with Date_je. This can be viewed in another document:note:67522.1 Why isn't my index not used?

Summary:

I'm just here. Count (*), COUNT (1), and count (primary key) are in fact consistent from the execution plan, and the MOS documentation explains in detail how Oracle evaluates the execution plan, or how the CBO optimizer can make a choice using 10053 event. Because my skill is not enough, for the 10053 incident is not very clear, and temporarily do not do a demonstration, or where the wrong is not good, this can also be used as a blog to share content.

From this point of view, we should do an in-depth study of one thing, and there is ample evidence to prove it, especially if we want to develop in a certain aspect.

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1883642

Oracle in Count (1), COUNT (*) and COUNT (primary key) which is faster