Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

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
build immediate
refresh complete
enable query rewrite
   sum(sales) sum_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


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






Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.