今天研究了一下oracle对部分记录建立索引的方法。其实对部分记录建立的索引就是基于
函数的索引。由于部分记录函数化以后，返回了NULL值，而ORACLE索引不索引NULL值，
导致这些记录没有索引条目。
这样我们就可以对自已关注的少数记录建立很小索引，提高查询速度。
一个例子，学校有10000学生，其中女同学仅有100人。我们经常关注女生，几乎不关注男生。
这样我们就可以只在女生记录上添加索引。这个很小的索引就能帮我们很快把女生找到。
做个测试：
create
table students(id
int ,
name varchar2(200),sex
varchar(16))
2 /
Table created
SQL>
declare
2 style varchar2(16);
3
begin
4
for i
in 1..10000 loop
5
if(i mod 100 = 0)
then
6 style:=
'F';
7
else style :=
'M';
8
end
if;
9
insert
into students
values(i,rpad(
'student'||i,150,
'F'),style);
10
end loop;
11
commit;
12
end;
13 /
PL/SQL
procedure
这里为了让女生分布均匀，加了i mod 100；
SQL> select count(1) from students where sex='F'
2 /
COUNT(1)
----------
100
刚好100位女生.
select *
from students
where (
case
when sex=
'F'
then sex
end)=
'F'
2 /
已选择100行。
执行计划
----------------------------------------------------------
Plan hash value: 4078133427
------------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | | 93 | 11625 | 56 (2)| 00:00:01 |
|* 1 |
TABLE ACCESS
FULL| STUDENTS | 93 | 11625 | 56 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified
by operation id):
---------------------------------------------------
1 - filter(
CASE "SEX"
WHEN
'F'
THEN "SEX"
END =
'F')
Note
-----
- dynamic sampling used
for this statement
统计信息
----------------------------------------------------------
169 recursive calls
0 db block gets
337 consistent gets
0 physical reads
0 redo
size
17196 bytes sent via SQL*Net
to client
451 bytes received via SQL*Net
from client
8 SQL*Net roundtrips
to/
from client
4 sorts (memory)
0 sorts (
disk)
100 rows processed
执行计划采用了全表扫描。
建立部分元组索引:
create
index femaleIndex
on students(
case
when sex=
'F'
then sex
end);
Index created
SQL>
exec dbms_stats.gather_index_stats(
'study',
'femaleIndex')
PL/SQL
procedure successfully completed
SQL>
select num_rows,blevel
from user_indexes
where index_name=
upper(
'femaleIndex')
2 /
NUM_ROWS BLEVEL
---------- ----------
100 0
索引条目刚好100个，与女生数相等。
select *
from students
where (
case
when sex=
'F'
then sex
end)=
'F'
2 /
已选择100行。
执行计划
----------------------------------------------------------
Plan hash value: 2152294204
| Id | Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | | 93 | 11625 | 2 (0)|00:00:01 |
| 1 |
TABLE ACCESS
BY
INDEX ROWID| STUDENTS | 93 | 11625 | 2 (0)| 00:00:01 |
|* 2 |
INDEX RANGE SCAN | FEMALEINDEX| 38 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified
by operation id):
---------------------------------------------------
2 - access(
CASE "SEX"
WHEN
'F'
THEN "SEX"
END =
'F')
Note
------ dynamic sampling used for this statement
统计信息
----------------------------------------------------------
11 recursive calls
0 db block gets
174 consistent gets
0 physical reads
0 redo
size
17196 bytes sent via SQL*Net
to client
451 bytes received via SQL*Net
from client
8 SQL*Net roundtrips
to/
from client
0 sorts (memory)
0 sorts (
disk)
100 rows processed
可以看到速度提高了很多。但是如果我们查询男生：
select *
from students
where (
case
when sex=
'F'
then sex
end)=
'M'
2 /
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2152294204
-------------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes | Cost (%CPU)|Time |
--------------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | | 3 | 375 | 2 (0)|00:00:01 |
| 1 |
TABLE ACCESS
BY
INDEX ROWID| STUDENTS| 3 | 375 | 2 (0)|00:00:01 |
|* 2 |
INDEX RANGE SCAN | FEMALEINDEX | 38 | | 1 (0)|00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified
by operation id):
---------------------------------------------------
2 - access(
CASE "SEX"
WHEN
'F'
THEN "SEX"
END =
'M')
Note
-----
- dynamic sampling used
for this statement
统计信息
----------------------------------------------------------
11 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo
size
375 bytes sent via SQL*Net
to client
374 bytes received via SQL*Net
from client
1 SQL*Net roundtrips
to/
from client
0 sorts (memory)
0 sorts (
disk)
0 rows processed
居然没有记录返回!! 当然了因为(case when sex='F' then sex end)这个函数，我们姑且把它看成一个函数，是不会返回'M'这个值的。这也说明了如果建立了函数索引，查询条件上使用了该函数，则查询必走此索引。
以上是个简单的例子，假如我们一个项目管理系统，项目只有三种状态：投标，开发，验收。历经很多年,验收的验目很多，但正在投标，开发的项目肯定不多(IBM可能有很多)，这样我们就可以只在需要关注的投标与开发的记录上添加索引:
create index test on projects(case when status='投标' then status when status='开发' then status end).
部分记录建立的索引还有一个经典用法就是建立唯一索引，完成对记录的约束，比如上面的例子，如果建成唯一索引，那么全校只能有一个女生了，而男生无限制。我不喜欢这样，所以就不演示了，记得加个unique就成。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
