count(*),count(1),count(col) 区别

count(1)和count(*)都是统计表的总行数，两者执行结果相同。

表上没有主键或者唯一键索引，两者都走全表扫描；
表上有主键或者唯一键索引，那么走主键或者唯一键索引。

count(col)则是统计col列中不为空的总行数,如果该列存在索引，那么自动走索引（INDEX FULL SCAN）；
否则走全表扫描。


验证：
--count(1)和count(*)都是统计表的总行数，两者执行结果相同。
SQL> set autot on
SQL> select count(*) from count_t;

  COUNT(*)
----------
        20

Execution Plan
----------------------------------------------------------
Plan hash value: 711883932

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_ID |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        526  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

SQL> select count(1) from count_t;

  COUNT(1)
----------
        20


Execution Plan
----------------------------------------------------------
Plan hash value: 711883932

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_ID |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        526  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


--count(col)则是统计col列中不为空的总行数。如果该列存在索引，那么自动走索引（INDEX FULL SCAN）；否则走全表扫描。
--没有索引走全表扫描
SQL> select count(name) from count_t;


COUNT(NAME)
-----------
          5

Execution Plan
----------------------------------------------------------
Plan hash value: 1307538749


------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    12 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |    12 |            |          |
|   2 |   TABLE ACCESS FULL| COUNT_T |    20 |   240 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
        132  recursive calls
          0  db block gets
         34  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
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed


--创建索引
SQL> create index ind_name on count_t(name);

Index created.

--有索引走索引
SQL> select count(name) from count_t;

COUNT(NAME)
-----------
          5

Execution Plan
----------------------------------------------------------
Plan hash value: 2640505279

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    12 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |          |     1 |    12 |            |          |
|   2 |   INDEX FULL SCAN| IND_NAME |    20 |   240 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         10  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