http://forums.oracle.com/forums/message.jspa?messageID=3661603
- Standardエディションを使っているし
- Standardエディションは1)Bitmap Indexとその兄弟と言える2)Index Combinationを支援しません。
- Function-based Indexを使っているし
- OR Predicateを使っています。
- 一番大きい問題はSQL文章を変えることができないと言うことです。Softwareに含まれているQueryと言う理由で。。。
私がなぜこれを悲しい話と言うのかは下のデモを見れば理解できるんでしょう。
1. オブジェクトを作ります。
drop table t1 purge;
create table t1(c1 int, c2 int, c3 int);
insert into t1
select level, level, level
from dual
connect by level <= 100000;
create index t1_n1 on t1(c1+1); -- function-based index
create index t1_n2 on t1(c2+1); -- function-based index
create index t1_n3 on t1(c1); -- normal index
create index t1_n4 on t1(c2); -- normal index
exec dbms_stats.gather_table_stats(user, 't1');
2.Index Combinationが適用できる場合は次のようにとても効率的な実行計画を作ります。
alter session set "_b_tree_bitmap_plans" = true;
explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 48 | 2 (0)|
| 2 | BITMAP CONVERSION TO ROWIDS | | | | |
| 3 | BITMAP OR | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 5 | INDEX RANGE SCAN | T1_N1 | | | 1 (0)|
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 7 | INDEX RANGE SCAN | T1_N2 | | | 1 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C1"+1=1)
7 - access("C2"+1=1)
3. でも、Index Combinationが非活性化すれば？
alter session set "_b_tree_bitmap_plans" = false; -- In standard edition, this would be fixed behavior.
explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 99 (6)|
|* 1 | TABLE ACCESS FULL| T1 | 2 | 48 | 99 (6)|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"+1=1 OR "C2"+1=1)
驚くべきことにFull Table Scanを選択してしまいます。
4.Function-based Indexではない一般Indexの場合には次善の策でOr-Expansionを選択します。これだけでも十分に効率的です。
explain plan for
select *
from t1
where c1 = 1 or c2 = 1
;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 30 | 4 (0)|
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 2 (0)|
|* 3 | INDEX RANGE SCAN | T1_N4 | 1 | | 1 (0)|
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 2 (0)|
|* 5 | INDEX RANGE SCAN | T1_N3 | 1 | | 1 (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2"=1)
4 - filter(LNNVL("C2"=1))
5 - access("C1"=1)
5. では、次のようにヒントを通じて完璧に制御すればどうでしょうか。
explain plan for
select
/*+
INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" "T1_N1")
INDEX_RS_ASC(@"SEL$1_1" "T1"@"SEL$1" "T1_N2")
USE_CONCAT(@"SEL$1" 8) */
*
from t1
where c1+1 = 1 or c2+1 = 1
;
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 48 | 195 (4)|
| 1 | CONCATENATION | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 24 | 98 (5)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 24 | 98 (5)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C2"+1=1)
3 - filter("C1"+1=1 AND LNNVL("C2"+1=1))
やはり無駄です。
なぜこんな現状が起きるんでしょうか。下の文章によく説明されています。
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_in.htm#1006464
불행하게도 Oracle은 Or-Expansion과 Funtion-based Index를 같이 사용하지 못합니다. 따라서 이 경우에는 Full Table Scan이 가장 효율적인 실행 계획이 되어 버린 셈입니다.
すなわち、オラクルはOr-ExpansionとFunction-based Indexを一緒に使えません。従って、この場合にはFull Table Scanが一番効率的な実行計画になってしまいました。
6. 試しできるトリックの一つは手動でStored Outlineを作るのです。でも、この場合にはそれさえも不可能です。ヒントで制御できないからです。
7. 最後のトリックはAdvanced Query Rewriting機能を使ってSQL文章自体を変えてしまうということです。ただし、この機能は次のような制約を持ってあります。
- やはりEnterpriseエディション
- Select文章だけ支援します。
- バインド変数は支援しません。
上の簡単なデモの場合にはこの機能を使って解決できません。
begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
name => 'rewrite1',
source_stmt =>
'select *
from t1
where c1+1 = 1 or c2+1 = 1',
destination_stmt =>
'select *
from t1
where c1 = 0 or c2 = 0',
validate => false,
rewrite_mode => 'text_match');
end;
/
alter session set query_rewrite_integrity = trusted;
explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 30 | 4 (0)|
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 2 (0)|
|* 3 | INDEX RANGE SCAN | T1_N4 | 1 | | 1 (0)|
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 2 (0)|
|* 5 | INDEX RANGE SCAN | T1_N3 | 1 | | 1 (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2"=0)
4 - filter(LNNVL("C2"=0))
5 - access("C1"=0)
しかし、実際の本番環境では使用するのにはあまり制約が多いと思います。
Posts
