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