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"，共同学习，共同成长！！！
