1.
次の結果をご覧ください。特に索引範囲スキャンのFilter Predicate部分に注意してください。
drop table t1 purge;
create table t1 (
c1 varchar2(10),
c2 varchar2(10),
c3 varchar2(10),
c4 varchar2(10),
c5 varchar2(10)
);
insert into t1
select mod(level, 2), mod(level, 2), mod(level, 2), mod(level, 2), level
from dual
connect by level <= 1000
;
exec dbms_stats.gather_table_stats(user, 't1');
create index t1_n1 on t1(c1, c2, nvl(c3,'x'), c4);
explain plan for
select /*+ index(t1 t1_n1) */
*
from t1
where c1 = :b1 and c2 = :b2 and nvl(c3,'x') >= :b3 and c4 = :b4 and c5 = :b5
;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 3 (0)|
|* 2 | INDEX RANGE SCAN | T1_N1 | 4 | | 2 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C5"=:B5)
2 - access("C1"=:B1 AND "C2"=:B2 AND NVL("C3",'x')>=:B3 AND "C4"=:B4 AND
NVL("C3",'x') IS NOT NULL)
filter("C4"=:B4)
これまではどんな問題もありません。範囲PredicateのNVL("C3",'x')>=:B3のおかげで"C4"=:B4条件がFilter Predicateで使われました。
2. いま二番目のFunction Based Indexを生成します。次の結果を見てください。変なFilter Predicateが付いているのを気づきましたか － SUBSTR("T1"."C4",1,3)=SUBSTR(:B4,1,3)。そして、このPredicateのかげで予測行件数が4から1に変わってしまいました。
create index t1_n2 on t1(c1, c2, substr(c4,1,3));
explain plan for
select /*+ index(t1 t1_n1) */
*
from t1
where c1 = :b1 and c2 = :b2 and nvl(c3,'x') >= :b3 and c4 = :b4 and c5 = :b5
;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 3 (0)|
|* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 2 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C5"=:B5)
2 - access("C1"=:B1 AND "C2"=:B2 AND NVL("C3",'x')>=:B3 AND "C4"=:B4 AND
NVL("C3",'x') IS NOT NULL)
filter("C4"=:B4 AND SUBSTR("T1"."C4",1,3)=SUBSTR(:B4,1,3))
SUBSTR("T1"."C4",1,3)=SUBSTR(:B4,1,3)だと？ このPredicateがどうして追加されたのでしょうか。なぜオラクルが二番目の索引が含んでいる表現を一番目の索引に使用しているのでしょうか。
多分もう正答が分かっているでしょう。はい、Function Based IndexはHidden列を作ります。
3. 10053トレースに対してDiffをしてみれば手軽にその違いが分かることができます。
-- With 1 function index -- With 2 function indexes
SINGLE TABLE ACCESS PATH | SINGLE TABLE ACCESS PATH
Column (#1): C1(VARCHAR2) | Column (#1): C1(VARCHAR2)
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
Column (#2): C2(VARCHAR2) | Column (#2): C2(VARCHAR2)
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
Column (#4): C4(VARCHAR2) | Column (#7): SYS_NC00007$(VARCHAR2) NO STATISTICS
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 6.00 NDV: 0 Nulls: 0 Density: 0.0000e+00
Column (#5): C5(VARCHAR2) | Column (#4): C4(VARCHAR2)
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
Column (#6): SYS_NC00006$(VARCHAR2) NO STAT| Column (#5): C5(VARCHAR2)
AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
Table: T1 Alias: T1 | Column (#6): SYS_NC00006$(VARCHAR2) NO STATISTICS
Card: Original: 0 Rounded: 1 Computed: 0| AvgLen: 10.00 NDV: 0 Nulls: 0 Density: 0.0000e+0
kkofmx: index filter: | Table: T1 Alias: T1
"T1"."C4"=:B1 AND | Card: Original: 0 Rounded: 1 Computed: 0.00 N
"T1"."C5"=:B2 AND | kkofmx: index filter:
NVL("T1"."C3",'x')>=:B3 | SUBSTR("T1"."C4",1,3)=SUBSTR(:B1,1,3) AND
Access Path: index (IndexOnly) | "T1"."C4"=:B2 AND "T1"."C5"=:B3 AND
Index: T1_N1 | NVL("T1"."C3",'x')>=:B4
resc_io: 0.00 resc_cpu: 200 | kkofmx: index filter:
ix_sel: 9.0000e-007 ix_sel_with_filters: | "T1"."C4"=:B1 AND
Cost: 0.00 Resp: 0.00 Degree: 1 | "T1"."C5"=:B2 AND
Best:: AccessPath: IndexRange Index: T1_N1 | NVL("T1"."C3",'x')>=:B3
Cost: 0.00 Degree: 1 Resp: 0.00 Ca| Access Path: index (IndexOnly)
| Index: T1_N1
| resc_io: 0.00 resc_cpu: 200
| ix_sel: 9.0000e-007 ix_sel_with_filters: 9.0000
| Cost: 0.00 Resp: 0.00 Degree: 1
| Best:: AccessPath: IndexRange Index: T1_N1
| Cost: 0.00 Degree: 1 Resp: 0.00 Card: 0.
|
二番目のFunction Based Indexによって、SYS_NC00007$(SUBSTR("T1"."C4",1,3))が作られたのがわかります。一番目の索引がT1.C4列を含んでいるから、この条件がFilter Predicateで使われたのです。
一般的には、この現状は問題になりません。しかし、予測行件数が低くなるから、特定な状況では問題を起こす可能性もあります。
Posts
