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;
Continue reading...
No comments:
Post a Comment