ITPub博客

首页 > 数据库 > Oracle > 关于count(*)

关于count(*)

原创 Oracle 作者:sunwgneuqsoft 时间:2007-12-01 09:34:53 0 删除 编辑

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

一.RULE优化器(RBO

1, 表上无索引

SQL> create table test01 as select * from dba_objects;

Table created

SQL> select count(*) from test01;

已用时间: 00: 00: 00.05

[@more@]

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.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属性的索引列

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8394333/viewspace-987140/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    56
  • 访问量
    759892