索引系列四--索引特性之存列值优化count

--要领：只要索引能回答问题，索引就可以当成一个"瘦表"，访问路径就会减少。另外切记不存储空值

成都创新互联是一家专业从事成都做网站、成都网站建设的网络公司。作为专业网站建设公司,成都创新互联依托的技术实力、以及多年的网站运营经验,为您提供专业的成都网站建设、营销型网站及网站设计开发服务！

drop table t purge;

create table t as select * from dba_objects;

update t set object_id=rownum;

commit;

create index idx1_object_id on t(object_id);

set autotrace on

select count(*) from t;

执行计划

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

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

| 0 | SELECT STATEMENT | | 1 | 292 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 69485 | 292 (1)| 00:00:04 |

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

统计信息

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

0 recursive calls

0 db block gets

1048 consistent gets

--为啥用不到索引，因为索引不能存储空值，所以加上一个is not null,再试验看看

select count(*) from t where object_id is not null;

执行计划

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 50 (2)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 69485 | 882K| 50 (2)| 00:00:01 |

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

统计信息

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

0 recursive calls

0 db block gets

170 consistent gets

--也可以不加is not null，直接把列的属性设置为not null，也成，继续试验如下：

alter table t modify OBJECT_ID not null;

select count(*) from t;

执行计划

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

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

| 0 | SELECT STATEMENT | | 1 | 49 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 69485 | 49 (0)| 00:00:01 |

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

统计信息

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

0 recursive calls

0 db block gets

170 consistent gets

0 physical reads

0 redo size

425 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

--如果是主键就无需定义列是否允许为空了。

drop table t purge;

create table t as select * from dba_objects;

update t set object_id=rownum;

alter table t add constraint pk1_object_id primary key (OBJECT_ID);

set autotrace on

select count(*) from t;

执行计划

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

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

| 0 | SELECT STATEMENT | | 1 | 46 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| PK1_OBJECT_ID | 69485 | 46 (0)| 00:00:01 |

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

统计信息

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

0 recursive calls

0 db block gets

160 consistent gets

文章标题：索引系列四--索引特性之存列值优化count
分享网址：https://www.cdcxhl.com/article46/ishihg.html

成都网站建设公司_创新互联，为您提供微信小程序、响应式网站、网站维护、商城网站、Google、服务器托管

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容