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;