索引系列四--索引特性之存列值優(yōu)化count

--要領(lǐng)：只要索引能回答問題，索引就可以當(dāng)成一個(gè)"瘦表"，訪問路徑就會減少。另外切記不存儲空值

成都創(chuàng)新互聯(lián)是一家專業(yè)從事成都做網(wǎng)站、成都網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司。作為專業(yè)網(wǎng)站建設(shè)公司,成都創(chuàng)新互聯(lián)依托的技術(shù)實(shí)力、以及多年的網(wǎng)站運(yùn)營經(jīng)驗(yàn),為您提供專業(yè)的成都網(wǎng)站建設(shè)、營銷型網(wǎng)站及網(wǎng)站設(shè)計(jì)開發(fā)服務(wù)！

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;

執(zhí)行計(jì)劃

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

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

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

| 1 | SORT AGGREGATE | | 1 | | |

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

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

統(tǒng)計(jì)信息

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

0 recursive calls

0 db block gets

1048 consistent gets

--為啥用不到索引，因?yàn)樗饕荒艽鎯罩?，所以加上一個(gè)is not null,再試驗(yàn)看看

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

執(zhí)行計(jì)劃

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

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

| 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 |

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

統(tǒng)計(jì)信息

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

0 recursive calls

0 db block gets

170 consistent gets

--也可以不加is not null，直接把列的屬性設(shè)置為not null，也成，繼續(xù)試驗(yàn)如下：

alter table t modify OBJECT_ID not null;

select count(*) from t;

執(zhí)行計(jì)劃

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

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

| 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 |

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

統(tǒng)計(jì)信息

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

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;

執(zhí)行計(jì)劃

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

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

| 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 |

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

統(tǒng)計(jì)信息

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

0 recursive calls

0 db block gets

160 consistent gets

文章標(biāo)題：索引系列四--索引特性之存列值優(yōu)化count
分享網(wǎng)址：http://www.muchs.cn/article46/ishihg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、響應(yīng)式網(wǎng)站、網(wǎng)站維護(hù)、商城網(wǎng)站、Google、服務(wù)器托管

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容