Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

Materialized Views overview

We are now ready to examine the use of Oracle materialized views (MVs).  When accessing data against any relational database, the database developer is faced with a quandary of aggregation.  As we know, extracting complex summaries and aggregations from a database can clause repeated large-table full-table scans against the database.  For very large systems, these kinds of large queries can run for many hours.
 
So how can we provide sub-second response time when the queries may run for hours?
 
The answer is with Oracle materialized views.  An Oracle materialized view allows us to pre-summarize information and store inside Oracle tables.  With Oracle's query rewrite facility enabled, Oracle will detect queries that can use the materialized views and automatically re-write the SQL to reference the materialized view.
 
The query optimizer can use materialized views by automatically recognizing when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables, which should result in a significant performance gain.
 
This revolutionary technique can be used to take ordinary SQL queries down from hours to sub-second response time. This illusion of instantaneous response time is achieved by pre-summarizing the data.
 
However, there is a downside to materialized views.  Because the materialized views are derived from subsets of the Oracle data, the information and the materialized views may become stale as soon as additional data is added into our database. 
 
Hence, the Oracle database provides a refresh mechanism for materialized views.  The Oracle professional can specify that the materialized views be refreshed instantly, every 10 minutes, every day, and so on, depending on the volatility of the data.  Here is an example:
 
CREATE MATERIALIZED VIEW emp_sum
ENABLE QUERY REWRITE
AS SELECT deptno,job,SUM(sal)
      FROM emp
GROUP BY deptno,job
 
 
;
Materialized View Created.
 
In the above example, the MV is re-created every 1/24 of a day (once per hour).
 
This refresh interval gives the database developer complete control over the refresh interval for the materialized views, and allows them to take long-running expensive SQL queries and make then run super-fast.
 
Prior to Oracle8i, DBAs who have used summaries spent a significant amount of time manually to create them, identifying which ones to create, index them, update them, and advise their users on which ones to use.
 
To see how MV’s work, here is an actual example of a manual aggregation from a real data warehouse:
 
Once the MV is defined, Oracle will automatically check the data dictionary to see of an MV can be used to service the query.
 
 
If so, Oracle will dynamically re-write your query to reference the MV, and you will be able to see this change when you gather the execution plan for your SQL.

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.