count(*)在平常工作中，使用到的频率很高，是否会走索引，对性能影响不小！但是不是所有的count(*)都能走索引!小记下
create table t3
(
sid number not null primary key,
sno number,
sname varchar2(10)
)
tablespace test;
declare
        maxrecords constant int:=100000;
        i int :=1;
    begin
        for i in 1..maxrecords loop
          insert into t3 values(i,i,'ocpyang');
        end loop;
    dbms_output.put_line(' 成功录入数据！ ');
    commit;
    end;
/
declare
        maxrecords constant int:=200000;
        i int :=100001;
    begin
        for i in 100001..maxrecords loop
          insert into t3(sid,sname) values(i,'ocpyang');
        end loop;
    dbms_output.put_line(' 成功录入数据！ ');
    commit;
    end;
/
create index index_sno on t3(sno);
exec dbms_stats.gather_table_stats('SYS','T3',cascade=>TRUE);
***********
1.count
***********
SQL> set autotrace traceonly explain stat;
SQL> select count(*) from t3;
执行计划
----------------------------------------------------------
Plan hash value: 463314188
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T3   |    82 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
   - SQL plan baseline "SQL_PLAN_27gnhfjz9qahj14fae16c" used for this statement
统计信息
----------------------------------------------------------
         55  recursive calls
         38  db block gets
        521  consistent gets
         19  physical reads
      14676  redo size
        527  bytes sent via SQL*Net to client
        520  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 t1 where sid is not null;
执行计划
----------------------------------------------------------
Plan hash value: 1551730033
--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |    13 |    68   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C0023596 | 85899 |  1090K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline "SQL_PLAN_4xztry6akgpqqf2d247c8" used for this statement
统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        310  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--通过索引实现的.