By Franck Pachot

.
When I passed the OCM 11g exam, I had to prepare the ‘Manage Materialized Views to improve rewrite’ topic. Now that I’m preparing the 12c upgrade I checked quickly what I’ve prepared… and it doesn’t work. This post is about query rewrite, a bug in 12c, how to understand why it doesn’t rewrite, and how rewrite is accepted when constraints are not enforced and when materialized view is stale.

SCOTT

I start with the SCOTT schema where I alter EMP to define DEPTNO as not null, meaning that I’ve a one-to-many association.


SQL> host sqlplus / as sysdba @ ?/rdbms/admin/utlsampl
SQL> alter table SCOTT.EMP modify ( DEPTNO not null);
Table altered.

And I’ll need a few additional grants for my test.


SQL> grant create materialized view, select any dictionary to scott;
Grant succeeded.

Create materialized view

I’m creating a materialized view that join the two tables DEPT and EMP


SQL> connect scott/tiger
Connected.
SQL> 
SQL> create materialized view TEST_MV enable query rewrite
  2   as
  3   select * from DEPT join EMP using (DEPTNO);
Materialized view created.

I check that query_rewrite_integrity is enabled


SQL> show parameter rewrite
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

and run a query on EMP. The query should be able to read the materialized view instead of the table, because it has all rows and all columns. Because the cost may not be better, I force the query rewrite with the REWRITE_OR_ERROR hint: force the rewrite and fail if not possible.


SQL> set autotrace on explain
SQL> select /*+ rewrite_or_error */ * from EMP;
select /*+ rewrite_or_error */ * from EMP
                                      *
ERROR at line 1:
ORA-30393: a query block in the statement did not rewrite
 
SQL> set autotrace off

Hummm. No rewrite. If you try it on 11.2.0.3 query rewrite occurs. But not in 11.2.0.4 not in 12.1

Explain rewrite

When you have to understand why query rewrite do not occur, you can have more information with dbms_mview.explain_rewrite. In 11g we had the possibility to do it easily with dbconsole, but in 12c there is nothing in EM Express nor in SQL Developer. If I’m wrong, please tell me.

Thus, we have to use it in command line. First, we need to create the EXPLAIN_REWRITE table to store the result.


SQL> @ ?/rdbms/admin/utlxrw.sql


SQL> Rem
SQL> Rem $Header: rdbms/admin/utlxrw.sql /main/7 2011/05/20 12:21:56 traney Exp $
SQL> Rem
SQL> Rem utlxrw.sql
SQL> Rem
SQL> Rem Copyright (c) 2000, 2011, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      utlxrw.sql - Create the output table for EXPLAIN_REWRITE
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem     Outputs of the EXPLAIN_REWRITE goes into the table created
SQL> Rem     by utlxrw.sql (called REWRITE_TABLE). So utlxrw must be
SQL> Rem     invoked before any EXPLAIN_REWRITE tests.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      If user specifies a different name in EXPLAIN_REWRITE, then
SQL> Rem      it should have been already created before calling EXPLAIN_REWRITE.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    traney	04/05/11 - 35209: long identifiers dictionary upgrade
SQL> Rem    desingh	10/19/05 - bug#4401918:change join_back cols lengths
SQL> Rem    mthiyaga	04/29/05 - Remove unncessary comment
SQL> Rem    mthiyaga	06/08/04 - Add rewritten_txt field
SQL> Rem    mthiyaga	10/10/02 - Add extra columns
SQL> Rem    mthiyaga	09/27/00 - Create EXPLAIN_REWRITE output table
SQL> Rem    mthiyaga	09/27/00 - Created
SQL> Rem
SQL> Rem
SQL> CREATE TABLE REWRITE_TABLE(
  2  		       statement_id	     VARCHAR2(30),  -- id for the query
  3  		       mv_owner 	     VARCHAR2(128),  -- owner of the MV
  4  		       mv_name		     VARCHAR2(128),  -- name of the MV
  5  		       sequence 	     INTEGER,	    -- sequence no of the error msg
  6  		       query		     VARCHAR2(4000),-- user query
  7  		       query_block_no	     INTEGER,	    -- block no of the current subquery
  8  		       rewritten_txt	     VARCHAR2(4000),-- rewritten query
  9  		       message		     VARCHAR2(512), -- EXPLAIN_REWRITE error msg
 10  		       pass		     VARCHAR2(3),   -- rewrite pass no
 11  		       mv_in_msg	     VARCHAR2(128),  -- MV in current message
 12  		       measure_in_msg	     VARCHAR2(30),  -- Measure in current message
 13  		       join_back_tbl	    VARCHAR2(4000),-- Join back table in current msg
 14  		       join_back_col	    VARCHAR2(4000),-- Join back column in current msg
 15  		       original_cost	     INTEGER,	    -- Cost of original query
 16  		       rewritten_cost	     INTEGER,	    -- Cost of rewritten query
 17  		       flags		     INTEGER,	    -- associated flags
 18  		       reserved1	     INTEGER,	    -- currently not used
 19  		       reserved2	     VARCHAR2(10))  -- currently not used
 20  /
 
