Oracle DBA – Tips and Techniques

We can use the DBMS_MVIEW.EXPLAIN_REWRITE package (which was first introduced in Oracle 9i if memory serves me right) to diagnose why Query redirection to a materialized view is not happening.

We can redirect the output to a table called REWRITE_TABLE which is created via the utlxrw.sql script located under $ORACLE_HOME/rdbms/admin.

Let us take a simple example to see how this works.

We have created a materialized view SALES_MV which is based on the following query in the SH schema:

create materialized view sales_mv
build immediate
enable query rewrite
as
SELECT p.prod_name, SUM(amount_sold)
FROM sales s, products p
WHERE s.prod_id = p.prod_id
AND prod_name > ‘B%’
AND prod_name < 'C%'
GROUP BY prod_name;

We will now run the EXPLAIN_REWRITE procedure to see if a particular query will be redirected to the MV instead.

EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE -
(‘SELECT p.prod_name, SUM(amount_sold)-
FROM sales s, products p -
WHERE s.prod_id = p.prod_id -
AND prod_name > ”B%”-
AND prod_name < ''C%''-
GROUP BY prod_name',-
'SALES_MV','SH');

PL/SQL procedure successfully completed.

SQL> SELECT message FROM rewrite_table ORDER BY sequence;

MESSAGE
——————————————————————————–
QSM-01150: query

Continue reading Using TUNE_MVIEW and EXPLAIN_REWRITE to enable us to tune MV Fast Refreshes and Query Rewrites