[20180727]再论count(*)和count(1).txt
--//这是一个古老的话题,最近在看exadata方面的书,自己在重新探究看看.
1.环境
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
2.测试:
SCOTT@test01p> select count(*) from emp;
COUNT(*)
----------
14
SCOTT@test01p> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp
Plan hash value: 2937609675
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
--//可以发现走主键索引,做INDEX FULL SCAN.注意看Column Projection Information .
--//实际上并没有访问任何字段.
SCOTT@test01p> select /*+ full(emp) */ count(*) from emp;
COUNT(1)
----------
14
SCOTT@test01p> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c6w9w9vn6ssq8, child number 0
-------------------------------------
select /*+ full(emp) */ count(*) from emp
Plan hash value: 2083865914
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (rowset=200)
--//注意看projection,可以发现实际上没有访问的字段.
SCOTT@test01p> select /*+ full(emp) */ count(1) from emp;
COUNT(1)
----------
14
SCOTT@test01p> @ dpc '' advanced
...
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (rowset=200)
--//一样没有访问字段.
SCOTT@test01p> @ desc emp;
Name Null? Type
--------- -------- ----------------------
EMPNO NOT NULL NUMBER(4)
ENAME NOT NULL VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SCOTT@test01p> select count(comm) from emp;
COUNT(COMM)
-----------
4
SCOTT@test01p> @ dpc '' advanced
....
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("COMM")[22]
2 - (rowset=200) "COMM"[NUMBER,22]A
--//可以发现count(comm)仅仅记数comm非NULL的记录.仔细看projection:
--//会访问comm字段.如果查询ename 非空字段.
SCOTT@test01p> select count(ENAME) from emp;
COUNT(ENAME)
------------
14
SCOTT@test01p> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7ad4kajqqsghj, child number 0
-------------------------------------
select count(ENAME) from emp
Plan hash value: 2937609675
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------
....
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
--//ename 字段定义not null,oracle直接走主键索引,因为没有其它更小的非空字段索引.
--//一样没有访问ename字段,实际上都变成了count(*)
--//换一句话讲 执行count(*) 与 count(1) 效果一样的.
3.可以通过10053验证:
--//可以通过10053跟踪也可以发现实际上转换为count(*)的查询.
SCOTT@test01p> @ 10053on 12
Session altered.
SCOTT@test01p> Select count(ENAME) from emp;
COUNT(ENAME)
------------
14
--//注意:要产生1次硬分析才能收集到信息.
SCOTT@test01p> @ 10053off
Session altered.
--//检查跟踪文件内容:
...
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: Converting COUNT(ENAME) to COUNT(*).
CNT: COUNT() to COUNT(*) done.
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
....
Stmt: ******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(ENAME)" FROM "SCOTT"."EMP" "EMP"
Objects referenced in the statement
EMP[EMP] 92287, type = 1
Objects in the hash table
Hash table Object 92287, type = 1, ownerid = 14677601663756975076:
No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(ENAME)" FROM "SCOTT"."EMP" "EMP"
kkoqbc: optimizing query block SEL$1 (#0)
--//可以发现只要查询not null字段,全部转换为count(*).
4.为什么重论这个问题:
--//实际上今天在看exadata书时遇到一个情况,我们生产系统有一张大表,大约210G.存在大量的行迁移.
--//如果简单查询走直接路径读,在大量行迁移的情况下,exadata无法充分发现存储服务器的功能,拿来验证看看.
--//全部转换为块的形式传输到数据库.
--//如果我查询
select /*+ full(a) */ count(*) from bigtable a ;
--//大约90秒.
--//如果查询:
select /*+ full(a) */ count(x) from bigtable ;
--//我发现查询时间是一样的,开始感觉奇怪.仔细检查才发现我查询时X字段实际上是非空字段.当我在换成包含null值的字段.
--//发现是一场灾难!!最后竟然报ora-01555错误.4个多小时都没有查询出来,下个星期再重复演示.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2168668/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2168668/