• 你还在争论 count(*) 与 count(column) 哪个更快？

    目录

    count（*） 和 count（列） 哪个更快？ 版本：12cR2

    1. 构造测试数据

    SQL> create table test as select * from dba_objects;

Table created.

SQL> update test set object_id=rownum;

72825 rows updated.

SQL> commit;

Commit complete.

SQL> set timing on
SQL> set autotrace on;

    2. 对比 count（*） 与 count（列）

    我们每条语句执行两次，以第二次执行结果为准（缓存）

    • COUNT(*)
    SQL> select count(*) from test; （第一次）
SQL> select count(*) from test; （第二次）

  COUNT(*)
----------
     72825

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   387   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| TEST | 79431 |   387   (1)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  5  db block gets
       1433  consistent gets
	  0  physical reads
	  0  redo size
	544  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
    • COUNT(列)
    SQL> select count(object_id) from test; （第一次）
SQL> select count(object_id) from test; （第二次）

COUNT(OBJECT_ID)
----------------
	   72825

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    13 |   387   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |    13 |	       |	  |
|   2 |   TABLE ACCESS FULL| TEST | 79431 |  1008K|   387   (1)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  5  db block gets
       1433  consistent gets
	  0  physical reads
	  0  redo size
	552  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

    观察两条SQL语句执行计划和统计信息，CPU开销都是 387，逻辑读都是 1433，谁快？

    3. 添加索引

    为 object_id 字段添加索引后再次查询对比

    SQL> create index idx_object_id on test(object_id);

Index created.

    4. 二次对比 count（*） 与 count（列）

    • COUNT(*)
    SQL> select count(*) from test; （第一次）
SQL> select count(*) from test; （第二次）

  COUNT(*)
----------
     72825

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   387   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| TEST | 79431 |   387   (1)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  5  db block gets
       1433  consistent gets
	  0  physical reads
	  0  redo size
	544  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
    • COUNT(列)
    SQL> select count(object_id) from test; （第一次）
SQL> select count(object_id) from test; （第二次）

COUNT(OBJECT_ID)
----------------
	   72825

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604

---------------------------------------------------------------------------------------
| Id  | Operation	      | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 	      |     1 |    13 |    46	(0)| 00:00:01 |
|   1 |  SORT AGGREGATE       | 	      |     1 |    13 | 	   |	      |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 79431 |  1008K|    46	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	169  consistent gets
	  0  physical reads
	  0  redo size
	552  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

    创建索引后，观察两条SQL语句执行计划和统计信息，count(*) CPU开销是 387，逻辑读是 1433；count(object_id) CPU开销是 46，逻辑读是 169。谁快？

    5. 添加非空约束

    为 object_id 列添加非空 not null 约束后再次查询对比

    SQL> alter table test modify object_id not null;

Table altered.

Elapsed: 00:00:00.49

    6. 三次对比 count（*） 与 count（列）

    • COUNT(*)
    SQL> select count(*) from test; （第一次）
SQL> select count(*) from test; （第二次）

  COUNT(*)
----------
     72825

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	169  consistent gets
	  0  physical reads
	  0  redo size
	544  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
    • COUNT(列)
    SQL> select count(object_id) from test; （第一次）
SQL> select count(object_id) from test; （第二次）

COUNT(OBJECT_ID)
----------------
	   72825

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	169  consistent gets
	  0  physical reads
	  0  redo size
	552  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

    为 object_id 列添加索引和非空约束后，观察两条SQL语句执行计划和统计信息，CPU开销都是 46，逻辑读都是 169，谁快？

    7. 他们谁更快？

    你走你的阳关道，我过我的独木桥！咱们井水不犯河水，两个东西不等价，谈何对比？
  • 相关阅读:
    hadoop balance
    随笔
    ubuntu server 使用parted分区
    程序员内功续
    hadoop——hdfs多硬盘挂载
    hdfs老数据压缩备份的一些问题20120521
    hadoop balance failed
    hoj 2524 Allocate Dormitories 二分图的最大匹配
    HDOJ 分类（转）
    hoj 3008 Matryoshka Dolls Again 最大独立子集
  • 原文地址：https://www.cnblogs.com/askscuti/p/13746923.html
Copyright © 2020-2023  润新知