[索引相关] 不使用索引的情况 - 2

-- select count(*) 没有使用索引
-- btree索引，优化器不统计条目，而是全表扫描
-- 索引键所在列可能全部允许为null，对于全为null值不创建索引条目
-- 如果索引包含的列中，至少有一个是具有not null约束，则可以使用索引

go@DEMO> create table t (x number,y number); --- x,y都为允许null值

Table created.

go@DEMO> create index t_idx on t(x,y); --- 建立组合索引(x,y)

Index created.

go@DEMO> insert into t values (1,1);

1 row created.

go@DEMO> insert into t values (2,2);

1 row created.

go@DEMO> insert into t values (3,3);

1 row created.

go@DEMO> insert into t values (4,null);

1 row created.

go@DEMO> insert into t values (null,5);

1 row created.

go@DEMO> insert into t values (null,null);

1 row created.

go@DEMO> commit;

Commit complete.

go@DEMO> begin
dbms_stats.gather_table_stats
(user,'T',cascade=>true);
end;
/

PL/SQL procedure successfully completed.

go@DEMO> set autotrace on explain

-- 这里不走索引，而走全表扫描
go@DEMO> select count(*) from t;
  COUNT(*)
----------
         6

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |     6 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------

go@DEMO> analyze index t_idx validate structure;

Index analyzed.

-- 索引条目与实际行数不相等
go@DEMO> select name,lf_rows from index_stats;
NAME                                        LF_ROWS
---------------------------------------- ----------
T_IDX                                             5

1 row selected.