关于count

来源：互联网发布：ubuntu 软件安装位置编辑：程序博客网时间：2024/05/19 15:23

关于count(*)的执行计划的分析
一． RULE优化器（RBO）

1，表上无索引

SQL> create table test01 as select * from dba_objects;Table createdSQL> select count(*) from test01;已用时间:  00: 00: 00.05Execution 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.03Execution 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 createdSQL> select count(*) from test01;已用时间:  00: 00: 00.02Execution 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.01Execution 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 deletedSQL> commit;Commit complete

a，非主键索引

SQL> drop index ind_test01;Index droppedSQL> create index ind_test01 on test01(object_id);Index createdSQL> select count(*) from test01;已用时间:  00: 00: 00.03Execution 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.01Execution 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 droppedSQL> alter table test01  2  add constraint pk_test01 primary key (object_id);Table alteredSQL> 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.05Execution 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 createdSQL> analyze table test02 compute statistics;Table analyzedSQL> select count(*) from test02;已用时间:  00: 00: 00.05Execution 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.02Execution 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 deletedSQL> commit;Commit complete

c，非主键索引

SQL> drop index ind_test02;Index droppedSQL> create index ind_test02 on test02(object_id);Index createdSQL> analyze table test02 compute statistics;Table analyzedSQL> select count(*) from test02;已用时间:  00: 00: 00.03Execution 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.01Execution 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 droppedSQL> alter table test02  2  add constraint pk_test02primary key (object_id);Table alteredSQL> analyze table test02 compute statistics;Table analyzedSQL> 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 CSQL> 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属性的列

0 0