[20180727]再论count(*)和count(1).txt

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