部分行索引使用介绍_第三行用到的索引命令是-CSDN博客

部分行索引使用介绍

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

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