Chinaunix首页 | 论坛 | 博客
  • 博客访问: 345882
  • 博文数量: 79
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 42
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-30 12:25
文章分类

全部博文(79)

文章存档

2019年(1)

2017年(19)

2016年(25)

2015年(30)

2014年(4)

分类: Oracle

2017-03-29 12:25:48

=================================
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




**另外附tom说的关于count(*)和count(1)完全相同的回答



You Asked


What is the difference between count(1) and count(*) in a sql query
eg.
select count(1) from emp;
   and
select count(*) from emp;


and we said...

nothing, they are the same, incur the same amount of work -- do the same thing, take the 
same amount of resources.


You can see this via:


ops$tkyte@ORA817.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.


ops$tkyte@ORA817.US.ORACLE.COM> select count(*) from all_objects;

  COUNT(*)
----------
     27044


ops$tkyte@ORA817.US.ORACLE.COM> select count(1) from all_objects
  2  /


  COUNT(1)
----------
     27044

and the tkprof will show:


select count(*)
from
 all_objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.56       5.56          0     234998          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.58       5.58          0     234998          4           1


select count(1)
from
 all_objects




call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.46       5.47          0     234998          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.48       5.49          0     234998          4           1

Same number of blocks read/written/processed, same cpu times (basically) same elapsed 
times (basically).

they are identical.

Anyone who thinks different (and I know you are out there) will have to post a test case 
like the above or some scientific proof otherwise to be taken seriously....
阅读(803) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~