This is one of those notes that I thought I’d written years ago. It answers two questions:
- what can I do with my materialized view?
- why isn’t the optimizer using my materialized view for query rewrite?
I’ve actually supplied an example of code to address the first question as a throwaway comment in a blog that dealt with a completely different problem, but since the two questions above go together, and the two answers depend on the same package, I’m going to repeat the first answer.
The reason for writing this note now is that the question “why isn’t this query using my materialized view” came up on the Oracle Developer community forum a few days ago – and I couldn’t find the article that I thought I’d written.
Note: a couple of days after I started drafting this note Frank Pachot tweeted the links to a couple of extensive posts on materialized views that included everything I had to say (and more) about “what can my materialized view do”. Fortunately he didn’t get on to second question – so I decided to publish the whole of my note anyway as the two questions go well together.
The key feature is the dbms_mview package, and the two procedures (both overloaded) explain_mview() and explain_rewrite(). To quote the 12.2 “PL/SQL Supplied Packages” manual page, the first procedure:
“explains what is possible with a materialized view or potential [ed: my emphasis] materialized view” –
note particularly that the materialized view doesn’t need to have been created before you run the package – the second procedure:
“explains why a query failed to rewrite or why the optimizer chose to rewrite a query with a particular materialized view or materialized views”.
Here’s the relevant extract from the SQL*Plus describe of the package –
PROCEDURE EXPLAIN_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MV VARCHAR2 IN STMT_ID VARCHAR2 IN DEFAULT PROCEDURE EXPLAIN_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MV CLOB IN STMT_ID VARCHAR2 IN DEFAULT PROCEDURE EXPLAIN_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MV VARCHAR2 IN MSG_ARRAY EXPLAINMVARRAYTYPE IN/OUT PROCEDURE EXPLAIN_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MV CLOB IN MSG_ARRAY EXPLAINMVARRAYTYPE IN/OUT PROCEDURE EXPLAIN_REWRITE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- QUERY VARCHAR2 IN MV VARCHAR2 IN DEFAULT STATEMENT_ID VARCHAR2 IN DEFAULT PROCEDURE EXPLAIN_REWRITE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- QUERY CLOB IN MV VARCHAR2 IN DEFAULT STATEMENT_ID VARCHAR2 IN DEFAULT PROCEDURE EXPLAIN_REWRITE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- QUERY VARCHAR2 IN MV VARCHAR2 IN DEFAULT MSG_ARRAY REWRITEARRAYTYPE IN/OUT PROCEDURE EXPLAIN_REWRITE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- QUERY CLOB IN MV VARCHAR2 IN DEFAULT MSG_ARRAY REWRITEARRAYTYPE IN/OUT
As you can see there are 4 overloaded versions of explain_mview() and 4 of explain_rewrite(): both procedures take an input of an SQL statement (parameter mv for explain_mv(), parameter query for explain_rewrite()) – which can be either a varchar2() or a CLOB, and both procedures supply an output which can be written to a table or written to an “in/out” pl/sql array.
Two possible input options times two possible output options gives 4 overloaded versions of each procedure. In this note I’ll restrict myself to the first version of the two procedures – varchar2() input, writing to a pre-created table.
Here’s a simple demo script. Before we do anything else we need to call a couple of scripts in the $ORACLE_HOME/rdbms/admin directory to create the target tables. (If you want to do something a little clever you could tweak the scripts to create them as global temporary tables in the sys schema with a public synonym – just like the plan_table used in calls to explain plan.)
rem
rem Script: c_explain_mv.sql
rem Author: Jonathan Lewis
rem Dated: March 2002
rem
@$ORACLE_HOME/rdbms/admin/utlxmv.sql
@$ORACLE_HOME/rdbms/admin/utlxrw.sql
-- @$ORACLE_HOME/sqlplus/demo/demobld.sql
create materialized view dept_cost
refresh complete on demand
enable query rewrite
as
select
d.deptno,sum(e.sal)
from
emp e,dept d
where
e.deptno = d.deptno
group by
d.deptno
;
set autotrace traceonly explain
select
d.deptno,sum(e.sal)
from
emp e,dept d
where
e.deptno = d.deptno
and d.deptno=10
group by
d.deptno
;
set autotrace off
/*
Execution Plan
----------------------------------------------------------
Plan hash value: 3262931184
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| DEPT_COST | 1 | 7 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPT_COST"."DEPTNO"=10)
*/
My original script is rather old and in any recent version of Oracle the Scott tables are no longer to be found in demobld.sql, so I’ve added them at the end of this note.
After creating the emp and dept tables I’ve created a materialized view and then enabled autotrace to see if a suitable query will use the materialized view – and as you can see from the execution plan the materialized view can be used.
So let’s do a quick analysis of the view and the rewrite:
column audsid new_value m_audsid
select sys_Context('userenv','sessionid') audsid from dual;
begin
dbms_mview.explain_mview(
-- mv => 'DEPT_COST',
mv => q'{
create materialized view dept_cost
refresh complete on demand
enable query rewrite
as
select
d.deptno,sum(e.sal)
from
emp e,dept d
where
e.deptno = d.deptno
group by
d.deptno
}',
stmt_id => '&m_audsid'
);
end;
/
set linesize 180
column cap_class noprint
column capability_name format a48
column related_text format a15
column short_msg format a90
break on cap_class skip 1
select
substr(capability_name,1,3) cap_class,
capability_name, possible, related_text, msgno, substr(msgtxt,1,88) short_msg
from
mv_capabilities_table
where
mvname = 'DEPT_COST'
and statement_id = '&m_audsid'
order by
substr(capability_name,1,3), related_num, seq
;
I’ve captured the session’s audsid because the mv_capabilities_table table and the rewrite_table table both have a statement_id column and the audsid is a convenient value to use to identify the most recent data you’ve created if you’re sharing the table. Then I’ve called dbms_mview.explain_mview() indicating two possible strategies (with one commented out, of course).
I could pass in a materialized view name as the mv parameter, or I could pass in the text of a statement to create a materialized view (whether or not I have previously created it). As you can see, I’ve also used a substitution variable to pass in my audsid as the statement id.
After setting up a few SQL*Plus format options I’ve then queried some of the columns from the mv_capabilitie_table table, with the following result:
CAPABILITY_NAME P RELATED_TEXT MSGNO SHORT_MSG ------------------------------------------------ - --------------- ---------------------------------------- PCT_TABLE N EMP 2068 relation is not a partitioned table PCT_TABLE_REWRITE N EMP 2068 relation is not a partitioned table PCT_TABLE N DEPT 2068 relation is not a partitioned table PCT_TABLE_REWRITE N DEPT 2068 relation is not a partitioned table PCT N REFRESH_FAST_AFTER_ONETAB_DML N SUM(E.SAL) 2143 SUM(expr) without COUNT(expr) REFRESH_COMPLETE Y REFRESH_FAST N REFRESH_FAST_AFTER_INSERT N TEST_USER.EMP 2162 the detail table does not have a materialized view log REFRESH_FAST_AFTER_INSERT N TEST_USER.DEPT 2162 the detail table does not have a materialized view log REFRESH_FAST_AFTER_ONETAB_DML N 2146 see the reason why REFRESH_FAST_AFTER_INSERT is disabled REFRESH_FAST_AFTER_ONETAB_DML N 2142 COUNT(*) is not present in the select list REFRESH_FAST_AFTER_ONETAB_DML N 2143 SUM(expr) without COUNT(expr) REFRESH_FAST_AFTER_ANY_DML N 2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled REFRESH_FAST_PCT N 2157 PCT is not possible on any of the detail tables in the materialized view REWRITE Y REWRITE_FULL_TEXT_MATCH Y REWRITE_PARTIAL_TEXT_MATCH Y REWRITE_GENERAL Y REWRITE_PCT N 2158 general rewrite is not possible or PCT is not possible on any of the detail tables 20 rows selected.
If you want the complete list of possible messages, the msgno is interpreted from $ORACLE_HOME/rdbms/mesg/qsmus.msg (which is shared with the explain_rewrite() procedure. Currently (19.3) it holds a slightly surprising 796 messages. A call to “oerr qsm nnnnn” will translate the number to the message text.
In a similar vein we can call dbms_mview.explain_rewrite(). Before we do so I’m going to do something that will stop the rewrite from taking place, then call the procedure:
update emp set ename = 'JAMESON' where ename = 'JAMES';
commit;
begin
dbms_mview.explain_rewrite (
query => q'{
select
d.deptno,sum(e.sal)
from
emp e,dept d
where
e.deptno = d.deptno
and d.deptno=10
group by
d.deptno
}',
mv => null,
statement_id => '&m_audsid'
);
end;
/
column message format a110
select
sequence, message
from
rewrite_table
where
statement_id = '&m_audsid'
order by
sequence
/
You’ll notice that there’s an input parameter of mv – there may be cases where the optimizer has a choice of which materialized view to use to rewrite a query, you could supply the name of a materialized view for this parameter to find out why Oracle didn’t choose the materialized view you were expecting. (In this case mv has to supply a materialized view name, not the text to create a materialized view)
In my example I get the following results:
SEQUENCE MESSAGE
---------- --------------------------------------------------------------------------------------------------------------
1 QSM-01150: query did not rewrite
2 QSM-01106: materialized view, DEPT_COST, is stale with respect to some partition(s) in the base table(s)
3 QSM-01029: materialized view, DEPT_COST, is stale in ENFORCED integrity mode
3 rows selected.
The 2nd and 3rd messages are a bit of a clue – so let’s change the session’s query_rewrite_integrity parameter to stale_tolerated and re-execute the procedure; which gets us to:
SEQUENCE MESSAGE
---------- --------------------------------------------------------------------------------------------------------------
1 QSM-01151: query was rewritten
2 QSM-01033: query rewritten with materialized view, DEPT_COST
Footnote:
You’ll notice that I’ve quote-escaping in my inputs for the materialized view definition and query. This is convenience that let’s me easily cut and paste a statement into the scripts – or even use the @@script_name mechanism – writing a query (without a trailing slash, semi-colon, or any blank lines) in a separate file and then citing the script name between the opening and closing quote lines, e.g.
begin
dbms_mview.explain_rewrite (
query => q'{
@@test_script
}',
mv => null,
statement_id => '&m_audsid'
);
end;
/
It’s worth noting that the calls to dbms_mview.explain_mv() and dbms_mview.explain_rewrite() don’t commit the rows they write to their target tables, so you ought to include a rollback; at the end of your script to avoid any confusion as you test multiple views and queries.
Footnote:
Here’s the text of the demobld.sql script as it was when I copied it in the dim and distant past. I’m not sure which version it came from – but I don’t think it’s the original v4/v5 release which I’m fairly sure had only the emp and dept tables. (For reference, and hash joins, there used to be a MOS note explaining that EMP was the big table and DEPT was the small table ;)
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
insert into emp values
(7369, 'SMITH', 'CLERK', 7902,
to_date('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
insert into emp values
(7499, 'ALLEN', 'SALESMAN', 7698,
to_date('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
insert into emp values
(7521, 'WARD', 'SALESMAN', 7698,
to_date('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
insert into emp values
(7566, 'JONES', 'MANAGER', 7839,
to_date('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
insert into emp values
(7654, 'MARTIN', 'SALESMAN', 7698,
to_date('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
insert into emp values
(7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
insert into emp values
(7782, 'CLARK', 'MANAGER', 7839,
to_date('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
insert into emp values
(7788, 'SCOTT', 'ANALYST', 7566,
to_date('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
insert into emp values
(7839, 'KING', 'PRESIDENT', NULL,
to_date('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
insert into emp values
(7844, 'TURNER', 'SALESMAN', 7698,
to_date('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
insert into emp values
(7876, 'ADAMS', 'CLERK', 7788,
to_date('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
insert into emp values
(7900, 'JAMES', 'CLERK', 7698,
to_date('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
insert into emp values
(7902, 'FORD', 'ANALYST', 7566,
to_date('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
insert into emp values
(7934, 'MILLER', 'CLERK', 7782,
to_date('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
CREATE TABLE DEPT(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
);
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER
);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
