DBMS_ADVANCED_REWRITEとDML

Oracle10gで紹介されたDBMS_ADVANCED_REWRITEパッケージを利用すれば特定のSQL文章のテキスト自体が変えられます。強力な機能ですけど、この機能はOLTPではなくてDW用で設計されたという限界があります。次のような制約を持っています。

1. バインド変数を含んだ場合には動作しません。(Metalink Doc ID. 392214.1)
   
2. SELECT文章についてだけ適用可能です。
   
3. ベーステーブルについてDMLが発生すれば動作しません。
   

三つ目の制約を避けられる方法はよく知られていません。テストを通じて見つけられるのはQUERY_REWRITE_INTEGRITYパラメータで制御可能だということです。

簡単なテストケースを通じて説明してみます。

1. 次のように簡単なテーブルを作ります。

UKJA@ukja1106> create table t1
  2  as select level as c1, level as c2
  3  from dual
  4  connect by level <= 1000;

Table created.

Elapsed: 00:00:00.04

UKJA@ukja1106> create index t1_n1 on t1(c1);

Index created.

Elapsed: 00:00:00.03

UKJA@ukja1106> exec dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

2. FULLのヒントを持っているSELECT文章があって、当然にFull Table Scanが選択されます。

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     7 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

3. これを避けられる方法中一つは次のようにSQL文章を変えてしまうことです。この時QUERY_REWRITE_INTEGRITYパラメータの値をTRUSTEDに変更すべきです。

UKJA@ukja1106> begin
  2  
  3        sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
  4        'test_rewrite',
  5        'select /*+ full(t1) */ * from t1 where c1 = 1',
  6        'select /*+ index(t1) */ c1, c2 from t1 where c1 = 1',
  7        false,
  8        'text_match');
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

UKJA@ukja1106> alter session set query_rewrite_integrity=trusted;

Session altered.

Elapsed: 00:00:00.00

4. 次のように魔法のようにIndex Range Scanに実行計画が変わってしまいます。

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

5. 問題は次のようにDMLが発生すればRewriteができないということです。

UKJA@ukja1106> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.00

UKJA@ukja1106> commit;

Commit complete.

Elapsed: 00:00:00.00

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     7 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

6. これを避けられる一つの方法はQUERY_REWRITE_INTEGRITYパラメータの値をSTALE_TOLERATEDに変えることです。データがSTALEでも(最新の状態ではなくても)、TOLERATEしろ(堪えろ)ということですよ。

UKJA@ukja1106> alter session set query_rewrite_integrity=stale_tolerated;

Session altered.

Elapsed: 00:00:00.00

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

7. DMLが発生してもRewriteは成功的に整います。

UKJA@ukja1106> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.01

UKJA@ukja1106> commit;

Commit complete.

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

8. ただし、も一つの制約があります。DMLの発生後Commitが実行されなければRewriteはまた失敗してしまいます。

UKJA@ukja1106> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.01

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     7 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

何やかやでOLTPでは制約が多いんです。DW環境では本当に有用かも知れませんが。OLTPでも適当な所によく使用したら大きい効果が得られるでしょう。