REWRITE OR ERROR

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] 기본 정보

[편집] 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.

[편집] 관련 정보