部分行索引使用介绍

函数索引是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

部分行索引在特定情况下(譬如某表中仅少数特定行有大量查询更新操作)可以发挥非常巨大的作用。

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号