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

上一篇 / 下一篇 2008-03-24 09:59:57 / 个人分类：pl/sql

测试如下:不加的会走全表扫描要是数据量大的话有可能结果需要很长时间才出来

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 24 10:00:23 2008

ITPUB个人空间q#}#p3HH0H
Connected to:ITPUB个人空间;}'U7D;^*v X6{
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionITPUB个人空间*?0Fn{*yI|
With the Partitioning option
f!^|S$N-nYU#}#@tv0JServer Release 9.2.0.4.0 - Production

SQL> set autot trace exp
/v(J3W\%d6j0SQL> select count(*) from tb_TEST

Execution Plan
h3}&hc I5@0----------------------------------------------------------
5a4qa#Uy0   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=22978 Card=1)
'`Xb E3jOW0   1    0   SORT (AGGREGATE)ITPUB个人空间`v5a+Q0B6E
   2    1     PARTITION RANGE (ALL)ITPUB个人空间:g1xx2X9f+[
   3    2       TABLE ACCESS (FULL) OF 'TB_TEST (CoITPUB个人空间T tn6~ p ^9Q
          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
$oS B;W/Z0----------------------------------------------------------ITPUB个人空间5Q+eT8Y KW
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1)ITPUB个人空间C(fK#[E7[-{5P0E
   1    0   SORT (AGGREGATE)
*hQA/kB'TE0   2    1     INDEX (FAST FULL SCAN) OF 'PK_TB_TEST1' (UNIQUE) (Cost=3ITPUB个人空间{0Hqj:i*b
           Card=39590341)

查询结果也很快

SQL> set timing on
`E dLRg0SQL> select count(*) from tb_test;

COUNT(*)
)A:Q/O9Ga0----------
\tm&De1p&P0 39590341

Elapsed: 00:00:02.08