In the fairy tale, The Shoemaker and The Elves, a poor cobbler works to the best of his ability; and somehow, when he isn’t looking, magical beings would sneak in and polish and perfect his handiwork.

It seems the people at Oracle are up on their fairy tales. Perfectly-tuned queries are the Holy Grail to developers and DBAs; we are constantly fiddling, forever optimising, shaving milliseconds from the total run time. Well, since 8i, functionality has been in place that can take our DML statements and magically improve them.

Oracle do not admit that this involves any elves. But I have my suspicions.

How It Works

Here’s how it works: part of the reason queries involving joins between data-heavy tables can take such a long time is that the engine needs to compute aggregates and disentangle the joins between the tables before it can run the query. However, materialized views, by their very nature, have all that information precomputed. Therefore, if you can find the right materialized view and match it to the right query, you can cut running times dramatically.

This trick, called Query Rewrite, only works with SELECT statements. However, those statements may be hidden away in a CREATE TABLE … AS SELECT statement, or an INSERT INTO … SELECT statement, or they may be squirrelled away in any type of subquery or sub-clause.

Additionally, Query Rewrite must be enabled at the initialization parameter level (

Alter session set query_rewrite_enabled = TRUE;

) and within the materialized view itself by including the ENABLE QUERY REWRITE clause while creating the view, or by altering an existing materialized view to add it.


Did It Work?

One question remains, however: how do you know that query rewrite has even worked, that it has made a difference? The beautiful thing about Query Rewrite is that it is a low-cost way of improving the performance of slow, creaky application code without the nightmare of re-engineering the whole thing. If you create the right materialized views you could see a huge change in performance immediately. That’s the proof: your slow query suddenly outpaces Usain Bolt.

However, if you require further proof that Query Rewrite has kicked in, there are tools available to you. From 10g, you can simply run an EXPLAIN PLAN for your query; if a query rewrite has taken place, it will tell you which materialized views were used.

Alternatively, you can call the DBMS_MVIEW.EXPLAIN_REWRITE procedure. This procedure takes as parameters your query (as a string), the name of your materialized view (this is optional; if you do not supply a name it will check the query against all existing materialized views to see if they will speed it up), and an ID for the statement. It will then output its findings – if the query can be rewritten, why it cannot be rewritten (if it cannot be), or which materialized view will be used in the rewrite (if it can be) – into the EXPLAIN_REWRITE table (which can be created by running the utlxrw.sql script).

query VARCHAR2 The query you wish to speed up
Mv VARCHAR2 Optional. The view to check the query against.
Statement_id VARCHAR2 An identifier for this test.


How To Make It Work:

If it is not possible to rewrite your query using any of your existing materialized views, Oracle has a tool which will suggest brand new materialized views to create. And it will even provide you with a script to create the materialized view it is recommending, if you ask it nicely enough.  This tool is called DBMS_ADVISOR.QUICK_TUNE.

Advisor_name VARCHAR2 Advisor to perform analysis. Use the constant DBMS_ADVISOR.SQLACCESS_ADVISOR
Task_name VARCHAR2 An identifier for your task
Attr1 CLOB In this case, your query.


To view the recommendations of  QUICK_TUNE in a text file, we will need to return to the DBMS_ADVISOR package and call up two further methods.


Where ‘taskName’ is the name we gave the task in QUICK_TUNE, ‘Directory’ is the name of an existing Oracle directory, and ‘Filename.sql’ is, of course, the name of a file.

After running QUICK_TUNE and its sidekicks, we will be left with a file that contains scripts to create any materialized views (or indexes) that you may need to speed up the parameterized query. You may want to edit the file, as Oracle will give any objects generic names.


This introduction to the alchemy of Oracle’s query rewrite is by no means exhaustive, but it should be enough to get you started. Query rewrite is one of those rare, magical tools: it is easily implemented, it is low-cost (there is no database cost in creating materialized views), it can be used to retrospectively improve pre-existing code. And, as if that wasn’t impressive enough, Oracle supply us with a procedure, DBMS_ADVISOR.QUICK_TUNE, that actually creates the needed materialized views for us!