[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个多小时都没有查询出来,下个星期再重复演示.