Explain Why An Oracle Database Materialized View Does Not Rewrite

Explain Why An Oracle Database Materialized View Does Not Rewrite

Get Social!

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.

Setup

First, create the table to store the output created by the DBMS_MVIEW.EXPLAIN_REWRITE package:

Rewrite output

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.

Finally, view the result


Related posts:


Leave a Reply

Visit our advertisers

Search

Quick Poll

How many Proxmox servers do you work with?

Visit our advertisers