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