189 8069 5689
Oracle 对某列的部分数据创建索引
成都创新互联公司主营进贤网站建设的网络公司,主营网站建设方案,成都App制作,进贤h5成都小程序开发搭建,进贤网站营销推广欢迎进贤等地区企业咨询
--- 说明：案例来自《 收获，不止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"，共同学习，共同成长！！！
