Donald K. Burleson Oracle Tips

Materialized views and query re-write

Oracle8 has a special feature called materialized views at can greatly speed-up data warehouse queries.  In a materialized view, a summary table is created from a base table, and all queries that perform a similar summation against the base table will be transparently re-written to reference the pre-built summary table.

Below is a simple example.  We begin by creating a materialized view that sums sales data.

create materialized view
   sum_sales
build immediate
refresh complete
enable query rewrite
as
select
   product_nbr,
   sum(sales) sum_sales
from
   sales;

Now, when we have any query that summarizes sales, that query will be dynamically re-written to reference the summary table.

alter session set query_rewrite_enabled=true;
set autotrace on

select
   sum(sales)
from  
   sales
;

In the execution plan for this query we see that the sum_sales table is being referenced.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=83)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'SUM_SALES' (Cost=1 Card=423 Bytes=5342)

If you use bind variables in a query, the query will be not be rewritten to use materialized views even if you have enabled query rewrite.

Once the query re-write feature is enabled, you can use standard SQL hints to force the SQL parser to re-write the query.

select /*+RRWRITE(sales)*/
...

As Oracle SQL evolves and becomes more sophisticated there will be more cases where the SQL parser will re-write queries into a more efficient form

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm