[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個多小時都沒有查詢出來,下個星期再重複演示.