Oracle Enabling Materialized Views

Remote DBA

Remote DBA Plans

Oracle:

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

Burleson is the American Team

American Flag

BC Remote Oracle Support
P.O. Box 511 • Kittrell, NC, 27544

Remote DBA

Remote DBA Services