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

 

   
  Oracle Concepts by Burleson Consulting

Materialized Views

We are now ready to examine the use of Oracle materialized views (MVs).  When accessing data against any relational database, the Oracle database developer is faced with a quandary of aggregation.  As we know, extracting complex summaries and aggregations from a Oracle database can clause repeated large-table full-table scans against the Oracle 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 Oracle 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 Oracle 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.

Enabling Materialized Views

However, we must make the following changes to Oracle to enable MV’s in your system:

Grant the following Oracle privileges:

grant query rewrite to pubs;
grant create materialized view to pubs;
alter session set query_rewrite_enabled = true;

Set the following Oracle parameters:

optimizer_mode = choose
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced

Let’s take a close look at how this works.  In the following two examples, we will pre-aggregate data and pre-join table together.  Here is a working example showing SQL query re-write in action:

1. First we create the MV to store department, job and salary summaries in an MV called emp_sum

CREATE MATERIALIZED VIEW emp_sum
ENABLE QUERY REWRITE
AS
SELECT
   deptno,
   job,
   SUM(sal)
FROM
   emp
GROUP BY
   deptno,job;

2. Next, we Create Optimizer Statistics and Refresh the Materialized View:

execute dbms_utility.analyze_schema('SCOTT','ESTIMATE');

execute dbms_mview.refresh('emp_sum');


3. Now we can test our MV to ensure that it is using the MV:

set autotrace traceonly explain

-- Test the Materialized View:

set autotrace on explain
SELECT
   deptno,
   job,
   SUM(sal)
FROM
   emp
GROUP BY
   deptno, job;


Execution Plan
-----------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP_SUM'

Above, we see that the Oracle database had dynamically changed the execution plan to reference our pre-aggregated summary, giving the end-user the illusion of instantaneous response time!

We also discussed in Tutorial 2 that it takes Oracle database resources to pre-join tables together.  The following example shops how we can pre-join two tables and automatically re-write any SQL queries to make them use our pre-joined table:

1 – First we create the Materialized View called emp_dept_sum to join the EMP and DEPT tables and summarize salaries:

CREATE MATERIALIZED VIEW emp_dept_sum
ENABLE QUERY REWRITE
AS
SELECT
   dname,
   job,
   SUM(sal)
FROM
   emp e,
   dept d
WHERE
   e.deptno = d.deptno
GROUP BY
   dname,job;

2 – Next, we create the SQL Optimizer Statistics and Refresh our new Materialized View:

execute dbms_utility.analyze_schema('SCOTT','ESTIMATE');

execute dbms_mview.refresh('emp_dept_sum');

3 – Now we can test the Materialized View and verify that the SQL is being re-written:

set autotrace on explain

SELECT
   dname,
   job,
   SUM(sal)
FROM
   emp e,
   dept d
WHERE
   e.deptno = d.deptno
GROUP BY
   dname,job;

Execution Plan
----------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP_DEPT_SUM'

In sum, we must recognize that Materialized views are one of the most important SQL tools in a relational database.  By pre-summarizing data and pre-joining tables, we can get dramatic speed improvements on SQL code, which accesses millions of table rows.

 

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

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.