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

相关推荐: RMAN的使用（七）

十五、非归档模式下的完全恢复非归档模式下的恢复可能是完全恢复，也可能是不完全恢复，如果联机重做日志被复写，只能做不完全恢复的可能性更大。参数文件丢失1.控制文件、数据文件以及联机重做日志文件丢失的恢复先对数据库做全备。包括数据文件、控制文件和参数文件，并使用快…