L
o
a
d
i
n
g
.
.
.

ホーム

お知らせ

製品・ソリューション

サービス

導入事例・パートナー

EXEM Academy・ブログ

会社情報

採用情報

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.
    

PHP Code Snippets Powered By : XYZScripts.com