Oracle 对某列的部分数据创建索引

---说明：案例来自《收获，不止SQL优化》

请问：Oracle可以针对某列的部分数据创建索引吗？比如t1表id列的数据有1,2,3,4,5。可以只针对id=3的数据创建索引吗？

可以通过函数索引实现只针对id=3的数据创建索引，例如:

Create index i_t1_id on t(case when id=3 then 3 end);

请问：请举一个具体案例，并说明在该场景下使用部分索引性能更好？

案例如下：

---创建测试表t

SQL>createtablet (idint,status varchar2(2));

--建立普通索引

SQL>createindexid_normal ont(status);

--插入数据

SQL>insertintot selectrownum,'Y'fromdual connectbyrownum<=100000;

SQL>insertintot select1,'N'fromdual;

SQL>commit;

---数据分布

SQL>selectcount(*),status fromt groupbystatus;

---收集统计信息

SQL>analyzetablet computestatisticsfortableforallindexesforallindexedcolumns;

---查询表t，查看执行计划

SQL>setlinesize 1000

SQL>setautotrace traceonly

SQL>select*fromt wherestatus='N';

--查看索引信息

SQL>setautotrace off

SQL>analyzeindexid_normal validatestructure;

SQL>selectname,btree_space,lf_rows,height fromindex_stats;

备注：INDEX_STATS存储的是最近一次ANALYZE INDEX ... VALIDATE STRUCTURE语句的结果，最多只有当前会话的一条数据。

---创建函数索引的情况

SQL>dropindexid_normal;

SQL>createindexid_status ont (Casewhenstatus='N'then'N'end);

SQL>analyzetablet computestatisticsfortableforallindexesforallindexedcolumns;

---再次查看执行计划

SQL>setautotrace traceonly

SQL>select*fromt where(casewhenstatus='N'then'N'end)='N';

--观察id_status索引的情况

SQL>setautotrace off

SQL>analyzeindexid_status validatestructure;

SQL>selectname,btree_space,lf_rows,height fromindex_stats;

---在对比下之前普通索引的值

结论：普通索引改成函数索引后，索引当前分配的空间(BTREE_SPACE)由20230168降到7996，,逻辑读consistent gets由5降到2，索引叶子数(LF_ROWS)由100001降到1，索引高度(HEIGHT)由3降到1，性能有所提升。

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！