麻袋爸爸

不懂利用选择性索引提高效率

数据库的设计是很灵活多变的，要有跳出常规思维的想法。结算组有个兄弟问我：“能不能只对某个字段的某些特定的值建索引？”问这话值得表扬，因为他会思考，认为这样的索引要比普通索引“个头”来的小一点，所以会更块！
确实有只对列的某些值建索引的方法，测试如下：

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as ljb
SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test (id int ,status varchar2(2));
Table created

这里表示只对status列的值为N的情况建索引

SQL> create index id_status on  ljb_test (Case when status= 'N' then 'N' end);
Index created
插入很多为Y的值
SQL> insert into ljb_test select rownum ,'Y' from dual connect by rownum<=100000;
100000 rows inserted
插入单条值为N的记录
SQL> insert into ljb_test select 1 ,'N' from dual;
1 row inserted
SQL> commit;
Commit complete

分析的目的是为了让查询在基于COST模式下，因为只为字段的部分值建索引本质是函数索引，要在COST模式下才生效。

SQL> analyze table ljb_test compute statistics for table for all indexes for all indexed columns;
Table analyzed
以下这个select * from ljb_test where (case when status='N' then 'N' end)='N’写法不能变,如果是select * from ljb_test where status='N'将无效!我见过有些人设置了选择性索引，却这样调用的，结果根本起不到任何效果！
SQL> explain plan for  select * from ljb_test where (case when status='N' then 'N' end)='N';
Explained

可以看出来代价还是比较低的，效果不错！

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3317130416
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     2   (0)| 0
|   1 |  TABLE ACCESS BY INDEX ROWID| ljb_test   |     1 |    10 |     2   (0)| 0
|*  2 |   INDEX RANGE SCAN          | ID_STATUS |     1 |       |     1   (0)| 0
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')
14 rows selected

接着删除掉选择性索引，建普通索引比较一下效果

SQL> drop index id_status;
Index dropped
SQL> create index id_normal on ljb_test(status);
Index created
SQL> explain plan for  select * from ljb_test where status='N';
Explained

发现同样是走了索引，但是代价却高出许多！

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2648582318
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  1000 | 10000 |    40   (0)| 0
|   1 |  TABLE ACCESS BY INDEX ROWID| ljb_test   |  1000 | 10000 |    40   (0)| 0
|*  2 |   INDEX RANGE SCAN          | ID_NORMAL |   400 |       |    91   (0)| 0
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------------------
   2 - access("STATUS"='N')
14 rows selected

不难看两者效率差别巨大，所以在你知道某些字段值的分布,并确定某些字段的特定值比较少，而你的应用却经常查询这些特定的少量值的时候，可以用这个方法, 于是选择性索引就闪亮登场，有了大展身手的机会了！
介绍完了选择性索引，大家能回答为什么更快吗？很多人会说：“这简单，选择性索引只选择少的特定值做索引，那索引块就很小，搜索起来更快！”回答正确！不过你能回答索引块变小是多小，和普通索引差距有多少？能量化为具体值吗？下面我来回答这个问题。

以下是选择性索引id_status建好后，索引的情况

SQL> analyze index id_status validate structure;
Index analyzed
SQL> select name,btree_space,lf_rows,height from index_stats;
NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ------------------------------------------------------
ID_STATUS                             8000           1            1 
接着是看id_normal，普通索引的情况
SQL> select name,btree_space,lf_rows,height from index_stats;
NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- -------------------------------------------------------
ID_NORMAL                          1464032        100001         2

总结：很明显，选择索引的大小为8K，而普通索引的大小为1464K；选择索引的高度为1，而普通索引的的高度为2。差异相当显著！通过本小节应该学会巧妙的将选择性索引应用到的工作中去，会带来意想不到效果！