Shulou(Shulou.com)06/01 Report--

After listening to nearly 20 speeches in the past two days, I feel that I have gained a lot. The deepest feeling is that I still have a long way to go. There are a few points to record:

Yesterday, I heard from the old cat that a common question was the question of which is faster than count (*), count (1) or count (primary key) in Oracle. This question seems very simple, everyone will have their own answer, go to Baidu search will come out a lot of posts about which is faster. But the old cat said that the three of them were actually the same, and I was surprised when I heard it, because I remember someone told me that count (primary key) would be fast, and then I thought about it briefly, and I didn't look into it as if that was the case. Then the old cat said that there is an official saying that the three are actually equivalent. When I came back in the evening, I looked it up on MOS and found How the Oracle CBO Chooses a Path for the SELECT COUNT (*) Command (document ID 124717.1). This document is about how Oracle evaluates the best path without where conditional select count (*) and select count (colum) statements in CBO optimizer mode.

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

-- create test table sys@ORCL > 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.-- create index sys@ORCL > create index indx_ecr_date_je_balanced on journal_entries (date_je,balanced); Index created.sys@ORCL > create index indx_ecr_balanced_date_je on journal_entries (balanced,date_je) Index created.sys@ORCL > create index indx_ecr_balanced on journal_entries (balanced); Index created.-- inserts test data sys@ORCL > insert into journal_entries values (1 recital sysdatelag11); 1 row created.sys@ORCL > insert into journal_entries values (2 recite sysdated21); 1 row created.sys@ORCL > insert into journal_entries values (3 menstrual sysdated31); 1 row created.sys@ORCL > insert into journal_entries values (4 parallelism 41); 1 row created.sys@ORCL > insert into journal_entries values (5 parcels 51) 1 row created.sys@ORCL > insert into journal_entries values, 1 row created.sys@ORCL > insert into journal_entries values, 1 row created.sys@ORCL > insert into journal_entries values, 1 row created.sys@ORCL > insert into journal_entries values, 1 row created.sys@ORCL > commit;Commit complete.-- collect statistics sys@ORCL > exec dbms_stats.gather_table_stats (ownname= > USER,tabname= > 'JOURNAL_ENTRIES',cascade= > true), PL/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. Scenario 1 is equivalent to scenario 2.

For CBO, Sel1 and Sel2 are strictly equivalent

Sys@ORCL > alter session set statistics_level=all;Session altered.sys@ORCL > select count (*) from journal_entries; COUNT (*)-9sys@ORCL > 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 INDEX FULL SCAN 00.01 | 1 | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 00 INDEX FULL SCAN 00.01 | 1 | 2 | INDEX FULL SCAN | INDX_ECR_ ID_JE | 1 | 9 | 9 | 00, 00, 00, 00, 00.01 | 1 |- -14 rows selected.sys@ORCL > select count (1) from journal_entries COUNT (1)-9sys@ORCL > 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 INDEX FULL SCAN 00.01 | 1 | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 00 INDEX FULL SCAN 00.01 | 1 | 2 | INDEX FULL SCAN | INDX_ECR_ ID_JE | 1 | 9 | 9 | 0000 rows selected. 00.01 | 1 |-14.

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

2. Scenario 3 is also equivalent to the first two scenarios, because id_je has NOT NULL constraints

For Sel3, CBO does the same as for Sel1 and Sel2 since "id_je" has a NOT NULL constraint.

Sys@ORCL > select count (id_je) from journal_entries;COUNT (ID_JE)-9sys@ORCL > 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 INDEX FULL SCAN 00.01 | 1 | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 00 INDEX FULL SCAN 00.01 | 1 | 2 | INDEX FULL SCAN | INDX_ ECR_ID_JE | 1 | 9 | 9 | 0000 rows selected. 00.01 | 1 |-14.

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

Scenario 4 is different from the previous three, because there is no NOT NULL constraint on the balanced column, but there is an index on the balanced column, so will it take the index on this column? Let's take a look at the implementation plan:

Sys@ORCL > select count (balanced) from journal_entries;COUNT (BALANCED)-9sys@ORCL > 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.01 | 1 | | 1 | SORT AGGREGATE | | | 1 | 1 | 1 | 00 INDEX FULL SCAN | 00.01 | 1 | 2 | INDEX FULL SCAN | INDX_ECR_DATE_JE_BALANCED | 1 | 9 | 00 | 00.01 | 1 |-- | -14 rows selected.

We see that this execution plan does not take the index on the balanced column, but the federated index with date_je. This can be viewed in another document: Note:67522.1 Why is my index not used?

A brief summary:

I'm simply looking at count (*), count (1) and count (primary key) in terms of the execution plan. MOS's documentation explains in detail how Oracle evaluates the execution plan, and you can use 10053 event to see how the CBO optimizer makes a choice. As my skills are not enough, I do not quite understand the 10053 incident, so I will not make a demonstration for the time being, or it will be bad if I am wrong, which can also be used as the content to be shared by the blog in the future.

From this point of view, we should do an in-depth study of a thing, and there is sufficient evidence to prove it, especially when we want to have in-depth development in a certain aspect.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.