Table created.

and call the explain_rewrite procedure with the statement, and to materialized view:


SQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');
 
PL/SQL procedure successfully completed.

and check the result:


SQL> select sequence,message from REWRITE_TABLE  order by statement_id,sequence;
 
  SEQUENCE MESSAGE
---------- ------------------------------------------------------------
         1 QSM-01150: query did not rewrite
         2 QSM-01219: no suitable materialized view found to rewrite th
           is query

But there is no reason there. It’s like the materialized view cannot even be considered for query rewrite. Well, I’ll not go into the detail here. I’ve opened bug 17651484 for that. There is a problem since 11.2.0.4 where query uses ANSI join. I like ANSI join syntax, but here I have to avoid them.

Without ANSI join


SQL> drop materialized view TEST_MV;
Materialized view dropped.

SQL> create materialized view TEST_MV enable query rewrite
  2   as
  3   select DNAME,EMP.* from DEPT , EMP where DEPT.DEPTNO=EMP.DEPTNO;

Materialized view created.

and now check rewrite:


SQL> set autotrace on explain
SQL> select /*+ rewrite_or_error */ * from EMP;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1627509066

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   546 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| TEST_MV |    14 |   546 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

SQL> set autotrace off

Good. Query rewrite occured. Here is the output from explain rewrite:


SQL> delete from REWRITE_TABLE;
2 rows deleted.
 
SQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');
PL/SQL procedure successfully completed.

SQL> select sequence,message from REWRITE_TABLE  order by statement_id,sequence;

  SEQUENCE MESSAGE
---------- ------------------------------------------------------------
         1 QSM-01151: query was rewritten
         2 QSM-01033: query rewritten with materialized view, TEST_MV

From now, I’ll use only explain_rewrite to check different variations.

Disabled constraint

Constraints are not only there to verify integrity constraints, but also to give information to the optimizer. I’ve added the not null before because using the join instead of the table is possible only when we know that all rows from EMP have a matching row in DEPT. Now let’s see what happens if I remove the constraint:


SQL> alter table EMP disable constraint FK_DEPTNO;
Table altered.

SQL> delete from REWRITE_TABLE;
2 rows deleted.

SQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');
PL/SQL procedure successfully completed.

SQL> select sequence,message from REWRITE_TABLE  order by statement_id,sequence;

  SEQUENCE MESSAGE
---------- ------------------------------------------------------------
         1 QSM-01150: query did not rewrite
         2 QSM-01110: query rewrite not possible with materialized view
            TEST_MV because it contains a join between tables (EMP and
           DEPT) that is not present in the query and that potentially
           eliminates rows needed by the query
         3 QSM-01052: referential integrity constraint on table, EMP, n
           ot VALID in ENFORCED integrity mode

The explain rewrite tells me everything. The join may eliminate rows, and the disabled constraint cannot ensure that no rows are eliminated.

RELY constraint

If you know that your data verifies the constraint, and don’t want the overhead to validate it, you can alter the constraint to RELY so that the optimizer can rely on it.


SQL> alter table EMP modify constraint FK_DEPTNO rely;
Table altered.

SQL> delete from REWRITE_TABLE;
3 rows deleted.

SQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');
PL/SQL procedure successfully completed.

SQL> select sequence,message from REWRITE_TABLE  order by statement_id,sequence;

  SEQUENCE MESSAGE
---------- ------------------------------------------------------------
         1 QSM-01150: query did not rewrite
         2 QSM-01110: query rewrite not possible with materialized view
            TEST_MV because it contains a join between tables (EMP and
           DEPT) that is not present in the query and that potentially
           eliminates rows needed by the query
         3 QSM-01052: referential integrity constraint on table, EMP, n
           ot VALID in ENFORCED integrity mode

