H.Tonguç YILMAZ Blog

Overview

Materialized views are a data warehousing/decision support system tool that can increase by many orders of magnitude the speed of queries that access a large number of records. In basic terms, they allow a user to query potentially terabytes of detail data in seconds. They accomplish this by transparently using pre-computed summarizations and joins of data. These pre-computed summaries would typically be very small compared to the original source data.

Example

Prepare the large table BIGTAB:

create table bigtab
nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects
/

insert /*+ APPEND */ into bigtab
select * from bigtab;
commit;
insert /*+ APPEND */ into bigtab
select * from bigtab;
commit;
insert /*+ APPEND */ into bigtab
select * from bigtab;
commit;

analyze table bigtab compute statistics;
select count(*) from bigtab;

COUNT(*)
———
406008

Run query against this BIGTABLE, initially this quewry will require a full scan of the large table;

set autotrace on
set timing on
select owner, count(*) from bigtab group by owner;

Elapsed: 00:00:00.04

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=844 Card=20 Bytes=100)
1 0 HASH (GROUP BY) (Cost=844 Card=20 Bytes=100)
2 1 TABLE ACCESS (FULL) OF ‘BIGTAB’ (TABLE) (Cost=802 Card=406008 Bytes=2030040)

Statistics
———————————————————-
1 recursive calls
0 db block gets
2616 consistent gets
0 physical reads
0 redo size
575 bytes sent via SQL*Net to client
279 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed

Now let’s create the Materialized View;

grant query rewrite to user_name;
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

create materialized view mv_bigtab
build immediate
refresh on commit
enable query rewrite
as
select owner, count(*)
from bigtab
group by owner
/

analyze table mv_bigtab compute statistics;

Basically, what we’ve done is pre-calculate the object count, and define this summary information as a materialized view. We have asked that the view be immediately built and populated with data. You’ll notice that we have also specified REFRESH ON COMMIT and ENABLE QUERY REWRITE. Also notice that we may have created a materialized view, but when we ANALYZE, we are analyzing a table. A materialized view creates a real table, and this table may be indexed, analyzed, and so on.

set timing on
set autotrace traceonly
select owner, count(*)
from bigtab
group by owner;
set autotrace off
set timing off

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=20 Bytes=200)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF ‘MV_BIGTAB’ (MAT_VIEW REWRITE) (Cost=4 Card=20 Bytes=200)

Statistics
———————————————————-
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
590 bytes sent via SQL*Net to client
279 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed

When the SELECT OWNER, … query is issued, the database(optimizer) automatically directs it to the materialized view. No physical I/O this time around as the data was found in the cache. Our buffer cache will be much more efficient now as it has less to cache.

Now, add a new row to the BIGTAB table and commit the change

insert into bigtab
(owner, object_name, object_type, object_id)
values (’Owner‘, ‘Object_name’, ‘object_type’, 1111111);

commit;

select owner, count(*)
from bigtab
where owner = ‘Owner‘
group by owner;

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=10)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF ‘MV_BIGTAB’ (MAT_VIEW REWRITE) (Cost=4 Card=1 Bytes=10)

Statistics
———————————————————-
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
253 bytes sent via SQL*Net to client
272 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

The analysis shows that we scanned the materialized view MV_BIGTAB and found the new row. By specifying REFRESH ON COMMIT in our original definition of the view, we requested that Oracle maintain synchronization between the view and the details, the summary will be maintained as well.

Uses of Materialized Views is relatively straightforward and is answered in a single word - performance. By calculating the answers to the really hard questions up front (and once only), we will greatly reduce the load on the machine, We will experience:

= Less physical reads - There is less data to scan through.
= Less writes - We will not be sorting/aggregating as frequently.
= Decreased CPU consumption - We will not be calculating aggregates and functions on the data, as we will have already done that.
= Markedly faster response times - Our queries will return incredibly quickly when a summary is used, as opposed to the details. This will be a function of the amount of work we can avoid by using the materialized view, but many orders of magnitude is not out of the question.

Reference

http://www.akadia.com/services/ora_materialized_views.html