Archive for the ‘Misc.’ Category
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.
- Does not work with the bind variables.(Metalink Doc ID. 392214.1)
- Only works for the SELECT statement.
- 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. :(
