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.
query=>q'[SELECT SUM(value) val FROM performance.bill_detail]',
Finally, view the result
SELECT * FROM REWRITE_TABLE