DBMS_ADVANCED_REWRITE and DML

原文地址：

Oracle 10g has introduced DBMS_ADVANCED_REWRITE package which enables a specific query to be transformed to another form. Very powerful feature, but this is designed for DW not OLTP. Thus has following restrictions.

1. Does not work with the bind variables.(Metalink Doc ID. 392214.1)
2. Only works for the SELECT statement.
3. Does not work when the base table is modified through DML.

It’s not well known how to avoid 3rd restriction. The only way I got from some test cases is to use QUERY_REWRITE_INTEGRITY parameter.

Let me explain through simple test case.

1. Make objects.

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. The SELECT statement has FULL hint and (naturally) follows 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. How to fix it? DBMS_ADVANCED_REWRITE package would give you the power to change the SELECT statement on the fly with QUERY_REWRITE_INTEGRITY parameter set to 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. Now you have a plan with magical 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. But one simple DML would prevent rewrite from happening.

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. One way to avoid this restriction is to set QUERY_REWRITE_INTEGRITY parameter to STALE_TOLERATED.

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. Now DML does not prevent the rewrite from happening.

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. But here you have another restriction. If the DML is not committed, the rewrite would fail even with STALE_TOLERATED setting.

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 |
--------------------------------------------------------------------------

Hm… there seems to be really many restrictions for OLTP, but these would not make any problem for DW. But I believe that even OLTP would get a big and good effect when used in the right place.

Footnote:It makes me a bit gloomy not to post on OOW events but on these little tips. Well, the only consolation is that San Francisco is very far from here and I really hate long-distance journey. :(