SELECT COUNT(*) 索引会走 index fast full scan

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:63

SELECT COUNT(*) 索引会走 index fast full scan

测试如下:不加主键的会走全表扫描 要是数据量大的话 有可能结果需要很长时间才出来
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 24 10:00:23 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> set autot trace exp
SQL> select count(*) from tb_TEST
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=22978 Card=1)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (FULL) OF 'TB_TEST (Co
st=22978 Card=39590341)
SQL> alter table TB_test
2 add constraint pk_tb_test1 primary key (ID);
Table altered.
SQL> select count(*) from TB_TEST;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PK_TB_TEST1' (UNIQUE) (Cost=3
Card=39590341)
查询结果也很快
SQL> set timing on
SQL> select count(*) from tb_test;
COUNT(*)
----------
39590341
Elapsed: 00:00:02.08

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】