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

Home
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

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
   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

 

 

 

 

 
 
 

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.