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
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JDU_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/ALN_web-min-scaled.jpg)