函数索引是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 部分行索引在特定情况下(譬如某表中仅少数特定行有大量查询更新操作)可以发挥非常巨大的作用。
部分行索引使用介绍
最新推荐文章于 2022-10-24 07:57:37 发布