count(*), count(1) ,count(唯一键索引),count(非唯一键索引),count(存在null索引)的效率

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/robinson1988/article/details/4985617

看见pub上面有人问count(*),count(1),count(索引字段)有什么区别,效率是否相同,恩 是个好问题下面我来研究研究

SQL> create table test as select * from dba_objects;

表已创建。

SQL> alter table test modify object_id primary key;

表已更改。

SQL> set autot trace
SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select count(1) from test;
执行计划
----------------------------------------------------------
Plan hash value: 33512955

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    25   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C005230 | 56783 |    25   (4)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
        326  recursive calls
          0  db block gets
        236  consistent gets
        620  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 33512955

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    25   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C005230 | 56783 |    25   (4)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
       1112  recursive calls
          0  db block gets
        353  consistent gets
        632  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          1  rows processed

上面的测试清空了缓存,现在不清空缓存

SQL> select count(1) from test;
执行计划
----------------------------------------------------------
Plan hash value: 33512955

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    25   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C005230 | 56783 |    25   (4)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        110  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  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(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 33512955

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    25   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C005230 | 56783 |    25   (4)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        110  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

现在将表的主键去掉

SQL> alter table test drop  primary key ;

表已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter system flush buffer_cache;

系统已更改。

SQL> select count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 1950795681

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

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
        274  recursive calls
          0  db block gets
        795  consistent gets
        699  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter system flush buffer_cache;

系统已更改。

SQL> select count(1) from test;
执行计划
----------------------------------------------------------
Plan hash value: 1950795681

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

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
        282  recursive calls
          0  db block gets
        797  consistent gets
        701  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

现在不刷新缓存,多运行几次

SQL> select count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 1950795681

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

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        691  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  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 test;
执行计划
----------------------------------------------------------
Plan hash value: 1950795681

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

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        691  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
存在主键的时候,从统计信息上看,第一次硬解析的时候count(1)效率比count(*)高一点点,但是一旦硬解析之后,再次执行他们的效率是相同的,在没有主键的时候,从统计信息上看,第一次硬解析的时候count(*)的效率比count(1)高点点,再次执行的时候,他们的效率是相同的。由此可以说count(*),count(1)他们的效率是相同的。

现在来测试一下count(索引字段),这个时候分两种情况

1.该字段中有null

2.该字段无null

 

 

第一种情况(该字段有null):

SQL> update test set object_name=null where owner='SCOTT';

已更新13行。

SQL> commit;

提交完成。

SQL> create index ind_on on test(object_name);

索引已创建。

SQL> select count(*) from test;

  COUNT(*)
----------
     49916

SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select count(object_name) from test;

COUNT(OBJECT_NAME)
------------------
             49903
执行计划
----------------------------------------------------------
Plan hash value: 1496018862

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    66 |    53   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |    66 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_ON | 56783 |  3659K|    53   (2)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
        990  recursive calls
          0  db block gets
        472  consistent gets
        766  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(object_name) from test;

COUNT(OBJECT_NAME)
------------------
             49903
执行计划
----------------------------------------------------------
Plan hash value: 1496018862

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    66 |    53   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |    66 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_ON | 56783 |  3659K|    53   (2)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        253  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

第二种情况(该字段无null值)

SQL> update test set object_name='DEPT' where owner='SCOTT';

已更新13行。

SQL> commit;

提交完成。

SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select count(object_name) from test;

COUNT(OBJECT_NAME)
------------------
             49916
执行计划
----------------------------------------------------------
Plan hash value: 1496018862

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    66 |    53   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |    66 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_ON | 56783 |  3659K|    53   (2)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
        990  recursive calls
          0  db block gets
        472  consistent gets
        759  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(object_name) from test;

COUNT(OBJECT_NAME)
------------------
             49916
执行计划
----------------------------------------------------------
Plan hash value: 1496018862

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    66 |    53   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |    66 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_ON | 56783 |  3659K|    53   (2)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        253  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

在硬解析的时候,从统计信息上看,有null值时count(索引字段)的花费要比没有null值时高,而软解析的时候他们的效率是一样的。另外需要注意的就是count(索引字段),存在null值的时候,count不会统计null值,因为ORACLE不会对索引列上包含null值的行进行索引,这点需要特别注意。

下面比较count(唯一键索引)与非唯一键索引的效率

SQL> create unique index ind_oid on test(object_id);

索引已创建。

SQL> set autot on
SQL> select count(object_id) from test;

COUNT(OBJECT_ID)
----------------
           49916
执行计划
----------------------------------------------------------
Plan hash value: 886325522

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    13 |    25   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_OID | 56783 |   720K|    25   (4)| 00:00:01 |
---------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        187  consistent gets
        109  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> drop index ind_oid;

索引已删除。

SQL> create index ind_oid on test(object_id);

索引已创建。

SQL> select count(object_id) from test;

COUNT(OBJECT_ID)
----------------
           49916
执行计划
----------------------------------------------------------
Plan hash value: 886325522

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    13 |    26   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_OID | 56783 |   720K|    26   (4)| 00:00:01 |
---------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
         36  recursive calls
          0  db block gets
        197  consistent gets
        110  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

由此可见count(唯一键索引的效率)比count(非唯一键)效率高。

 

总结:

当表存在主键或者唯一索引的时候count(*),count(1)效率相同。count(唯一键索引字段)比count(非唯一键索引字段)效率高。

count(非唯一键索引,列存在null)与count(非唯一键索引,列不存在null)的效率差不多,不过count(唯一键索引,列存在null)的值比count(*)小,因为null的值不会被记录在索引中。

注意:我这里所指的效率主要是指的cost,经过多次执行后的cost。

 

 

 

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页