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