count(*)在平常工作中，使用到的频率很高，是否会走索引，对性能影响不小！但是不是所有的count(*)都能走索引!小记下
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
--通过索引实现的.