dbms_advance_rewrite来workround

原始sql:

select /*+ opt_param('query_rewrite_integrit','trusted') */ * from t_or where object_id is null or object_name='aa'

SCOTT@oracle11g>create table t_or as select object_id,object_name from dba_objects;

表已创建。

SCOTT@oracle11g>create index i_t_or on t_or(object_id,1);

索引已创建。

SCOTT@oracle11g>create index i_t_or_1 on t_or(object_name);

索引已创建。


SCOTT@oracle11g>analyze table t_or compute statistics;

表已分析。

SCOTT@oracle11g>set autot trace
SCOTT@oracle11g>select * from t_or where object_id is null or object_name='aa';


执行计划
----------------------------------------------------------
Plan hash value: 56920880

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     9 |   252 |    95   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_OR |     9 |   252 |    95   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID" IS NULL OR "OBJECT_NAME"='aa')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        371  consistent gets
          0  physical reads
          0  redo size
        475  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2.生成dbms_advanced_rewrite的过程:

BEGIN
SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
name => 'test_rewrite2',
source_stmt => 'select /*+ opt_param(''query_rewrite_integrity'',''trusted'') */ * from t_or where object_id is null or

object_name=''aa''',
destination_stmt => 'select /*+ opt_param(''query_rewrite_integrity'',''trusted'') */ * from t_or where object_id is null

union select * from t_or where object_name=''aa''',
validate => FALSE,
rewrite_mode => 'TEXT_MATCH');
END;
/

因为dbms_advanced_rewrite在query_rewrite_integrity=trusted的时候可行,因此我只修改这个sql,默认让它是trusted

3.重新执行sql:

SCOTT@oracle11g>select /*+ opt_param('query_rewrite_integrity','trusted') */ * from t_or where object_id is null or

object_name='aa'

执行计划
----------------------------------------------------------
Plan hash value: 87288835

--------------------------------------------------------------------------------
-----------

| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------
-----------

|   0 | SELECT STATEMENT               |          |     3 |   237 |     9  (23)|
00:00:01 |

|   1 |  VIEW                          |          |     3 |   237 |     9  (23)|
00:00:01 |

|   2 |   SORT UNIQUE                  |          |     3 |    84 |     9  (67)|
00:00:01 |

|   3 |    UNION-ALL                   |          |       |       |            |
          |

|   4 |     TABLE ACCESS BY INDEX ROWID| T_OR     |     1 |    28 |     3   (0)|
00:00:01 |

|*  5 |      INDEX RANGE SCAN          | I_T_OR   |     1 |       |     2   (0)|
00:00:01 |

|   6 |     TABLE ACCESS BY INDEX ROWID| T_OR     |     2 |    56 |     4   (0)|
00:00:01 |

|*  7 |      INDEX RANGE SCAN          | I_T_OR_1 |     2 |       |     3   (0)|
00:00:01 |

--------------------------------------------------------------------------------
-----------


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("OBJECT_ID" IS NULL)
   7 - access("OBJECT_NAME"='aa')


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        475  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

[ 本帖最后由 棉花糖ONE 于 2011-1-6 08:47 编辑 ]

to 棉花糖ONE
dbms_advance_rewrite来workround 这个功能还是有一些局限性吧
原始的sql 很少写成这样的吧：select /*+ opt_param('query_rewrite_integrity','trusted') */ * from t_or where object_id is null or object_name='aa'

原始的sql 应该是这样的居多：select * from t_or where object_id is null or object_name='aa';
而这样的sql进行替换 ，比如：
SQL> BEGIN
  2  SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
  3  name => 'test_rewrite2',
  4  source_stmt => 'select * from t_or where object_id is null or object_name=''aa''',
  5  destination_stmt => 'select /*+ opt_param(''query_rewrite_integrity'',''trusted'') */ * from t_or where object_id is null union select * from t_or where object_name=''aa''',
  6  validate => FALSE,
  7  rewrite_mode => 'TEXT_MATCH');
  8  END;
  9  /

PL/SQL procedure successfully completed

TEST@ sun>select * from t_or where object_id is null or object_name='aa';

OBJECT_ID OBJECT_NAME
---------- ------------------------------
           L_EARTH


执行计划
----------------------------------------------------------
Plan hash value: 56920880

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    84 |    46   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_OR |     3 |    84 |    46   (3)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID" IS NULL OR "OBJECT_NAME"='aa')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        242  consistent gets
          0  physical reads
          0  redo size
        487  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

TEST@ sun>ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;       --要修改参数 影响略大一些

会话已更改。

TEST@ sun>select * from t_or where object_id is null or object_name='aa';

OBJECT_ID OBJECT_NAME
---------- ------------------------------
           L_EARTH


执行计划
----------------------------------------------------------
Plan hash value: 87288835

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     3 |   237 |     7  (29)| 00:00:01 |
|   1 |  VIEW                          |          |     3 |   237 |     7  (29)| 00:00:01 |
|   2 |   SORT UNIQUE                  |          |     3 |    84 |     7  (58)| 00:00:01 |
|   3 |    UNION-ALL                   |          |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_OR     |     1 |    28 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | I_T_OR   |     1 |       |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T_OR     |     2 |    56 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | I_T_OR_1 |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("OBJECT_ID" IS NULL)
   7 - access("OBJECT_NAME"='aa')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        487  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed