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


    看见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。

  • 相关阅读:
    Rancher 2.1平台搭建及使用
    回归博客园
    CGI与FastCGI
    [转]1小时内打造你自己的PHP MVC框架
    MySQL学习随笔--通过实例理解merge ,temptable算法的差异
    MySQL学习随笔--视图
    使用onenote写博客园的方法
    手动配置wamp环境(1)--apache安装与基本操作
    文档兼容性定义,使ie按指定的版本解析
    JavaScript线程
  • 原文地址:https://www.cnblogs.com/hehe520/p/6330648.html
Copyright © 2020-2023  润新知