這兩天聽了將近20場(chǎng)演講，感覺收獲很多，最深的感覺就是自己還有很長(zhǎng)的路要走。有幾個(gè)點(diǎn)記錄一下：
創(chuàng)新互聯(lián)公司服務(wù)項(xiàng)目包括尼開遠(yuǎn)網(wǎng)站建設(shè)、尼開遠(yuǎn)網(wǎng)站制作、尼開遠(yuǎn)網(wǎng)頁制作以及尼開遠(yuǎn)網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，尼開遠(yuǎn)網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到尼開遠(yuǎn)省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
昨天聽老貓講，提到一個(gè)普遍的問題就是Oracle里count(*)、count(1)和count(主鍵)到底哪個(gè)快的問題。這個(gè)問題看起來很簡(jiǎn)單，每個(gè)人都會(huì)有自己的答案，去百度上搜會(huì)出來一大堆帖子來講哪個(gè)更快。但是老貓說了它們?nèi)齻€(gè)其實(shí)是一樣的，我聽到之后也覺得挺詫異的，因?yàn)槲矣浀脛e人跟我說過count(主鍵)會(huì)快，然后自己簡(jiǎn)單想了一下，覺得好像是那么回事的就沒有深入去追究。接著老貓說官方有這樣的說法這三個(gè)其實(shí)是等價(jià)的。晚上回來之后到MOS上查了一下，居然被我找到了How the Oracle CBO Chooses a Path for the SELECT COUNT(*) Command (文檔 ID 124717.1)。這篇文檔講的就是在CBO優(yōu)化器模式下，Oracle怎樣去評(píng)估沒有where條件select count(*)和select count(colum)語句的最優(yōu)路徑。
1、創(chuàng)建測(cè)試表并設(shè)計(jì)測(cè)試場(chǎng)景：
--創(chuàng)建測(cè)試表 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. --創(chuàng)建索引 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. --插入測(cè)試數(shù)據(jù) 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. --收集統(tǒng)計(jì)信息 sys@ORCL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'JOURNAL_ENTRIES',cascade=>true); PL/SQL procedure successfully completed.
設(shè)計(jì)四個(gè)場(chǎng)景進(jìn)行對(duì)比：
Sel1 : Select count(*) from journal_entries;Sel4 : Select count(balanced) from journal_entries;
1、場(chǎng)景1和場(chǎng)景2等價(jià)
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.
可以看到兩個(gè)語句的執(zhí)行計(jì)劃是完全相同的。
2、場(chǎng)景3也與前兩個(gè)場(chǎng)景等價(jià)，因?yàn)閕d_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.
可以看到執(zhí)行計(jì)劃與前兩個(gè)也是完全相同的。
4、場(chǎng)景4跟前邊3個(gè)不同，因?yàn)閎alanced列上沒有NOT NULL約束，但是balanced列上有索引，那會(huì)走這個(gè)列上的索引么？我們來看一下執(zhí)行計(jì)劃：
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.
我們看到這個(gè)執(zhí)行計(jì)劃沒有走balanced列上的索引，而是走了和date_je的聯(lián)合索引。這個(gè)可以查看另一篇文檔：Note:67522.1 Why is my index not used?
小結(jié)一下：
我這里只是簡(jiǎn)單的從執(zhí)行計(jì)劃上看count(*)、count(1)和count(主鍵)其實(shí)是一致，MOS的文檔中詳細(xì)的講解了Oracle是如何評(píng)估執(zhí)行計(jì)劃的，也可以使用10053 event查看CBO優(yōu)化器是如何做出選擇的。由于我的功力還不夠，對(duì)于10053事件還不是很明白，暫時(shí)就先不做演示了，要不哪說錯(cuò)了就不好了，這也可以做為以后博客分享的內(nèi)容。
從這個(gè)事情上來看，我們對(duì)于一件事情應(yīng)該做一個(gè)深入的研究，有充足的證據(jù)來證明，尤其是想要在某一方面有深入發(fā)展的時(shí)候。
重慶分公司
重慶分公司
