Oracle索引妙用之部分记录

drop table t purge;
set autotrace off
create table t (id int ,status varchar2(2));
--建立普通索引
create index id_normal on t(status);
insert into t select rownum ,'Y' from dual connect by rownum<=1000000;
insert into t select 1 ,'N' from dual;
commit;
analyze table t compute statistics for table for all indexes for all indexed columns;

set linesize 1000
set autotrace traceonly
select * from t where status='N';
执行计划
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |     1 |    10 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_NORMAL |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
   2 - access("STATUS"='N')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        483  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
          
--看索引情况
set autotrace off
analyze index id_normal validate structure;
select name,btree_space,lf_rows,height from index_stats;
 
NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
ID_NORMAL                         22960352    1000001          3




--建函数索引
drop index id_normal;
create index id_status on  t (Case when status= 'N' then 'N' end);
analyze table t compute statistics for table for all indexes for all indexed columns;
/*以下这个select * from t where (case when status='N' then 'N' end)='N'


写法不能变,如果是select * from t where status='N'将无效!我见过有些人设置了选择性索引，却这样调用的，结果根本起不到任何效果！
*/


set autotrace traceonly
select * from t where (case when status='N' then 'N' end)='N';
执行计划
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_STATUS |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
   2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        479  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


--接着观察id_status（即函数索引）索引的情况
set autotrace off
analyze index id_status validate structure;
select name,btree_space,lf_rows,height from index_stats;


NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
ID_STATUS                          8000          1          1