But that’s not enough. We have to read the last message. It’s not sufficient to have the constraint in RELY, we have to be trusted by the query rewrite.


SQL> alter session set query_rewrite_integrity=trusted;
Session altered.

SQL> delete from REWRITE_TABLE;
3 rows deleted.

SQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');
PL/SQL procedure successfully completed.

SQL> select sequence,message from REWRITE_TABLE  order by statement_id,sequence;

  SEQUENCE MESSAGE
---------- ------------------------------------------------------------
         1 QSM-01151: query was rewritten
         2 QSM-01033: query rewritten with materialized view, TEST_MV

Good. Now query rewrite occurs because I told the optimizer that it can RELY on the foreign key for the current data, and I told the query rewrite that it can trust me.

STALE

Ok, but my constraint is still disabled. New DML will not be verified. The RELY concerns only current data. So what happens if I insert a row in EMP that has no matching DEPT?


SQL> insert into EMP (EMPNO,ENAME,DEPTNO) values (9999,'rebel',50);
1 row created.
 
SQL> commit;
Commit complete.


SQL> delete from REWRITE_TABLE;
2 rows deleted.

SQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');
PL/SQL procedure successfully completed.

SQL> select sequence,message from REWRITE_TABLE  order by statement_id,sequence;

  SEQUENCE MESSAGE
---------- ------------------------------------------------------------
         1 QSM-01150: query did not rewrite
         2 QSM-01031: materialized view, TEST_MV, is stale in TRUSTED i
           ntegrity mode

Because changes have been done, the materialized view is stale. We need to lower the query rewrite security level if we still want query rewrite:


SQL> alter session set query_rewrite_integrity=stale_tolerated;
Session altered.

SQL> delete from REWRITE_TABLE;
2 rows deleted.

SQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');
PL/SQL procedure successfully completed.

SQL> select sequence,message from REWRITE_TABLE  order by statement_id,sequence;

  SEQUENCE MESSAGE
---------- ------------------------------------------------------------
         1 QSM-01151: query was rewritten
         2 QSM-01033: query rewritten with materialized view, TEST_MV

But then the select with query rewrite will not show all rows, because the row I’ve inserted into EMP is not in the join result.

ENFORCED

So trusted may show wrong results when the constraint is not enabled. You need to enforce query rewrite integrity to avoid that.


SQL> alter session set query_rewrite_integrity=enforced;
Session altered.

SQL> delete from REWRITE_TABLE;
2 rows deleted.

SQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');
PL/SQL procedure successfully completed.

SQL> select sequence,message from REWRITE_TABLE  order by statement_id,sequence;

  SEQUENCE MESSAGE
---------- ------------------------------------------------------------
         1 QSM-01150: query did not rewrite
         2 QSM-01110: query rewrite not possible with materialized view
            TEST_MV because it contains a join between tables (EMP and
           DEPT) that is not present in the query and that potentially
           eliminates rows needed by the query
         3 QSM-01052: referential integrity constraint on table, EMP, n
           ot VALID in ENFORCED integrity mode

In this case, query rewrite do not occur because the foreign key is not enforced by oracle (not enabled).

Conclusion

Query rewrite depends on referential integrity. When query_rewrite_integrity=enforced, the referential integrity must be enforced by oracle (constraint is validated and enabled). When query_rewrite_integrity=trusted, you are responsible about data integrity, and control whether rewrite can rely on your constraint, and even tolerate stale materialized view.

Don’t forget to call dbms_mview.explain_rewrite after creating the rewrite_table with utlxrw.sql

Post Views: 1,105

OCM 12c preparation: Materialized View explain_rewrite

By Franck Pachot

SCOTT

Create materialized view

Explain rewrite

Without ANSI join

Disabled constraint

RELY constraint

STALE

ENFORCED

Conclusion

Oracle Team

Leave a Reply:

Related blog articles

By Franck Pachot

SCOTT

Create materialized view

Explain rewrite

Without ANSI join

Disabled constraint

RELY constraint

STALE

ENFORCED

Conclusion

Oracle Team

Leave a Reply:

Related blog articles

Oracle Database on Apple Silicon with Virtualbox 7.1

Convert Single Instance database to RAC on an ODA

DPR and Dbvisit Standby on ODA

PostgreSQL 17: Enhancing JSON Support for Web Developers