部分行索引使用介绍
函数索引是Oracle索引中比较特殊的，我们这里讨论函数索引中部分行索引的使用。
部分行索引顾名思义仅就表中的一部分记录做索引，请看代码示例:
drop table test;
create table test (t1 int, t2 char(1));
declare
i int :=0;
begin
while i<100000
loop
insert into test values( i, 'N');
i:=i+1;
commit;
end loop;
end;
在test表上插入大量t2为N的行，并插入少量t2为Y的行
create index ind_t2y on test( case t2 when 'Y' then t2 end);
SQL> select count(*) from test;
COUNT(*)
----------
100004
表上供有100004条数据
SQL> select count(*) from test where t2='Y';
COUNT(*)
----------
4
为t2列为'Y'的共有4条。
我们来分析该索引：
SQL> analyze index ind_t2y validate structure;
索引已分析
SQL> select lf_rows from index_stats;
LF_ROWS
----------
4
可以看到确实仅记录了4条记录。
我们尝试利用此部分行索引:
SQL> set autotrace on;
SQL> select count(*) from test where t2='Y';
COUNT(*)
----------
4
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 43 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST | 2 | 6 | 43 (12)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T2"='Y')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
171 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
没有如预期地使用索引，我们加上hint 再试试
SQL> select /*+ index(test ind_t2y) */ count(*) from test where t2='Y';
COUNT(*)
----------
4
Execution Plan
----------------------------------------------------------
Plan hash value: 2501600095
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 6 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T2Y | 98705 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T2"='Y')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在使用部分行索引的情况下逻辑读大大下降了。
在不加hint的情况下优化器似乎永远无法做出正确的选择，即便修改了CBO相关的参数：
SQL> alter system set optimizer_index_cost_adj=1;
System altered.
SQL> select t2 from test where t2='Y';
T
-
Y
Y
Y
Y
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6 | 43 (12)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 2 | 6 | 43 (12)| 00:00:01 |
--------------------------------------------------------------------------
仅在where 子句中指定了case when then模式时，优化器自觉地使用了该部分行索引：
SQL> select * from test where case t2 when 'Y' then t2 end ='Y';
T1 T
---------- -
100001 Y
100002 Y
100003 Y
100004 Y
Execution Plan
----------------------------------------------------------
Plan hash value: 837354983
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 32 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 32 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T2Y | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE WHEN "T2"='Y' THEN "T2" END ='Y')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
650 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
部分行索引在特定情况下(譬如某表中仅少数特定行有大量查询更新操作)可以发挥非常巨大的作用。