2018.03.22
REWRITE OR ERROR
目次
- 1. 基本情報
基本情報
ヒント
REWRITE_OR_ERROR
構文
/*+ 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.
