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 did not rewrite
QSM-01001: query rewrite not enabled
Well we have made a simple mistake – parameter QUERY_REWRITE_ENABLED was not set to TRUE!
Let us set the parameter QUERY_REWRITE_ENABLE to TRUE and will see that running the same EXPLAIN_MVIEW procedure will show that query redirection in fact occur.
SQL> SELECT message FROM rewrite_table ORDER BY sequence;
MESSAGE
——————————————————————————–
QSM-01151: query was rewritten
QSM-01209: query rewritten with materialized view, SALES_MV, using text match algorithm
Let’s now see what happens if we alter our SQL query as shown below. Query Rewrite does not happen in this case and w ecan see the reasons why.
SQL> truncate table rewrite_table;
Table truncated.
SQL> 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 NOT LIKE ”A%” –
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 did not rewrite
QSM-01112: WHERE clause of mv, SALES_MV, is more restrictive than query
QSM-01052: referential integrity constraint on table, PRODUCTS, not VALID in ENFORCED integrity mode
Another package TUNE_MVIEW can also help us in another way by actually rewriting the CREATE MATERIALIZED VIEW statement for us to enable things like Fast Refreshes and also Query Rewrites.
Let us suppose we have a materialized view CUST_MV defined with a fast refresh and we then go an update some rows on the base table. When we try and refresh fast that MV, we will see that we are faced with an ORA-32314 error which states that a Refresh Fast is not supported on this MV after deletes/updates.
CREATE MATERIALIZED VIEW cust_mv
REFRESH FAST
ENABLE QUERY REWRITE AS
SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id
GROUP BY s.prod_id, s.cust_id;
SQL> update sales set QUANTITY_SOLD=100 where prod_id=13;
6002 rows updated.
SQL> commit;
Commit complete.
SQL> exec dbms_mview.refresh(‘CUST_MV’);
BEGIN dbms_mview.refresh(‘CUST_MV’); END;
*
ERROR at line 1:
ORA-32314: REFRESH FAST of “SH”.”CUST_MV” unsupported after deletes/updates
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2566
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2779
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2748
ORA-06512: at line 1
Let us now see how the TUNE_MVIEW package will help us to recreate the MV with a different definition which will allow for fast refreshes.
Define two variables – one for the Advisor task name and another for MV DDL text.
SQL> VARIABLE task_cust_mv VARCHAR2(30);
SQL> VARIABLE create_mv_ddl VARCHAR2(4000);
SQL> EXECUTE :task_cust_mv := ‘TEST_TUNE_MV’;
PL/SQL procedure successfully completed.
SQL> EXECUTE :create_mv_ddl := ‘-
CREATE MATERIALIZED VIEW cust_mv-
REFRESH FAST-
ENABLE QUERY REWRITE AS –
SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount-
FROM sales s, customers cs-
WHERE s.cust_id = cs.cust_id-
GROUP BY s.prod_id, s.cust_id’;
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
PL/SQL procedure successfully completed.
We can redirect the output to a script as well. In this case we have a directory DATA_PUMP_DIR which points to an OS location and we specify the script which should be created.
Note – usage of the DBMS_ADVISOR package will require the system privilege ADVISOR to be granted to the user executing the package.
SQL> EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), –
> ‘DATA_PUMP_DIR’,’create_mv.sql’);
PL/SQL procedure successfully completed.
We can either view the recommendations via the view *_TUNE_MVIEW or from the generated SQL script file.
SQL> set long 500000
SQL> SELECT STATEMENT FROM USER_TUNE_MVIEW
WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE=’IMPLEMENTATION’;
2
STATEMENT
——————————————————————————–
CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FAST WITH ROWID ENABLE QUERY REWRI
TE AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM(“SH”.”SALES”.”AMOUNT_
SOLD”) M1, COUNT(“SH”.”SALES”.”AMOUNT_SOLD”) M2, COUNT(*) M3 FROM SH.CUSTOMERS,
SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID
, SH.SALES.PROD_ID
SQL> !cat /u01/app/oracle/admin/11gdba/dpdump/create_mv.sql
Rem SQL Access Advisor: Version 11.2.0.2.0 – Production
Rem
Rem Username: SH
Rem Task: TEST_TUNE_MV
Rem Execution date:
Rem
CREATE MATERIALIZED VIEW SH.CUST_MV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM(“SH”.”SALES”.”AMOUNT_SOLD”)
M1, COUNT(“SH”.”SALES”.”AMOUNT_SOLD”) M2, COUNT(*) M3 FROM SH.CUSTOMERS,
SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID,
SH.SALES.PROD_ID;
Now we will drop the MV and recreate it using the script provided by the Access Advisor. We will then make an update to the SALES table and see that even after the update, Fast Refresh of the MV is now possible and we do not get the same error as before.
SQL> drop materialized view SH.CUST_MV;
Materialized view dropped.
SQL> CREATE MATERIALIZED VIEW SH.CUST_MV
2 REFRESH FAST WITH ROWID
3 ENABLE QUERY REWRITE
4 AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM(“SH”.”SALES”.”AMOUNT_SOLD”)
5 M1, COUNT(“SH”.”SALES”.”AMOUNT_SOLD”) M2, COUNT(*) M3 FROM SH.CUSTOMERS,
6 SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID,
7 SH.SALES.PROD_ID;
Materialized view created.
SQL> update sales set quantity_sold=10 where prod_id=13;
6002 rows updated.
SQL> commit;
Commit complete.
SQL> exec dbms_mview.refresh(‘CUST_MV’,’FAST’);
PL/SQL procedure successfully completed
What has happened to our other MV SALES_MV because we had updated the base table SALES. Let us see if the query rewrite is still happening.
SQL> 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 did not rewrite
QSM-01106: materialized view, SALES_MV, is stale with respect to some partition(s) in the base table(s)
QSM-01052: referential integrity constraint on table, PRODUCTS, not VALID in ENFORCED integrity mode
QSM-01029: materialized view, SALES_MV, is stale in ENFORCED integrity mode
Let us try the same after we have refreshed the MV. We will see that the query rewrite has indeed happened and this is confirmed by running the EXPLAIN_REWRITE procedure as well as by looking the output of the EXPLAIN PLAN
SQL> exec dbms_mview.refresh(‘SALES_MV’);
PL/SQL procedure successfully completed.
SQL> 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-01151: query was rewritten
QSM-01209: query rewritten with materialized view, SALES_MV, using text match algorithm
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
————————————————————————————————————————
Plan hash value: 3876866481
—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 40 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_MV | 1 | 40 | 3 (0)| 00:00:01 |
—————————————————————————————–
Great! Thanks for the share!
Great goods from you, man. I have understand your stuff prior to and you’re simply extremely wonderful. I actually like what you’ve obtained here, certainly like what you’re stating and the best way through which you assert it. You make it enjoyable and you still care for to stay it smart. I cant wait to learn far more from you. That is really a terrific web site.
I’m not that much of a online reader to be honest but your blogs really nice, keep it up! I’ll go ahead and bookmark your website to come back down the road. All the best
I loved as much as you’ll receive carried out right here. The sketch is attractive, your authored material stylish. nonetheless, you command get bought an impatience over that you wish be delivering the following. unwell unquestionably come further formerly again as exactly the same nearly very often inside case you shield this increase.
I’ve not checked in for some time for I believed it was obtaining dull, however the previous handful of articles are really good superior and so I guess I am going to lend a person to my day by day bloglist. You deserve this my friend.
Hey. Thank you for writing this article. It helped me alot.
thanks