溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這兩天聽了將近20場演講，感覺收獲很多，最深的感覺就是自己還有很長的路要走。有幾個點記錄一下：
昨天聽老貓講，提到一個普遍的問題就是Oracle里count(*)、count(1)和count(主鍵)到底哪個快的問題。這個問題看起來很簡單，每個人都會有自己的答案，去百度上搜會出來一大堆帖子來講哪個更快。但是老貓說了它們三個其實是一樣的，我聽到之后也覺得挺詫異的，因為我記得別人跟我說過count(主鍵)會快，然后自己簡單想了一下，覺得好像是那么回事的就沒有深入去追究。接著老貓說官方有這樣的說法這三個其實是等價的。晚上回來之后到MOS上查了一下，居然被我找到了How the Oracle CBO Chooses a Path for the SELECT COUNT(*) Command (文檔 ID 124717.1)。這篇文檔講的就是在CBO優化器模式下，Oracle怎樣去評估沒有where條件select count(*)和select count(colum)語句的最優路徑。
1、創建測試表并設計測試場景：
--創建測試表 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. --創建索引 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. --插入測試數據 sys@ORCL>insert into journal_entries values(1,sysdate,11); 1 row created. sys@ORCL>insert into journal_entries values(2,sysdate,21); 1 row created. sys@ORCL>insert into journal_entries values(3,sysdate,31); 1 row created. sys@ORCL>insert into journal_entries values(4,sysdate,41); 1 row created. sys@ORCL>insert into journal_entries values(5,sysdate,51); 1 row created. sys@ORCL>insert into journal_entries values(6,sysdate,61); 1 row created. sys@ORCL>insert into journal_entries values(7,sysdate,71); 1 row created. sys@ORCL>insert into journal_entries values(8,sysdate,81); 1 row created. sys@ORCL>insert into journal_entries values(9,sysdate,91); 1 row created. sys@ORCL>commit; Commit complete. --收集統計信息 sys@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'JOURNAL_ENTRIES',cascade=>true); PL/SQL procedure successfully completed.
設計四個場景進行對比：
Sel4 : Select count(balanced) from journal_entries;
1、場景1和場景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(*) ---------- 9 sys@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_entries Plan 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. sys@ORCL>select count(1) from journal_entries; COUNT(1) ---------- 9 sys@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_entries Plan 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.
可以看到兩個語句的執行計劃是完全相同的。
2、場景3也與前兩個場景等價，因為id_je有NOT NULL約束
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) ------------ 9 sys@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_entries Plan 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.
可以看到執行計劃與前兩個也是完全相同的。
4、場景4跟前邊3個不同，因為balanced列上沒有NOT NULL約束，但是balanced列上有索引，那會走這個列上的索引么？我們來看一下執行計劃：
sys@ORCL>select count(balanced) from journal_entries; COUNT(BALANCED) --------------- 9 sys@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_entries Plan 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.
我們看到這個執行計劃沒有走balanced列上的索引，而是走了和date_je的聯合索引。這個可以查看另一篇文檔：Note:67522.1 Why is my index not used?
小結一下：
我這里只是簡單的從執行計劃上看count(*)、count(1)和count(主鍵)其實是一致，MOS的文檔中詳細的講解了Oracle是如何評估執行計劃的，也可以使用10053 event查看CBO優化器是如何做出選擇的。由于我的功力還不夠，對于10053事件還不是很明白，暫時就先不做演示了，要不哪說錯了就不好了，這也可以做為以后博客分享的內容。
從這個事情上來看，我們對于一件事情應該做一個深入的研究，有充足的證據來證明，尤其是想要在某一方面有深入發展的時候。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
