Category : How-to
Using Oracle Database materialized views for query rewrite, when used in the right way, can really help aid performance with specific queries. It’s one of my favorite ways to quickly help increase performance of specific queries – often queries issued by front end reporting tools.
Materialized views, however, can be finicky in which queries they re-write, if any and the optimiser output doesn’t give away many clues.
Luckily, help is at hand with a small amount of setup and the DBMS_MVIEW.EXPLAIN_REWRITE package.
First, create the table to store the output created by the DBMS_MVIEW.EXPLAIN_REWRITE package:
CREATE TABLE REWRITE_TABLE( statement_id VARCHAR2(30), -- id for the query mv_owner VARCHAR2(30), -- owner of the MV mv_name VARCHAR2(30), -- name of the MV sequence INTEGER, -- sequence no of the msg query VARCHAR2(2000), -- user query query_block_no INTEGER, -- block no of the current subquery rewritten_txt VARCHAR2(2000), -- rewritten query message VARCHAR2(512), -- EXPLAIN_REWRITE msg pass VARCHAR2(3), -- rewrite pass no mv_in_msg VARCHAR2(30), -- MV in current message measure_in_msg VARCHAR2(30), -- Measure in current message join_back_tbl VARCHAR2(30), -- Join back table in message join_back_col VARCHAR2(30), -- Join back column in message original_cost INTEGER, -- Cost of original query rewritten_cost INTEGER, -- Cost of rewritten query flags INTEGER, -- associated flags reserved1 INTEGER, -- currently not used reerved2 VARCHAR2(10)) -- currently not used;
Once the output table is in place we can run the DBMS_MVIEW.EXPLAIN_REWRITE package to begin to understand why queries are/ are not being rewritten.
Run the package and substitute the following parameters:
- arg0 is the query you’re trying to re-write with the materialized view.
- arg1 is the materialized view itself.
declare begin DBMS_MVIEW.EXPLAIN_REWRITE ( query=>q'[SELECT SUM(value) val FROM performance.bill_detail]', mv=>'performance_mvs.mv_bills'); end; /
Finally, view the result
SELECT * FROM REWRITE_TABLE