REWRITE OR ERROR
EXEM Knowledge Base
목차 |
[편집] 기본 정보
[편집] Hint 명
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.