Oraclecount(*)是否走索引

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oraclecount(*)是否走索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3027字，纯文字阅读大概需要5分钟。

内容图文

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

--通过索引实现的.

内容总结

以上是互联网集市为您收集整理的Oraclecount(*)是否走索引全部内容，希望文章能够帮你解决Oraclecount(*)是否走索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---