索引系列四 --索引特性之存列值優化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;

執行計劃

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

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

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

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

執行計劃

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

| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

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

執行計劃

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

| Id  | Operation             | Name           | Rows  | Cost (%CPU)| Time     |

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

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

執行計劃

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

| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |

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

|   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