REWRITE OR ERROR - 日本エクセム株式会社 Oracle 技術情報
基本情報
ヒント名
REWRITE_OR_ERROR
Syntax
/*+ REWRITE_OR_ERROR */サポート
10g ~
詳細
Materialized Viewを使うようにQuery Rewriteが失敗する場合にQuery実行を中断してError (30393)を発生するように指定します。Query Rewrite失敗によって極端的な性能低下が発生することを防止する目的に使用されます。
30393 // *Cause: A query block with a REWRITE_OR_ERROR hint did not rewrite
// *Action: Verify the rewrite equivalence has been created使用例を以下に示します。
UKJA@ukja102> create table t1(c1, c2)
2 as select mod(level, 10), level
3 from dual
4 connect by level <= 10000
5 ;
Table created.
UKJA@ukja102>
UKJA@ukja102> create materialized view t1_mv
2 build immediate refresh force enable query rewrite
3 as
4 select c1, sum(c2)
5 from t1
6 group by c1
7 ;
Materialized view created.
UKJA@ukja102>
UKJA@ukja102> alter session set query_rewrite_enabled = true;
Session altered.
UKJA@ukja102> alter session set query_rewrite_integrity = enforced;
Session altered.
UKJA@ukja102>
UKJA@ukja102> explain plan for
2 select c1, sum(c2)
3 from t1
4 group by c1
5 ;
Explained.
UKJA@ukja102> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2390704265
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 260 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| T1_MV | 10 | 260 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> insert into t1 values(100, 1);
1 row created.
UKJA@ukja102> commit;
Commit complete.
UKJA@ukja102>
UKJA@ukja102> explain plan for
2 select c1, sum(c2)
3 from t1
4 group by c1
5 ;
Explained.
UKJA@ukja102> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 136660032
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 253K| 10 (20)| 00:00:01 |
| 1 | HASH GROUP BY | | 10001 | 253K| 10 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 10001 | 253K| 8 (0)| 00:00:01 |
---------------------------------------------------------------------------
UKJA@ukja102>
UKJA@ukja102> explain plan for
2 select /*+ rewrite_or_error */ c1, sum(c2)
3 from t1
4 group by c1
5 ;
from t1
*
ERROR at line 3:
ORA-30393: a query block in the statement did not rewrite
UKJA@ukja102> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 136660032
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 253K| 10 (20)| 00:00:01 |
| 1 | HASH GROUP BY | | 10001 | 253K| 10 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 10001 | 253K| 8 (0)| 00:00:01 |
---------------------------------------------------------------------------
UKJA@ukja102>
UKJA@ukja102> alter session set query_rewrite_integrity = trusted;
Session altered.
UKJA@ukja102>
UKJA@ukja102> explain plan for
2 select /*+ rewrite_or_error */ c1, sum(c2)
3 from t1
4 group by c1
5 ;
from t1
*
ERROR at line 3:
ORA-30393: a query block in the statement did not rewrite
UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> alter session set query_rewrite_integrity = stale_tolerated;
Session altered.
UKJA@ukja102>
UKJA@ukja102> explain plan for
2 select /*+ rewrite_or_error */ c1, sum(c2)
3 from t1
4 group by c1
5 ;
Explained.
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2390704265
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 260 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| T1_MV | 10 | 260 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
12 rows selected
