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 编辑 ]
原始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
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22936840/viewspace-750414/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22936840/viewspace-750414/