关于count(*)的执行计划的分析

一．RULE优化器（RBO）

1， 表上无索引

SQL> create table test01 as select * from dba_objects;

Table created

SQL> select count(*) from test01;

已用时间: 00: 00: 00.05

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST01'

SQL> select count(*) from test01 where object_id > 0;

已用时间: 00: 00: 00.03

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST01'

此时表上无任何索引，所以无论怎么样都是进行的全表扫描

2， 表上有索引

1， 索引中存在null值

SQL> create index ind_test01 on test01(object_id);

Index created

SQL> select count(*) from test01;

已用时间: 00: 00: 00.02

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST01'

SQL> select count(*) from test01 where object_id > 0;

已用时间: 00: 00: 00.01

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 INDEX (RANGE SCAN) OF 'IND_TEST01' (NON-UNIQUE)

2， 索引中不存在null值

SQL> delete test01 where object_id is null;

4 rows deleted

SQL> commit;

Commit complete

a， 非主键索引

SQL> drop index ind_test01;

Index dropped

SQL> create index ind_test01 on test01(object_id);

Index created

SQL> select count(*) from test01;

已用时间: 00: 00: 00.03

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST01'

SQL> select count(*) from test01 where object_id > 0;

已用时间: 00: 00: 01.01

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 INDEX (RANGE SCAN) OF 'IND_TEST01' (NON-UNIQUE)

b， 主键索引

SQL> drop index ind_test01;

Index dropped

SQL> alter table test01

2 add constraint pk_test01 primary key (object_id);

Table altered

SQL> select count(*) from test01;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST01'

SQL> select count(*) from test01 where object_id > 0;

已用时间: 00: 00: 00.05

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 INDEX (RANGE SCAN) OF 'PK_TEST01' (UNIQUE)

二．COST优化器（CBO）

3， 表上无索引

SQL> create table test02 as select * from dba_objects;

Table created

（略）

此时表上无任何索引，所以无论怎么样都是进行的全表扫描

4， 表上有索引

1， 索引中存在null值

SQL> create index ind_test02 on test02(object_id);

Index created

SQL> analyze table test02 compute statistics;

Table analyzed

SQL> select count(*) from test02;

已用时间: 00: 00: 00.05

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=1)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST02' (Cost=41 Card=30154)

SQL> select count(*) from test02 where object_id > 0;

已用时间: 00: 00: 00.02

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=4)

1 0 SORT (AGGREGATE)

2 1 INDEX (FAST FULL SCAN) OF 'IND_TEST02' (NON-UNIQUE) (Cos

2， 索引中不存在null值

SQL> delete test02 where object_id is null;

4 rows deleted

SQL> commit;

Commit complete

c， 非主键索引

SQL> drop index ind_test02;

Index dropped

SQL> create index ind_test02 on test02(object_id);

Index created

SQL> analyze table test02 compute statistics;

Table analyzed

SQL> select count(*) from test02;

已用时间: 00: 00: 00.03

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=1)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST02' (Cost=41 Card=30150)

SQL> select count(*) from test02 where object_id > 0;

已用时间: 00: 00: 00.01

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=4)

1 0 SORT (AGGREGATE)

2 1 INDEX (FAST FULL SCAN) OF 'IND_TEST02' (NON-UNIQUE) (Cos

d， 主键索引

SQL> drop index ind_test02;

Index dropped

SQL> alter table test02

2 add constraint pk_test02primary key (object_id);

Table altered

SQL> analyze table test02 compute statistics;

Table analyzed

SQL> select count(*) from test02;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1)

1 0 SORT (AGGREGATE)

2 1 INDEX (FAST FULL SCAN) OF 'PK_TEST02' (UNIQUE) (Cost=8 C

SQL> select count(*) from test01 where object_id > 0;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 INDEX (RANGE SCAN) OF 'PK_TEST01' (UNIQUE)

如果单独的没有where条件的select count(*)语句想要用上索引，那么必须满足以下两个条件

1， cbo

2， 存在not null属性的索引列