【優(yōu)化】COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)、COUNT(非空列)、COUNT(允許為空列)、COUNT(DISTINCT列名)
1.1BLOG文檔結(jié)構(gòu)圖
1.2前言部分
1.2.1導(dǎo)讀和注意事項(xiàng)
各位技術(shù)愛(ài)好者，看完本文后，你可以掌握如下的技能，也可以學(xué)到一些其它你所不知道的知識(shí)，~O(∩_∩)O~：
①COUNT(1)和COUNT(*)的區(qū)別（重點(diǎn)）
②10046和10053的使用
③“SELECT COUNT(列)”和“SELECT列”在選擇索引方面的區(qū)別
④COUNT計(jì)數(shù)的優(yōu)化
Tips：
①本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和微信公眾號(hào)（xiaomaimiaolhr）上有同步更新。
②文章中用到的所有代碼、相關(guān)軟件、相關(guān)資料及本文的pdf版本都請(qǐng)前往小麥苗的云盤(pán)下載，小麥苗的云盤(pán)地址見(jiàn)：http://blog.itpub.net/26736162/viewspace-1624453/。
③若網(wǎng)頁(yè)文章代碼格式有錯(cuò)亂，請(qǐng)下載pdf格式的文檔來(lái)閱讀。
④在本篇BLOG中，代碼輸出部分一般放在一行一列的表格中。
⑤ 本文適合于Oracle初中級(jí)人員閱讀，Oracle大師請(qǐng)略過(guò)本文。
本文若有錯(cuò)誤或不完善的地方請(qǐng)大家多多指正，您的批評(píng)指正是我寫(xiě)作的最大動(dòng)力。
1.2.2本文簡(jiǎn)介
看了很多有關(guān)COUNT(1)和COUNT(*)的區(qū)別和效率，眾說(shuō)紛紜。最終還是決定自己動(dòng)手實(shí)驗(yàn)一番。
-------------------------------------------------------------------------
第二章實(shí)驗(yàn)部分
2.1實(shí)驗(yàn)環(huán)境介紹
項(xiàng)目
source db
db類(lèi)型
RAC
db version
11.2.0.3.0
db存儲(chǔ)
ASM
OS版本及kernel版本
RHEL 6.5
2.2實(shí)驗(yàn)?zāi)繕?biāo)
弄清楚COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)、COUNT(非空列)、COUNT(允許為空列)、COUNT(DISTINCT列名)之間的區(qū)別，以及它們之間的效率問(wèn)題。
2.3實(shí)驗(yàn)過(guò)程
2.3.1實(shí)驗(yàn)?zāi)_本
--創(chuàng)建1W行的表
DROP TABLE T_COUNT_LHR;
CREATE TABLE T_COUNT_LHR AS
SELECT OBJECT_ID,
OBJECT_NAME,
OWNER,
DATA_OBJECT_ID,
OBJECT_TYPE,
LAST_DDL_TIME
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID IS NOT NULL
AND D.OBJECT_NAME IS NOT NULL
AND ROWNUM <= 10000;
--更新空值，
UPDATE T_COUNT_LHR t SET t.object_type='' WHERE Rownum<=5;
UPDATE T_COUNT_LHR t SET t.LAST_DDL_TIME=T.LAST_DDL_TIME+ROWNUM;
UPDATE T_COUNT_LHR t SET t.LAST_DDL_TIME='' WHERE Rownum<=1;
COMMIT;
--添加主鍵、非空約束、唯一索引、普通索引
ALTER TABLE T_COUNT_LHR ADD CONSTRAINT PK_OBJECT_ID PRIMARY KEY(OBJECT_ID);
ALTER TABLE T_COUNT_LHR MODIFY OBJECT_NAME NOT NULL;
CREATE UNIQUE INDEX IDX_LDT ON T_COUNT_LHR(LAST_DDL_TIME);
CREATE? INDEX IDX_DATA_OBJECT_ID ON T_COUNT_LHR(DATA_OBJECT_ID);
CREATE? INDEX IDX_DATA_OWNERON T_COUNT_LHR(OWNER);
ALTER TABLE T_COUNT_LHR MODIFYOWNERNOT NULL;
--收集統(tǒng)計(jì)信息
EXEC dbms_stats.gather_table_stats(USER,'T_COUNT_LHR');
SELECT d.COLUMN_NAME,d.DATA_TYPE,d.NUM_NULLS,d.NUM_DISTINCT,d.LAST_ANALYZED FROM cols d WHERE d.TABLE_NAME='T_COUNT_LHR';
表的信息如下所示：
列名
是否主鍵
是否允許為空
是否有索引
數(shù)據(jù)類(lèi)型
空值的行數(shù)
不同值的行數(shù)
總行數(shù)
OBJECT_ID
Y
N
唯一索引
NUMBER
0
10000
10000
OBJECT_NAME
N
無(wú)
VARCHAR2
0
8112
10000
OWNER
N
普通索引（IDX_OWNER）
VARCHAR2
0
5
10000
DATA_OBJECT_ID
Y
普通索引（IDX_DATA_OBJECT_ID）
NUMBER
7645
2318
10000
OBJECT_TYPE
Y
無(wú)
VARCHAR2
5
20
10000
LAST_DDL_TIME
Y
唯一索引（IDX_LDT）
DATE
1
9999
10000
需要統(tǒng)計(jì)如下幾種情況：
SELECT COUNT(1) FROM T_COUNT_LHR;--走索引
SELECT COUNT(*) FROM T_COUNT_LHR;--走索引
SELECT COUNT(ROWID) FROM T_COUNT_LHR; --走索引
SELECT COUNT(OBJECT_ID) FROM T_COUNT_LHR; --走索引
SELECT COUNT(OBJECT_NAME) FROM T_COUNT_LHR;--走索引
SELECT COUNT(OWNER) FROM T_COUNT_LHR D;--走索引
SELECT COUNT(D.DATA_OBJECT_ID) FROM T_COUNT_LHR D; --走索引
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D;--走索引
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;--走索引
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D; --不走索引
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;--走索引
SELECT COUNT(DISTINCT DATA_OBJECT_ID) FROM T_COUNT_LHR D;--不走索引
SELECT COUNT(DISTINCT OWNER) FROM T_COUNT_LHR D;--走索引
SELECT COUNT(DISTINCT DATA_OBJECT_ID) FROM T_COUNT_LHR D WHERE DATA_OBJECT_ID IS NOT NULL ;--走索引
2.3.2執(zhí)行計(jì)劃
介紹
SQL命令
執(zhí)行計(jì)劃
返回行數(shù)
COUNT(1)和COUNT(常量)是一樣的，SELECT COUNT(*) "COUNT(1)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(1) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id? | Operation???????????? | Name???????? | Rows? | Cost (%CPU)| Time???? |
|-----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????????? |???? 1 |???? 7?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?????? |????????????? |???? 1 |??????????? |????????? |
|?? 2 |?? INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |???? 7?? (0)| 00:00:01 |
|-----------------------------------------------------------------------------10000
SELECT COUNT(*) "COUNT(*)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(*) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id? | Operation???????????? | Name???????? | Rows? | Cost (%CPU)| Time???? |
|-----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????????? |???? 1 |???? 7?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?????? |????????????? |???? 1 |??????????? |????????? |
|?? 2 |?? INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |???? 7?? (0)| 00:00:01 |
|-----------------------------------------------------------------------------10000
SELECT COUNT("T_COUNT_LHR".ROWID) "COUNT(ROWID)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(ROWID) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-------------------------------------------------------------------------------------
| Id? | Operation???????????? | Name???????? | Rows? | Bytes | Cost (%CPU)| Time???? |
|-------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????????? |???? 1 |??? 12 |???? 7?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?????? |????????????? |???? 1 |??? 12 |??????????? |????????? |
|?? 2 |?? INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |?? 117K|???? 7?? (0)| 00:00:01 |
|-------------------------------------------------------------------------------------10000
SELECT COUNT(*) "COUNT(OBJECT_ID)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(OBJECT_ID) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id? | Operation???????????? | Name???????? | Rows? | Cost (%CPU)| Time???? |
|-----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????????? |???? 1 |???? 7?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?????? |????????????? |???? 1 |??????????? |????????? |
|?? 2 |?? INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |???? 7?? (0)| 00:00:01 |
|-----------------------------------------------------------------------------10000
SELECT COUNT(*) "COUNT(OBJECT_NAME)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(OBJECT_NAME) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id? | Operation???????????? | Name???????? | Rows? | Cost (%CPU)| Time???? |
|-----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????????? |???? 1 |???? 7?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?????? |????????????? |???? 1 |??????????? |????????? |
|?? 2 |?? INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |???? 7?? (0)| 00:00:01 |
|-----------------------------------------------------------------------------10000
OWNER列含有索引，非空列，選擇主鍵索引
SELECT COUNT(OWNER) FROM T_COUNT_LHR D;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id? | Operation???????????? | Name???????? | Rows? | Cost (%CPU)| Time???? |
|-----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????????? |???? 1 |???? 7?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?????? |????????????? |???? 1 |??????????? |????????? |
|?? 2 |?? INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |???? 7?? (0)| 00:00:01 |
|-----------------------------------------------------------------------------10000
DATA_OBJECT_ID列允許為空，含有普通索引
SELECT COUNT(D.DATA_OBJECT_ID) FROM T_COUNT_LHR D;
Plan hash value: 2404962198
|-------------------------------------------------------------------------------------------
| Id? | Operation???????????? | Name?????????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
|-------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |??????????????????? |???? 1 |???? 2 |???? 3?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?????? |??????????????????? |???? 1 |???? 2 |??????????? |????????? |
|?? 2 |?? INDEX FAST FULL SCAN| IDX_DATA_OBJECT_ID | 10000 | 20000 |???? 3?? (0)| 00:00:01 |
|-------------------------------------------------------------------------------------------2355
注意，COUNT（列）計(jì)算的是列值為非空的行數(shù)
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D;
Plan hash value: 887614938
|--------------------------------------------------------------------------------
| Id? | Operation???????????? | Name??? | Rows? | Bytes | Cost (%CPU)| Time???? |
|--------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |???????? |???? 1 |???? 8 |???? 9?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?????? |???????? |???? 1 |???? 8 |??????????? |????????? |
|?? 2 |?? INDEX FAST FULL SCAN| IDX_LDT | 10000 | 80000 |???? 9?? (0)| 00:00:01 |
|--------------------------------------------------------------------------------9999
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;
Plan hash value: 887614938
|--------------------------------------------------------------------------------
| Id? | Operation???????????? | Name??? | Rows? | Bytes | Cost (%CPU)| Time???? |
|--------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |???????? |???? 1 |???? 8 |???? 9?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?????? |???????? |???? 1 |???? 8 |??????????? |????????? |
|*? 2 |?? INDEX FAST FULL SCAN| IDX_LDT |? 9999 | 79992 |???? 9?? (0)| 00:00:01 |
|--------------------------------------------------------------------------------9999
由于列中存在空值，所以不會(huì)選擇索引。加上IS NOT NULL就可以選擇索引了。
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D;
Plan hash value: 2392171920
|--------------------------------------------------------------------------------
| Id? | Operation???????? | Name??????? | Rows? | Bytes | Cost (%CPU)| Time???? |
|--------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |???????????? | 10000 | 80000 |??? 22?? (0)| 00:00:01 |
|?? 1 |? TABLE ACCESS FULL| T_COUNT_LHR | 10000 | 80000 |??? 22?? (0)| 00:00:01 |
|--------------------------------------------------------------------------------10000
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;
Plan hash value: 2419516343
|-------------------------------------------------------------------------------
| Id? | Operation??????????? | Name??? | Rows? | Bytes | Cost (%CPU)| Time???? |
|-------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? |???????? |? 9999 | 79992 |???? 9?? (0)| 00:00:01 |
|*? 1 |? INDEX FAST FULL SCAN| IDX_LDT |? 9999 | 79992 |???? 9?? (0)| 00:00:01 |
|-------------------------------------------------------------------------------9999
DISTINCT允許為空列不會(huì)選擇索引，而DISTINCT非空列會(huì)選擇索引
SELECT COUNT(DISTINCT DATA_OBJECT_ID) FROM T_COUNT_LHR D;
Plan hash value: 3258478826
|-----------------------------------------------------------------------------------
| Id? | Operation??????????? | Name??????? | Rows? | Bytes | Cost (%CPU)| Time???? |
|-----------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? |???????????? |???? 1 |??? 13 |??? 23?? (5)| 00:00:01 |
|?? 1 |? SORT AGGREGATE????? |???????????? |???? 1 |??? 13 |??????????? |????????? |
|?? 2 |?? VIEW?????????????? | VW_DAG_0??? |? 2318 | 30134 |??? 23?? (5)| 00:00:01 |
|?? 3 |??? HASH GROUP BY???? |???????????? |? 2318 |? 4636 |??? 23?? (5)| 00:00:01 |
|?? 4 |???? TABLE ACCESS FULL| T_COUNT_LHR | 10000 | 20000 |??? 22?? (0)| 00:00:01 |
|-----------------------------------------------------------------------------------2318
SELECT COUNT(DISTINCT OWNER) FROM T_COUNT_LHR D;
Plan hash value: 4008695099
|-----------------------------------------------------------------------------------------
| Id? | Operation?????????????? | Name?????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
|-----------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??????? |??????????????? |???? 1 |??? 17 |???? 9? (12)| 00:00:01 |
|?? 1 |? SORT AGGREGATE???????? |??????????????? |???? 1 |??? 17 |??????????? |????????? |
|?? 2 |?? VIEW????????????????? | VW_DAG_0?????? |???? 5 |??? 85 |???? 9? (12)| 00:00:01 |
|?? 3 |??? HASH GROUP BY??????? |??????????????? |???? 5 |??? 30 |???? 9? (12)| 00:00:01 |
|?? 4 |???? INDEX FAST FULL SCAN| IDX_DATA_OWNER | 10000 | 60000 |???? 8?? (0)| 00:00:01 |
|-----------------------------------------------------------------------------------------5
2.3.310046事件
alter session set events '10046 trace name context forever, level 12';
SELECT COUNT(*) FROM T_COUNT_LHR;
alter system flush buffer_cache;
alter system flush shared_pool;
SELECT COUNT(1) FROM T_COUNT_LHR;
alter system flush buffer_cache;
alter system flush shared_pool;
SELECT COUNT(ROWID) FROM T_COUNT_LHR;
alter session set events '10046 trace name context OFF';
select value from v$diag_info where name like '%Default%';
exit
tkprof orclasm_ora_13825.trc orclasm_ora_13825.out
alter system flush buffer_cache;
alter system flush shared_pool;
alter session set sql_trace=true;
SELECT COUNT(*) FROM T_COUNT_LHR;
alter system flush buffer_cache;
alter system flush shared_pool;
SELECT COUNT(1) FROM T_COUNT_LHR;
alter system flush buffer_cache;
alter system flush shared_pool;
SELECT COUNT(ROWID) FROM T_COUNT_LHR;
alter session set sql_trace=false;
select value from v$diag_info where name like '%Default%';
********************************************************************************
count??? = number of times OCI procedure was executed
cpu????? = cpu time in seconds executing
elapsed? = elapsed time in seconds executing
disk???? = number of physical reads of buffers from disk
query??? = number of buffers gotten for consistent read
current? = number of buffers gotten in current mode (usually for update)
rows???? = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: g9rksvy7gkdmj Plan Hash: 1265209789
SELECT COUNT(*)
FROM
T_COUNT_LHR
call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
------- ------? -------- ---------- ---------- ---------- ----------? ----------
Parse??????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Execute????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Fetch??????? 2????? 0.00?????? 0.55???????? 22???????? 25????????? 0?????????? 1
------- ------? -------- ---------- ---------- ---------- ----------? ----------
total??????? 4????? 0.01?????? 0.56???????? 22???????? 25????????? 0?????????? 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)? Row Source Operation
---------- ---------- ----------? ---------------------------------------------------
1????????? 1????????? 1? SORT AGGREGATE (cr=25 pr=22 pw=0 time=552894 us)
10000????? 10000????? 10000?? INDEX FAST FULL SCAN PK_OBJECT_ID (cr=25 pr=22 pw=0 time=2502 us cost=7 size=0 card=10000)(object id 159543)
********************************************************************************
SQL ID: 4abkxq9x7uamj Plan Hash: 1265209789
SELECT COUNT(1)
FROM
T_COUNT_LHR
call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
------- ------? -------- ---------- ---------- ---------- ----------? ----------
Parse??????? 1????? 0.00?????? 0.01????????? 0????????? 0????????? 0?????????? 0
Execute????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Fetch??????? 2????? 0.00?????? 0.00???????? 22???????? 25????????? 0?????????? 1
------- ------? -------- ---------- ---------- ---------- ----------? ----------
total??????? 4????? 0.00?????? 0.01???????? 22???????? 25????????? 0?????????? 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)? Row Source Operation
---------- ---------- ----------? ---------------------------------------------------
1????????? 1????????? 1? SORT AGGREGATE (cr=25 pr=22 pw=0 time=5934 us)
10000????? 10000????? 10000?? INDEX FAST FULL SCAN PK_OBJECT_ID (cr=25 pr=22 pw=0 time=2469 us cost=7 size=0 card=10000)(object id 159543)
********************************************************************************
SQL ID: 4tm3jwzff8ub8 Plan Hash: 1265209789
SELECT COUNT(ROWID)
FROM
T_COUNT_LHR
call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
------- ------? -------- ---------- ---------- ---------- ----------? ----------
Parse??????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Execute????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Fetch??????? 2????? 0.00?????? 0.01???????? 22???????? 25????????? 0?????????? 1
------- ------? -------- ---------- ---------- ---------- ----------? ----------
total??????? 4????? 0.00?????? 0.01???????? 22???????? 25????????? 0?????????? 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)? Row Source Operation
---------- ---------- ----------? ---------------------------------------------------
1????????? 1????????? 1? SORT AGGREGATE (cr=25 pr=22 pw=0 time=11264 us)
10000????? 10000????? 10000?? INDEX FAST FULL SCAN PK_OBJECT_ID (cr=25 pr=22 pw=0 time=762 us cost=7 size=120000 card=10000)(object id 159543)
********************************************************************************
2.3.410053事件
alter session set events '10053 trace name context forever, level 12';
alter session set events '10053 trace name context OFF';
alter system flush buffer_cache;
alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 12';
SELECT COUNT(*) FROM T_COUNT_LHR;
alter system flush buffer_cache;
alter system flush shared_pool;
SELECT COUNT(1) FROM T_COUNT_LHR;
alter system flush buffer_cache;
alter system flush shared_pool;
SELECT COUNT(ROWID) FROM T_COUNT_LHR;
alter session set events '10053 trace name context OFF';
select value from v$diag_info where name like '%Default%';
搜索關(guān)鍵詞“Final”、“Starting”：
Finalquery after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1216, alloc=16344), compile(in-use=58000, alloc=58544), execution(in-use=2480, alloc=4032)
kkoqbc-subheap (create addr=0x7f9950a9fb58)
****************
QUERY BLOCK TEXT
****************
SELECT COUNT(*) FROM T_COUNT_LHR
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=159542 hint_alias="T_COUNT_LHR"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 1752 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM:? 10 milliseconds (default is 10)
MBRC:?????? NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T_COUNT_LHR? Alias: T_COUNT_LHR
#Rows: 10000? #Blks:? 75? AvgRowLen:? 44.00? ChainCnt:? 0.00
Index Stats::
Index: IDX_DATA_OBJECT_ID? Col#: 4
LVLS: 1? #LB: 5? #DK: 2318? LB/K: 1.00? DB/K: 1.00? CLUF: 86.00
Index: IDX_DATA_OWNER? Col#: 3
LVLS: 1? #LB: 23? #DK: 5? LB/K: 4.00? DB/K: 28.00? CLUF: 141.00
Index: IDX_LDT? Col#: 6
LVLS: 1? #LB: 26? #DK: 9999? LB/K: 1.00? DB/K: 1.00? CLUF: 377.00
Index: PK_OBJECT_ID? Col#: 1
LVLS: 1? #LB: 20? #DK: 10000? LB/K: 1.00? DB/K: 1.00? CLUF: 76.00
Access path analysis for T_COUNT_LHR
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T_COUNT_LHR[T_COUNT_LHR]
Table: T_COUNT_LHR? Alias: T_COUNT_LHR
Card: Original: 10000.000000? Rounded: 10000? Computed: 10000.00? Non Adjusted: 10000.00
Access Path: TableScan
Cost:? 22.10? Resp: 22.10? Degree: 0
Cost_io: 22.00? Cost_cpu: 2034108
Resp_io: 22.00? Resp_cpu: 2034108
Access Path: index (index (FFS))
Index: IDX_DATA_OWNER
resc_io: 8.00? resc_cpu: 1363793
ix_sel: 0.000000? ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost:? 8.06? Resp: 8.06? Degree: 1
Cost_io: 8.00? Cost_cpu: 1363793
Resp_io: 8.00? Resp_cpu: 1363793
Access Path: index (index (FFS))
Index: PK_OBJECT_ID
resc_io: 7.00? resc_cpu: 1342429
ix_sel: 0.000000? ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost:? 7.06? Resp: 7.06? Degree: 1
Cost_io: 7.00? Cost_cpu: 1342429
Resp_io: 7.00? Resp_cpu: 1342429
Access Path: index (FullScan)
Index: IDX_DATA_OWNER
resc_io: 24.00? resc_cpu: 2170915
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 24.10? Resp: 24.10? Degree: 1
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 1
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: IDX_DATA_OWNER
resc_io: 24.00? resc_cpu: 2170915
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 24.10? Resp: 24.10? Degree: 0
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 0
****** finished trying bitmap/domain indexes ******
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: IDX_DATA_OWNER
resc_io: 24.00? resc_cpu: 2170915
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 24.10? Resp: 24.10? Degree: 0
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 0
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 0
Bitmap nodes:
Used PK_OBJECT_ID
Cost = 26.377821, sel = 1.000000
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: IndexFFS
Index: PK_OBJECT_ID
Cost: 7.06? Degree: 1? Resp: 7.06? Card: 10000.00? Bytes: 0
***************************************
。。。。。。。。。。。。。。
Starting SQL statement dump
user_id=90 user_name=LHR module=SQL*Plus action=
sql_id=g9rksvy7gkdmj plan_hash_value=1265209789 problem_type=3
----- Current SQL Statement for this session (sql_id=g9rksvy7gkdmj) -----
SELECT COUNT(*) FROM T_COUNT_LHR
sql_text_length=33
sql=SELECT COUNT(*) FROM T_COUNT_LHR
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id? | Operation????????????? | Name??????? | Rows? | Bytes | Cost? | Time????? |
---------------------------------------------+-----------------------------------+
| 0?? | SELECT STATEMENT?????? |???????????? |?????? |?????? |???? 7 |?????????? |
| 1?? |? SORT AGGREGATE??????? |???????????? |???? 1 |?????? |?????? |?????????? |
| 2?? |?? INDEX FAST FULL SCAN | PK_OBJECT_ID|?? 10K |?????? |???? 7 |? 00:00:01 |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
Content of other_xml column
===========================
db_version???? : 11.2.0.3
parse_schema?? : LHR
plan_hash????? : 1265209789
plan_hash_2??? : 3881728982
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('star_transformation_enabled' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T_COUNT_LHR"@"SEL$1" ("T_COUNT_LHR"."OBJECT_ID"))
END_OUTLINE_DATA
*/
。。。。。。。。。。。。。。。。
====================== END SQL Statement Dump ======================
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(1)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1240, alloc=16344), compile(in-use=58312, alloc=58544), execution(in-use=2480, alloc=4032)
kkoqbc-subheap (create addr=0x7f9950a9fb58)
****************
QUERY BLOCK TEXT
****************
SELECT COUNT(1) FROM T_COUNT_LHR
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=159542 hint_alias="T_COUNT_LHR"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 1752 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM:? 10 milliseconds (default is 10)
MBRC:?????? NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T_COUNT_LHR? Alias: T_COUNT_LHR
#Rows: 10000? #Blks:? 75? AvgRowLen:? 44.00? ChainCnt:? 0.00
Index Stats::
Index: IDX_DATA_OBJECT_ID? Col#: 4
LVLS: 1? #LB: 5? #DK: 2318? LB/K: 1.00? DB/K: 1.00? CLUF: 86.00
Index: IDX_DATA_OWNER? Col#: 3
LVLS: 1? #LB: 23? #DK: 5? LB/K: 4.00? DB/K: 28.00? CLUF: 141.00
Index: IDX_LDT? Col#: 6
LVLS: 1? #LB: 26? #DK: 9999? LB/K: 1.00? DB/K: 1.00? CLUF: 377.00
Index: PK_OBJECT_ID? Col#: 1
LVLS: 1? #LB: 20? #DK: 10000? LB/K: 1.00? DB/K: 1.00? CLUF: 76.00
Access path analysis for T_COUNT_LHR
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T_COUNT_LHR[T_COUNT_LHR]
Table: T_COUNT_LHR? Alias: T_COUNT_LHR
Card: Original: 10000.000000? Rounded: 10000? Computed: 10000.00? Non Adjusted: 10000.00
Access Path: TableScan
Cost:? 22.10? Resp: 22.10? Degree: 0
Cost_io: 22.00? Cost_cpu: 2034108
Resp_io: 22.00? Resp_cpu: 2034108
Access Path: index (index (FFS))
Index: IDX_DATA_OWNER
resc_io: 8.00? resc_cpu: 1363793
ix_sel: 0.000000? ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost:? 8.06? Resp: 8.06? Degree: 1
Cost_io: 8.00? Cost_cpu: 1363793
Resp_io: 8.00? Resp_cpu: 1363793
Access Path: index (index (FFS))
Index: PK_OBJECT_ID
resc_io: 7.00? resc_cpu: 1342429
ix_sel: 0.000000? ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost:? 7.06? Resp: 7.06? Degree: 1
Cost_io: 7.00? Cost_cpu: 1342429
Resp_io: 7.00? Resp_cpu: 1342429
Access Path: index (FullScan)
Index: IDX_DATA_OWNER
resc_io: 24.00? resc_cpu: 2170915
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 24.10? Resp: 24.10? Degree: 1
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 1
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: IDX_DATA_OWNER
resc_io: 24.00? resc_cpu: 2170915
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 24.10? Resp: 24.10? Degree: 0
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 0
****** finished trying bitmap/domain indexes ******
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: IDX_DATA_OWNER
resc_io: 24.00? resc_cpu: 2170915
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 24.10? Resp: 24.10? Degree: 0
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 0
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 0
Bitmap nodes:
Used PK_OBJECT_ID
Cost = 26.377821, sel = 1.000000
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: IndexFFS
Index: PK_OBJECT_ID
Cost: 7.06? Degree: 1? Resp: 7.06? Card: 10000.00? Bytes: 0
***************************************
。。。。。。。。。。。。。。。。。
Starting SQL statement dump
user_id=90 user_name=LHR module=SQL*Plus action=
sql_id=4abkxq9x7uamj plan_hash_value=1265209789 problem_type=3
----- Current SQL Statement for this session (sql_id=4abkxq9x7uamj) -----
SELECT COUNT(1) FROM T_COUNT_LHR
sql_text_length=33
sql=SELECT COUNT(1) FROM T_COUNT_LHR
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id? | Operation????????????? | Name??????? | Rows? | Bytes | Cost? | Time????? |
---------------------------------------------+-----------------------------------+
| 0?? | SELECT STATEMENT?????? |???????????? |?????? |?????? |???? 7 |?????????? |
| 1?? |? SORT AGGREGATE??????? |???????????? |???? 1 |?????? |?????? |?????????? |
| 2?? |?? INDEX FAST FULL SCAN | PK_OBJECT_ID|?? 10K |?????? |???? 7 |? 00:00:01 |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
Content of other_xml column
===========================
db_version???? : 11.2.0.3
parse_schema?? : LHR
plan_hash????? : 1265209789
plan_hash_2??? : 3881728982
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('star_transformation_enabled' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T_COUNT_LHR"@"SEL$1" ("T_COUNT_LHR"."OBJECT_ID"))
END_OUTLINE_DATA
*/
====================== END SQL Statement Dump ======================
******************************************
----- Current SQL Statement for this session (sql_id=4tm3jwzff8ub8) -----
SELECT COUNT(ROWID) FROM T_COUNT_LHR
*******************************************
。。。。。。。。。
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT("T_COUNT_LHR".ROWID) "COUNT(ROWID)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1240, alloc=16344), compile(in-use=58224, alloc=62688), execution(in-use=2480, alloc=4032)
kkoqbc-subheap (create addr=0x7f9950a9fb58)
****************
QUERY BLOCK TEXT
****************
SELECT COUNT(ROWID) FROM T_COUNT_LHR
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=159542 hint_alias="T_COUNT_LHR"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 1752 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM:? 10 milliseconds (default is 10)
MBRC:?????? NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T_COUNT_LHR? Alias: T_COUNT_LHR
#Rows: 10000? #Blks:? 75? AvgRowLen:? 44.00? ChainCnt:? 0.00
Index Stats::
Index: IDX_DATA_OBJECT_ID? Col#: 4
LVLS: 1? #LB: 5? #DK: 2318? LB/K: 1.00? DB/K: 1.00? CLUF: 86.00
Index: IDX_DATA_OWNER? Col#: 3
LVLS: 1? #LB: 23? #DK: 5? LB/K: 4.00? DB/K: 28.00? CLUF: 141.00
Index: IDX_LDT? Col#: 6
LVLS: 1? #LB: 26? #DK: 9999? LB/K: 1.00? DB/K: 1.00? CLUF: 377.00
Index: PK_OBJECT_ID? Col#: 1
LVLS: 1? #LB: 20? #DK: 10000? LB/K: 1.00? DB/K: 1.00? CLUF: 76.00
Access path analysis for T_COUNT_LHR
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T_COUNT_LHR[T_COUNT_LHR]
Table: T_COUNT_LHR? Alias: T_COUNT_LHR
Card: Original: 10000.000000? Rounded: 10000? Computed: 10000.00? Non Adjusted: 10000.00
Access Path: TableScan
Cost:? 22.10? Resp: 22.10? Degree: 0
Cost_io: 22.00? Cost_cpu: 2034108
Resp_io: 22.00? Resp_cpu: 2034108
Access Path: index (index (FFS))
Index: IDX_DATA_OWNER
resc_io: 8.00? resc_cpu: 1363793
ix_sel: 0.000000? ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost:? 8.06? Resp: 8.06? Degree: 1
Cost_io: 8.00? Cost_cpu: 1363793
Resp_io: 8.00? Resp_cpu: 1363793
Access Path: index (index (FFS))
Index: PK_OBJECT_ID
resc_io: 7.00? resc_cpu: 1342429
ix_sel: 0.000000? ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost:? 7.06? Resp: 7.06? Degree: 1
Cost_io: 7.00? Cost_cpu: 1342429
Resp_io: 7.00? Resp_cpu: 1342429
Access Path: index (FullScan)
Index: IDX_DATA_OWNER
resc_io: 24.00? resc_cpu: 2170915
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 24.10? Resp: 24.10? Degree: 1
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 1
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: IDX_DATA_OWNER
resc_io: 24.00? resc_cpu: 2170915
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 24.10? Resp: 24.10? Degree: 0
Access Path: index (FullScan)
Index: PK_OBJECT_ID
resc_io: 21.00? resc_cpu: 2149550
ix_sel: 1.000000? ix_sel_with_filters: 1.000000
Cost: 21.10? Resp: 21.10? Degree: 0
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexFFS
Index: PK_OBJECT_ID
Cost: 7.06? Degree: 1? Resp: 7.06? Card: 10000.00? Bytes: 0
***************************************
。。。。。。。。。。。。。。。。。
Starting SQL statement dump
user_id=90 user_name=LHR module=SQL*Plus action=
sql_id=4tm3jwzff8ub8 plan_hash_value=1265209789 problem_type=3
----- Current SQL Statement for this session (sql_id=4tm3jwzff8ub8) -----
SELECT COUNT(ROWID) FROM T_COUNT_LHR
sql_text_length=37
sql=SELECT COUNT(ROWID) FROM T_COUNT_LHR
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id? | Operation????????????? | Name??????? | Rows? | Bytes | Cost? | Time????? |
---------------------------------------------+-----------------------------------+
| 0?? | SELECT STATEMENT?????? |???????????? |?????? |?????? |???? 7 |?????????? |
| 1?? |? SORT AGGREGATE??????? |???????????? |???? 1 |??? 12 |?????? |?????????? |
| 2?? |?? INDEX FAST FULL SCAN | PK_OBJECT_ID|?? 10K |? 117K |???? 7 |? 00:00:01 |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
Content of other_xml column
===========================
db_version???? : 11.2.0.3
parse_schema?? : LHR
plan_hash????? : 1265209789
plan_hash_2??? : 3881728982
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('star_transformation_enabled' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T_COUNT_LHR"@"SEL$1" ("T_COUNT_LHR"."OBJECT_ID"))
END_OUTLINE_DATA
*/
。。。。。。。。。。。。
====================== END SQL Statement Dump ======================
2.3.5COUNT(COL1)和SELECT COL1的區(qū)別
其實(shí)在2014年的時(shí)候，小麥苗發(fā)布過(guò)一篇博客（http://blog.itpub.net/26736162/viewspace-1329880/），里邊對(duì)這個(gè)問(wèn)題有詳細(xì)的實(shí)驗(yàn)。今天就把這個(gè)實(shí)驗(yàn)搬過(guò)來(lái)吧。
drop table t? purge;
Create Table t? nologging As select *? from??? dba_objects d ;
create?? index ind_objectname on? t(object_name);
set autotrace traceonly;
select t.object_name from t where t.object_name ='T';??????? --走索引
select t.object_name from t where UPPER(t.object_name) ='T';?????? --不走索引
select t.object_name from t where UPPER(t.object_name) ='T' and t.object_name IS NOT NULL ;?????????? --走索引(INDEX FAST FULL SCAN)
select t.object_name from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;???? --走索引(INDEX FAST FULL SCAN)
select t.object_name,t.owner from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;???? --不走索引
C:\Users\xiaomaimiao>sqlplus lhr/lhr@orclasm
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 12 10:52:29 2014
Copyright (c) 1982, 2010, Oracle.? All rights reserved.
連接到:
Oracle Database 11g Enterprise Edition Release11.2.0.3.0 - 64bitProduction
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>
SQL>
SQL> drop table t? purge;
表已刪除。
SQL> Create Table t? nologging As select *? from??? dba_objects d ;
表已創(chuàng)建。
SQL>? create?? index ind_objectname on? t(object_name);
索引已創(chuàng)建。
---- t表所有列均可以為空
SQL> desc t
NameNull???? Type
----------------------------------------- -------- ----------------------------
OWNERVARCHAR2(30)
OBJECT_NAMEVARCHAR2(128)
SUBOBJECT_NAMEVARCHAR2(30)
OBJECT_IDNUMBER
DATA_OBJECT_IDNUMBER
OBJECT_TYPEVARCHAR2(19)
CREATEDDATE
LAST_DDL_TIMEDATE
TIMESTAMPVARCHAR2(19)
STATUSVARCHAR2(7)
TEMPORARYVARCHAR2(1)
GENERATEDVARCHAR2(1)
SECONDARYVARCHAR2(1)
NAMESPACENUMBER
EDITION_NAMEVARCHAR2(30)
SQL>
SQL>? set autotrace traceonly;
SQL>select t.object_name from t where t.object_name ='T';
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 4280870634
-----------------------------------------------------------------------------------
| Id? | Operation??????? | Name?????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-----------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT |??????????????? |???? 1 |??? 66 |???? 3?? (0)| 00:00:01 |
|*? 1 |INDEX RANGE SCAN| IND_OBJECTNAME |???? 1 |??? 66 |???? 3?? (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_503ygb00mbj6k165e82cd" used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
34? recursive calls
43? db block gets
127? consistent gets
398? physical reads
15476? redo size
349? bytes sent via SQL*Net to client
359? bytes received via SQL*Net from client
2? SQL*Net roundtrips to/from client
0? sorts (memory)
0? sorts (disk)
1? rows processed
SQL>select t.object_name from t where UPPER(t.object_name) ='T';
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |??? 12 |?? 792 |?? 305?? (1)| 00:00:04 |
|*? 1 |TABLE ACCESS FULL| T??? |??? 12 |?? 792 |?? 305?? (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("T"."OBJECT_NAME")='T')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_9p76pys5gdb2b94ecae5c" used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
29? recursive calls
43? db block gets
1209? consistent gets
1092? physical reads
15484? redo size
349? bytes sent via SQL*Net to client
359? bytes received via SQL*Net from client
2? SQL*Net roundtrips to/from client
0? sorts (memory)
0? sorts (disk)
1? rows processed
SQL>select t.object_name from t where UPPER(t.object_name) ='T' and t.object_name IS NOT NULL ;
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 3379870158
---------------------------------------------------------------------------------------
| Id? | Operation??????????? | Name?????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? |??????????????? |??? 51 |? 3366 |?? 110?? (1)| 00:00:02 |
|*? 1 |INDEX FAST FULL SCAN| IND_OBJECTNAME |??? 51 |? 3366 |?? 110?? (1)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_NAME" IS NOT NULL AND UPPER("T"."OBJECT_NAME")='T')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_czkarb71kthws18b0c28f" used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
29? recursive calls
43? db block gets
505? consistent gets
384? physical reads
15612? redo size
349? bytes sent via SQL*Net to client
359? bytes received via SQL*Net from client
2? SQL*Net roundtrips to/from client
0? sorts (memory)
0? sorts (disk)
1? rows processed
SQL>select t.object_name,t.owner from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |??? 51 |? 4233 |?? 304?? (1)| 00:00:04 |
|*? 1 |TABLE ACCESS FULL| T??? |??? 51 |? 4233 |?? 304?? (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_NAME" IS NOT NULL AND
UPPER("T"."OBJECT_NAME")||'AAA'='TAAA')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_au9a1c4hwdtb894ecae5c" used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
30? recursive calls
44? db block gets
1210? consistent gets
1091? physical reads
15748? redo size
408? bytes sent via SQL*Net to client
359? bytes received via SQL*Net from client
2? SQL*Net roundtrips to/from client
0? sorts (memory)
0? sorts (disk)
1? rows processed
SQL>select t.object_name from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 3379870158
---------------------------------------------------------------------------------------
| Id? | Operation??????????? | Name?????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? |??????????????? |??? 51 |? 3366 |?? 110?? (1)| 00:00:02 |
|*? 1 |INDEX FAST FULL SCAN| IND_OBJECTNAME |??? 51 |? 3366 |?? 110?? (1)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_NAME" IS NOT NULL AND
UPPER("T"."OBJECT_NAME")||'AAA'='TAAA')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_1gu36rnh3s2a318b0c28f" used for this statement
統(tǒng)計(jì)信息
----------------------------------------------------------
28? recursive calls
44? db block gets
505? consistent gets
6? physical reads
15544? redo size
349? bytes sent via SQL*Net to client
359? bytes received via SQL*Net from client
2? SQL*Net roundtrips to/from client
0? sorts (memory)
0? sorts (disk)
1? rows processed
SQL>
2.4實(shí)驗(yàn)結(jié)論
COUNT()函數(shù)是Oracle中的聚合函數(shù)，用于統(tǒng)計(jì)結(jié)果集的行數(shù)。其語(yǔ)法形式如下所示：
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]
COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function.
If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.
If you specify expr, then COUNT returns the number of rows where expr is not null.You can count either all rows, or only distinct values of expr.
If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls.COUNT never returns null.
我們把COUNT的使用情況分為以下3類(lèi)：
①COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)、COUNT(非空列)
②COUNT(允許為空列)
③COUNT(DISTINCT列名)
下面分別從查詢結(jié)果和效率方面做個(gè)比較：
（一）結(jié)果區(qū)別
①COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主鍵)、COUNT(非空列)這幾種方式統(tǒng)計(jì)的行數(shù)是表中所有存在的行的總數(shù)，包括值為NULL的行和非空行。所以，這幾種方式的執(zhí)行結(jié)果相同。這里的常量可以為數(shù)字或字符串，例如，COUNT(2)、COUNT(333)、COUNT('x')、COUNT('xiaomaimiao')。需要注意的是：這里的COUNT(1)中的“1”并不表示表中的第一列，它其實(shí)是一個(gè)表達(dá)式，可以換成任意數(shù)字或字符或表達(dá)式。
②COUNT(允許為空列)這種方式統(tǒng)計(jì)的行數(shù)不會(huì)包括字段值為NULL的行。
③COUNT(DISTINCT列名)得到的結(jié)果是除去值為NULL和重復(fù)數(shù)據(jù)后的結(jié)果。
④“SELECT COUNT(''),COUNT(NULL) FROM T_COUNT_LHR;”返回0行。
（二）效率、索引
①如果存在主鍵或非空列上的索引，那么COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主鍵)、COUNT(非空列)會(huì)首先選擇主鍵上的索引快速全掃描（INDEX FAST FULL SCAN）。若主鍵不存在則會(huì)選擇非空列上的索引。若非空列上沒(méi)有索引則肯定走全表掃描（TABLE ACCESS FULL）。其中，COUNT(ROWID)在走索引的時(shí)候比其它幾種方式要慢。通過(guò)10053事件可以看到這幾種方式除了COUNT(ROWID)之外，其它最終都會(huì)轉(zhuǎn)換成COUNT(*)的方式來(lái)執(zhí)行。
②對(duì)于COUNT(COL1)來(lái)說(shuō)，只要列字段上有索引則會(huì)選擇索引快速全掃描（INDEX FAST FULL SCAN）。而對(duì)于“SELECT COL1”來(lái)說(shuō)，除非列上有NOT NULL約束，否則執(zhí)行計(jì)劃會(huì)選擇全表掃描。
③COUNT(DISTINCT列名)若列上有索引，且有非空約束或在WHERE子句中使用IS NOT NULL，則會(huì)選擇索引快速全掃描。其余情況選擇全表掃描。
2.5關(guān)于COUNT的優(yōu)化
統(tǒng)計(jì)記錄條數(shù)，如何才能最快？xb_audit_ddl_lhr表有2303262記錄。
（一）全表掃描：
LHR@orclasm > set line 9999
LHR@orclasm > set autot on
LHR@orclasm > set timing on
LHR@orclasm > set time on
12:34:01 LHR@orclasm >
12:35:20 LHR@orclasm > SELECT /*+full(t)*/ COUNT(*) FROM xb_audit_ddl_lhr t;
COUNT(*)
----------
2303262
Elapsed:00:01:16.53
Execution Plan
----------------------------------------------------------
Plan hash value: 3725780224
-------------------------------------------------------------------------------------------------
| Id? | Operation??????????? | Name???????????? | Rows? | Cost (%CPU)| Time???? | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? |????????????????? |???? 1 | 26629?? (1)| 00:05:20 |?????? |?????? |
|?? 1 |? SORT AGGREGATE????? |????????????????? |???? 1 |??????????? |????????? |?????? |?????? |
|?? 2 |?? PARTITION RANGE ALL|????????????????? |? 2247K| 26629?? (1)| 00:05:20 |???? 1 |1048575|
|?? 3 |??? PARTITION HASH ALL|????????????????? |? 2247K| 26629?? (1)| 00:05:20 |???? 1 |???? 5 |
|?? 4 |???? TABLE ACCESS FULL| XB_AUDIT_DDL_LHR |? 2247K| 26629?? (1)| 00:05:20 |???? 1 |1048575|
-------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1? recursive calls
57? db block gets
92274? consistent gets
92012? physical reads
0? redo size
529? bytes sent via SQL*Net to client
519? bytes received via SQL*Net from client
2? SQL*Net roundtrips to/from client
0? sorts (memory)
0? sorts (disk)
1? rows processed
12:36:47 LHR@orclasm >
（二）主鍵索引
12:38:31 LHR@orclasm > SELECT /*+index(SYS_C0011358)*/ COUNT(*) FROM xb_audit_ddl_lhr t;
COUNT(*)
----------
2303262
Elapsed:00:00:00.24
Execution Plan
----------------------------------------------------------
Plan hash value: 2548021478
------------------------------------------------------------------------------
| Id? | Operation???????????? | Name???????? | Rows? | Cost (%CPU)| Time???? |
------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT????? |????????????? |???? 1 |? 3114?? (1)| 00:00:38 |
|?? 1 |? SORT AGGREGATE?????? |????????????? |???? 1 |??????????? |????????? |
|?? 2 |?? INDEX FAST FULL SCAN| SYS_C0011358 |? 2247K|? 3114?? (1)| 00:00:38 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0? recursive calls
2? db block gets
12283? consistent gets
12220? physical reads
0? redo size
529? bytes sent via SQL*Net to client
519? bytes received via SQL*Net from client
2? SQL*Net roundtrips to/from client
0? sorts (memory)
0? sorts (disk)
1? rows processed
（三）位圖索引
CREATE BITMAP INDEX IND_xb_operation? ON xb_audit_ddl_lhr(operation) local;
12:41:44 LHR@orclasm > SELECT /*+index(IND_xb_operation)*/ COUNT(*) FROM xb_audit_ddl_lhr t;
COUNT(*)
----------
2303262
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1101410508
------------------------------------------------------------------------------------------------------------
| Id? | Operation?????????????????????? | Name???????????? | Rows? | Cost (%CPU)| Time???? | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??????????????? |????????????????? |???? 1 |?? 114?? (0)| 00:00:02 |?????? |?????? |
|?? 1 |? SORT AGGREGATE???????????????? |????????????????? |???? 1 |??????????? |????????? |?????? |?????? |
|?? 2 |?? PARTITION RANGE ALL?????????? |????????????????? |? 2247K|?? 114?? (0)| 00:00:02 |???? 1 |1048575|
|?? 3 |??? PARTITION HASH ALL?????????? |????????????????? |? 2247K|?? 114?? (0)| 00:00:02 |???? 1 |???? 5 |
|?? 4 |???? BITMAP CONVERSION COUNT???? |????????????????? |? 2247K|?? 114?? (0)| 00:00:02 |?????? |?????? |
|?? 5 |????? BITMAP INDEX FAST FULL SCAN| IND_XB_OPERATION |?????? |??????????? |????????? |???? 1 |1048575|
------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0? recursive calls
0? db block gets
312? consistent gets
0? physical reads
0? redo size
529? bytes sent via SQL*Net to client
519? bytes received via SQL*Net from client
2? SQL*Net roundtrips to/from client
0? sorts (memory)
0? sorts (disk)
1? rows processed
在數(shù)據(jù)量超大的表上，還可以采用位圖索引并行的方式。優(yōu)化無(wú)止境，根據(jù)場(chǎng)景選擇最適合的才是最好的。
第三章參考文章
3.1MOS
3.1.1How the Oracle CBO Chooses a Path for the SELECT COUNT(*) Command (文檔ID 124717.1)
Purpose:========The purpose of this article is to explain how the Oracle Cost Based Optimizer (CBO) evaluates the best path to do a SELECT COUNT(*) or aSELECT COLUMN(column) without using a WHERE clause. Scope & Application:====================This article is meant for all DBAs and anyone involved in SQL Tuning.How the Oracle CBO Chooses a Path for the SELECT COUNT(*) Command:==================================================================CBO is invoked when:? o OPTIMIZER_MODE = CHOOSE, or OPTIMIZER_GOAL = CHOOSE--AND--? o At least one of the objects of the select statement has? ? been analyzed.Here some examples of select count() without any where clause: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;* Table journal_entries has been analyzed.* id_je is a column with a NOT NULL constraint.* DATE_JE is a column with a NOT NULL constraint.* balanced is a column without a NOT NULL constraint.* There are four indexes on table journal_entries:? . NDX_ECR_ID_JE on column id_je? . NDX_ECR_DATE_JE_BALANCED on columns date_je, balanced? . NDX_ECR_BALANCED_DATE_JE on columns balanced, date_je? . NDX_ECR_BALANCED on column balancedI. Sel1 and Sel2:-----------------For CBO, Sel1 and Sel2 are strictly equivalent: 1. CBO looks at the table, retrieves statistics, and calculates the? cost for a full table scan.2. CBO then looks for all indexes on the table.? In this example it? ? lists four indexes.3. If CBO finds one or more indices which index a column NOT NULL? ? or index at least one column NOT NULL, it evaluates the cost of? the path for each of these indices.? In our example CBO evaluates three indices:? - NDX_ECR_ID_JE? - NDX_ECR_DATE_JE_BALANCED? - NDX_ECR_BALANCED_DATE_JE4. CBO chooses the path with the lower cost.? If an index has the lowest? ? cost, it is chosen, whatever columns are indexed. II. Sel3:---------For Sel3, CBO does the same as for Sel1 and Sel2 since "id_je" has aNOT NULL constraint.If the column in the count() is NOT NULL, CBO considers the selectstrictly equivalent to a select count(*) or a select count(constant).III. Sel4:----------For Sel4, the column in the count() function can have a NULL value.So, what happens?- CBO will not evaluate index NDX_ECR_BALANCED since "balanced" has no? NOT NULL constraint.? If you are sure that NDX_ECR_BALANCED is more? optimal than performing a full table scan on journal_entries, you must? rewrite your query from select count(balanced) from journal_entries to:? ? select count(*) from journal_entries? ? where balanced > " " or 0 (depending on column type)? CBO will then use the index.? Even if you write your select with a hint:? ? ? ? ? /*+ INDEX (JOURNAL_ENTRIES NDX_ECR_BALANCED) */? CBO will not use it as it is not evaluated.- CBO will not evaluate index NDX_ECR_ID_JE because BALANCED is not present? in this index.- CBO evaluates indexes NDX_ECR_DATE_JE_BALANCED and NDX_ECR_BALANCED_DATE_JE? since these indexes contain a column (DATE_JE) which has a NOT NULL? constraint.? If the cost of one of these two indexes is less than the? cost of a full table scan, CBO uses this index.? CBO does not take care? of the position of column balanced in the index.References:===========Note:67522.1Why is my index not used?
3.2博客
3.2.1asmtom
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245
You Asked
What is the difference between count(1) and count(*) in a sql query
eg.
select count(1) from emp;
and
select count(*) from emp;
and we said...
nothing, they are the same, incur the same amount of work -- do the same thing, take the same amount of resources.
You can see this via:
ops$tkyte@ORA817.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA817.US.ORACLE.COM> select count(*) from all_objects;
COUNT(*)
----------
27044
ops$tkyte@ORA817.US.ORACLE.COM> select count(1) from all_objects
2 /
COUNT(1)
----------
27044
and the tkprof will show:
select count(*)
from
all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.56 5.56 0 234998 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.58 5.58 0 234998 4 1
select count(1)
from
all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.46 5.47 0 234998 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.48 5.49 0 234998 4 1
Same number of blocks read/written/processed, same cpu times (basically) same elapsed times (basically).
they are identical.
Anyone who thinks different (and I know you are out there) will have to post a test case like the above or some scientific proof otherwise to be taken seriously....
-------------------------------------------------------------------------
About Me
...............................................................................................................................
●本文作者：小麥苗，只專(zhuān)注于數(shù)據(jù)庫(kù)的技術(shù)，更注重技術(shù)的運(yùn)用
●本文在itpub（http://blog.itpub.net/26736162）、博客園（http://www.cnblogs.com/lhrbest）和個(gè)人微信公眾號(hào)（xiaomaimiaolhr）上有同步更新
●本文itpub地址：http://blog.itpub.net/26736162/viewspace-2136339/
●本文博客園地址：http://www.cnblogs.com/lhrbest/p/6645603.html
●本文pdf版及小麥苗云盤(pán)地址：http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群：230161599微信群：私聊
●聯(lián)系我請(qǐng)加QQ好友(646634621)，注明添加緣由
●于2017-03-28 09:00 ~ 2017-03-30 22:00在魔都完成
●文章內(nèi)容來(lái)源于小麥苗的學(xué)習(xí)筆記，部分整理自網(wǎng)絡(luò)，若有侵權(quán)或不當(dāng)之處還請(qǐng)諒解
●版權(quán)所有，歡迎分享本文，轉(zhuǎn)載請(qǐng)保留出處
...............................................................................................................................
拿起手機(jī)使用微信客戶端掃描下邊的左邊圖片來(lái)關(guān)注小麥苗的微信公眾號(hào)：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學(xué)習(xí)最實(shí)用的數(shù)據(jù)庫(kù)技術(shù)。
