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

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 >   create   table  t ( id   int   , status varchar2 ( 2 ));

--建立普通索引

SQL >   create   index  id_normal on  t ( status );

-- 插入数据

SQL >   insert   into  t select   rownum   , 'Y'   from  dual connect   by   rownum <= 100000 ;

SQL >   insert   into  t select   1   , 'N'   from  dual ;

SQL >   commit ;

---数据分布

SQL >   select   count (*), status from  t group   by  status ;

--- 收集统计信息

SQL >   analyze   table  t compute   statistics   for   table   for   all   indexes   for   all   indexed   columns ;

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

SQL >   set  linesize 1000

SQL >   set  autotrace traceonly

SQL >   select   *   from  t where  status = 'N' ;

-- 查 看索引 信息

SQL >   set  autotrace off

SQL >   analyze   index  id_normal validate   structure ;

SQL >   select   name , btree_space , lf_rows , height from  index_stats ;

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

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

SQL >   drop   index  id_normal ;

SQL >   create   index  id_status on   t ( Case   when  status =   'N'   then   'N'   end );

SQL >   analyze   table  t compute   statistics   for   table   for   all   indexes   for   all   indexed   columns ;

--- 再次查看执行计划

SQL >   set  autotrace traceonly

SQL >   select   *   from  t where   ( case   when  status = 'N'   then   'N'   end )= 'N' ;

--观察id_status索引的情况

SQL >   set  autotrace off

SQL >   analyze   index  id_status validate   structure ;

SQL >   select   name , btree_space , lf_rows , height from  index_stats ;

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

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

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