Materialized Views
materialized views are mainly used in complex data warehousing environments, where the tables
grow so big that the data volume causes unacceptable performance problems. An important
property of data warehousing environments is that you don’t change the data very often.
Typically, there is a separate Extraction, Transformation, Loading (ETL)
process that updates the data warehouse contents.
Materialized views are also often used with distributed databases. In such
environments, accessing data over the network can become a performance bottleneck. You can
use materialized views to replicate data in a distributed database 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 (maybe many hundreds of thousands or millions) of
records. In basic terms, they allow a user to query potentially terabytes of detail data in seconds
(or less). 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
Say, for example, your company has a sales database loaded with the details of a million orders,
and you want to get a breakdown of sales by region (a common enough query).
Each and every record would be scanned, the data aggregated to the region level, and the
calculation performed. Using a materialized view, we can store a summary of the sales data by
region, and have the system maintain these summaries for us. If you have ten sales regions, this
summary will have ten records, so instead of sifting through a million detail
records, well query only ten. Furthermore, if someone asks a slightly different question,
say for the sales in a specific region, then that query can also obtain the answer from the
materialized view.
You will need a user account with the following privileges (at least):
• GRANT CREATE SESSION
• GRANT CREATE TABLE
• GRANT CREATE MATERIALIZED VIEW
• GRANT QUERY REWRITE
An Example
Well start with a large table that contains a list of owners of objects, and the objects they own.
This table is based on the ALL_OBJECTS data dictionary
view:
> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
65742 rows created.
> commit;
Commit complete.
> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
131484 rows created.
> commit
> commit;
Commit complete.
> analyze table my_all_objects compute statistics;
Table analyzed
> set autotrace on
> set timing on
> select owner, count(*) from my_all_objects group by owner;
OWNER COUNT(*)
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐
A 36
B 24
CTXSYS 2220
DBSNMP 48
DEMO 60
DEMO11 36
DEMO_DDL 108
MDSYS 2112
MV_USER 60
ORDPLUGINS 312
ORDSYS 2472
OUR_TYPES 12
OUTLN 60
PERFSTAT 636
PUBLIC 117972
SCHEDULER 36
SCOTT 84
SEAPARK 36
SYS 135648
SYSTEM 624
TESTING 276
TKYTE 12
TTS_USER 48
TYPES 36
24 rows selected.
Elapsed: 00:00:03.35
> set timing off
> set autotrace traceonly
> select owner, count(*) from my_all_objects group by owner;
24 rows selected.
Execution Plan
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2525 Card=24 Bytes=120)
1 0 SORT (GROUP BY) (Cost=2525 Card=24 Bytes=120)
2 1 TABLE ACCESS (FULL) OF MY_ALL_OBJECTS (Cost=547 Card=262968
Statistics
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
0 recursive calls
27 db block gets
3608 consistent gets
3516 physical reads
0 redo size
1483 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed
In order to get the aggregate count, we must count 250000+ records on over 3600 blocks.
Unfortunately, in our system we ask this question frequently, dozens of times every day.
We are scanning almost 30MB of data. We could avoid counting the details each and every
time by creating a materialized view of the data. The following demonstrates the basic
steps needed to perform this operation. Well discuss the GRANT and ALTER statements
> grant query rewrite to tkyte;
Grant succeeded.
> alter session set query_rewrite_enabled=true;
Session altered.
> alter session set query_rewrite_integrity=enforced;
Session altered.
> create materialized view my_all_objects_aggs
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner, count(*)
7 from my_all_objects
8 group by owner
9 /
Materialized view created.
> analyze table my_all_objects_aggs compute statistics;
Table analyzed.
Creating a Complete-Refreshable Materialized View
Complete refreshes are appropriate for MVs that have base tables in which significant portions of the rows change
from one refresh interval to the next. Complete refreshes are also required in situations where a fast refresh isn’t
possible (due to restrictions imposed by Oracle .
Note To create an MV, you need both the CREATE MATERIALIZED VIEW system privilege and the CREATE
TABLE system privilege.
The example in this section uses the following base table named SALES:
create table sales(
sales_id number
,sales_amt number
,region_id number
,sales_dtt timestamp
,constraint sales_pk primary key(sales_id)
);
Insert some sample data into the SALES table:
insert into sales values(1,101,100,sysdate-50);
insert into sales values(2,511,200,sysdate-20);
insert into sales values(3,11,100,sysdate);
commit;
The next step is to create the MV, using a CREATE MATERIALIZED VIEW...AS SELECT statement. This
statement names the MV, specifies its attributes, and defines the SQL query on which the MV is based:
create materialized view sales_mv
segment creation immediate
refresh
complete
on demand
as
select
sales_amt
,sales_dtt
from sales;
The SEGMENT CREATION IMMEDIATE clause is available with Oracle 11g release 2 and higher. It instructs
Oracle to create the segment and allocate an extent when you create the MV. This was the behavior in
previous versions of Oracle. If you don’t want immediate segment creation, use the SEGMENT CREATION
DEFERRED clause. If the newly created MV has any rows in it, then segments are created and extents are
allocated regardless of whether you use SEGMENT CREATION DEFERRED.
Let’s look at the USER_MVIEWS data dictionary to verify that the MV was created as expected. Here’s
the query to run:
select
mview_name
,refresh_method
,refresh_mode
,build_mode
,fast_refreshable
from user_mviews
where mview_name = 'SALES_MV';
Here’s the output for this MV:
MVIEW_NAME REFRESH_ REFRESH_MODE BUILD_MOD FAST_REFRESHABLE
------------ -------- ------------ --------- --------------------
SALES_MV COMPLETE DEMAND IMMEDIATE NO
It’s also informative to inspect the USER_OBJECTS and USER_SEGMENTS views to see what has been
created. When you query USER_OBJECTS, notice that several objects have been created:
select
object_name
,object_type
from user_objects
where object_name like 'SALES%'
order by object_name;
Here’s the corresponding output:
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
SALES TABLE
SALES_MV MATERIALIZED VIEW
SALES_MV TABLE
SALES_PK INDEX
The MV is a logical container that stores data in a regular database table. Querying the
USER_SEGMENTS view shows the base table, its primary-key index, and the table that stores data returned
by the MV query:
select
segment_name
,segment_type
from user_segments
where segment_name like 'SALES%'
order by segment_name;
Here’s the output for this example:
SEGMENT_NAME SEGMENT_TYPE
-------------------- --------------------
SALES TABLE
SALES_PK INDEX
SALES_MV TABLE
Note Oracle sometimes automatically creates an index on the MV. In this scenario, no index for the MV
was created
Next, insert some additional data into the base SALES table:
SQL> insert into sales values(4,99,200,sysdate);
SQL> insert into sales values(5,127,300,sysdate);
Now you attempt to initiate a fast refresh of the MV using the REFRESH procedure of the DBMS_MVIEW
package. This example passes two parameters to the REFRESH procedure: the name and the refresh
method. The name is SALES_MV, and the parameter is F (for fast):
SQL> exec dbms_mview.refresh('SALES_MV','F');
Because this MV wasn’t created in conjunction with an MV log, a fast refresh isn’t possible. The
following error is thrown:
ORA-12004: REFRESH FAST cannot be used for materialized view "MV"."SALES_MV"
Creating a Fast-Refreshable Materialized View
When you create a fast-refreshable MV, it initially populates the MV table with the entire result set of the
MV query. After the initial result set is in place, only data modified (in the base table) since the last
refresh needs to be applied to the MV. In other words, any updates, inserts, or deletes from the master
table that have occurred since the last refresh are copied over. This feature is appropriate when you have
a small amount of changes to a base table over a period of time compared to the total number of rows in
the table.
Here are the steps to implement a fast-refreshable MV:
1. Create a base table (if it’s not already created).
2. Create an MV log on the base table.
3. Create an MV as fast-refreshable.
This example first creates a base table. In most environments, you already have a base table in
place. For illustrative purposes, here’s the table-creation script and some sample data:
create table sales(
sales_id number
,sales_amt number
,region_id number
,sales_dtt timestamp
,constraint sales_pk primary key(sales_id)
);
--
insert into sales values(1,101,100,sysdate-50);
insert into sales values(2,511,200,sysdate-20);
insert into sales values(3,11,100,sysdate);
commit;
A fast-refreshable MV requires an MV log on the base table. When a fast refresh occurs, the MV log
must have a unique way to identify which records have been modified and thus need to be refreshed.
You can do this two different approaches. One method is to specify the PRIMARY KEY clause when you
create the MV log, and the other is to specify the ROWID clause. If the underlying base table has a primary
key, then use the primary key–based MV log. If the underlying base table has no primary key, then you
have to create the MV log using ROWID. In most cases, you’ll probably have a primary key defined for
every base table. However, the reality is that some systems are poorly designed or have some rare reason
for a table not to have a primary key.
In this example, a primary key is defined on the base table, so you create the MV log with the
PRIMARY KEY clause:
SQL> create materialized view log on sales with primary key;
If there was no primary key defined on the base table, this error is thrown when attempting to create
the MV log:
ORA-12014: table does not contain a primary key constraint
If the base table has no primary key, and you don’t have the option to add one, you must specify
ROWID when you create the MV log:
SQL> create materialized view log on sales with rowid;
Now that you’ve created the MV log, you can create the fast-refreshable MV. There are a couple of
interesting architectural considerations to point out. If there is no MV log on the base table, then Oracle
doesn’t allow you to create a fast-refreshable MV. This error is thrown:
ORA-23413: table does not have a materialized view log.
Also, when you create the fast-refreshable MV, you must specify whether it’s refreshed via the
PRIMARY KEY (which is the default) or via the ROWID. This example uses a table with a primary key and an
MV log created with a primary key. In this example, the MV is fast-refreshed via a primary key. When you
use a primary key–based fast-refreshable MV, the primary-key column(s) of the base table must be part
of the fast-refreshable MV SELECT statement:
create materialized view sales_mv
segment creation immediate
refresh
with primary key
fast
on demand
as
select
sales_id
,sales_amt
,sales_dtt
from sales;
At this point, it’s useful to inspect the objects that are associated with the MV. The following query
selects from USER_OBJECTS:
select
object_name
,object_type
from user_objects
where object_name like '%SALES%'
order by object_name;
Here are the objects that have been created:
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
MLOG$_SALES TABLE
RUPD$_SALES TABLE
SALES TABLE
SALES_MV MATERIALIZED VIEW
SALES_MV TABLE
SALES_PK INDEX
SALES_PK1 INDEX
SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;
Here’s the output:
SALES_AMT TO_CHAR(SALES_DTT
---------- -----------------
101 12-jun-2010
511 12-jul-2010
11 01-aug-2010
Let’s add two records to the base SALES table:
insert into sales values(4,99,200,sysdate);
insert into sales values(5,127,300,sysdate);
commit;
At this point, it’s instructional to inspect the M$LOG table. You should see two records that identify
how the data in the SALES table has changed:
SQL> select count(*) from mlog$_sales;
There are two records:
COUNT(*)
----------
2
Next, let’s refresh the MV. This MV is fast-refreshable, so you call the REFRESH procedure of the
DBMS_MVIEW package with the F (for fast) parameter:
SQL> exec dbms_mview.refresh('SALES_MV','F');
A quick inspection of the MV shows two new records:
SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;
SALES_AMT TO_CHAR(SALES_DTT
---------- -----------------
101 12-jun-2010
511 12-jul-2010
11 01-aug-2010
99 01-aug-2010
127 01-aug-2010
In addition, the count of the MLOG$ has dropped to zero. After the MV refresh is complete, those
records are no longer required:
SQL> select count(*) from mlog$_sales;
Here’s the output:
COUNT(*)
----------
0
You can verify the last method in which an MV was refreshed by querying the USER_MVIEWS view:
select
mview_name
,last_refresh_type
,last_refresh_date
from user_mviews
order by 1,3;
Here’s some sample output:
MVIEW_NAME LAST_REF LAST_REFR
------------------------------ -------- ---------
SALES_MV FAST 01-AUG-10
materialized views are mainly used in complex data warehousing environments, where the tables
grow so big that the data volume causes unacceptable performance problems. An important
property of data warehousing environments is that you don’t change the data very often.
Typically, there is a separate Extraction, Transformation, Loading (ETL)
process that updates the data warehouse contents.
Materialized views are also often used with distributed databases. In such
environments, accessing data over the network can become a performance bottleneck. You can
use materialized views to replicate data in a distributed database 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 (maybe many hundreds of thousands or millions) of
records. In basic terms, they allow a user to query potentially terabytes of detail data in seconds
(or less). 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
Say, for example, your company has a sales database loaded with the details of a million orders,
and you want to get a breakdown of sales by region (a common enough query).
Each and every record would be scanned, the data aggregated to the region level, and the
calculation performed. Using a materialized view, we can store a summary of the sales data by
region, and have the system maintain these summaries for us. If you have ten sales regions, this
summary will have ten records, so instead of sifting through a million detail
records, well query only ten. Furthermore, if someone asks a slightly different question,
say for the sales in a specific region, then that query can also obtain the answer from the
materialized view.
You will need a user account with the following privileges (at least):
• GRANT CREATE SESSION
• GRANT CREATE TABLE
• GRANT CREATE MATERIALIZED VIEW
• GRANT QUERY REWRITE
An Example
Well start with a large table that contains a list of owners of objects, and the objects they own.
This table is based on the ALL_OBJECTS data dictionary
view:
> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
65742 rows created.
> commit;
Commit complete.
> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
131484 rows created.
> commit
> commit;
Commit complete.
> analyze table my_all_objects compute statistics;
Table analyzed
> set autotrace on
> set timing on
> select owner, count(*) from my_all_objects group by owner;
OWNER COUNT(*)
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐
A 36
B 24
CTXSYS 2220
DBSNMP 48
DEMO 60
DEMO11 36
DEMO_DDL 108
MDSYS 2112
MV_USER 60
ORDPLUGINS 312
ORDSYS 2472
OUR_TYPES 12
OUTLN 60
PERFSTAT 636
PUBLIC 117972
SCHEDULER 36
SCOTT 84
SEAPARK 36
SYS 135648
SYSTEM 624
TESTING 276
TKYTE 12
TTS_USER 48
TYPES 36
24 rows selected.
Elapsed: 00:00:03.35
> set timing off
> set autotrace traceonly
> select owner, count(*) from my_all_objects group by owner;
24 rows selected.
Execution Plan
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2525 Card=24 Bytes=120)
1 0 SORT (GROUP BY) (Cost=2525 Card=24 Bytes=120)
2 1 TABLE ACCESS (FULL) OF MY_ALL_OBJECTS (Cost=547 Card=262968
Statistics
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
0 recursive calls
27 db block gets
3608 consistent gets
3516 physical reads
0 redo size
1483 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed
In order to get the aggregate count, we must count 250000+ records on over 3600 blocks.
Unfortunately, in our system we ask this question frequently, dozens of times every day.
We are scanning almost 30MB of data. We could avoid counting the details each and every
time by creating a materialized view of the data. The following demonstrates the basic
steps needed to perform this operation. Well discuss the GRANT and ALTER statements
> grant query rewrite to tkyte;
Grant succeeded.
> alter session set query_rewrite_enabled=true;
Session altered.
> alter session set query_rewrite_integrity=enforced;
Session altered.
> create materialized view my_all_objects_aggs
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner, count(*)
7 from my_all_objects
8 group by owner
9 /
Materialized view created.
> analyze table my_all_objects_aggs compute statistics;
Table analyzed.
Creating a Complete-Refreshable Materialized View
Complete refreshes are appropriate for MVs that have base tables in which significant portions of the rows change
from one refresh interval to the next. Complete refreshes are also required in situations where a fast refresh isn’t
possible (due to restrictions imposed by Oracle .
Note To create an MV, you need both the CREATE MATERIALIZED VIEW system privilege and the CREATE
TABLE system privilege.
The example in this section uses the following base table named SALES:
create table sales(
sales_id number
,sales_amt number
,region_id number
,sales_dtt timestamp
,constraint sales_pk primary key(sales_id)
);
Insert some sample data into the SALES table:
insert into sales values(1,101,100,sysdate-50);
insert into sales values(2,511,200,sysdate-20);
insert into sales values(3,11,100,sysdate);
commit;
The next step is to create the MV, using a CREATE MATERIALIZED VIEW...AS SELECT statement. This
statement names the MV, specifies its attributes, and defines the SQL query on which the MV is based:
create materialized view sales_mv
segment creation immediate
refresh
complete
on demand
as
select
sales_amt
,sales_dtt
from sales;
The SEGMENT CREATION IMMEDIATE clause is available with Oracle 11g release 2 and higher. It instructs
Oracle to create the segment and allocate an extent when you create the MV. This was the behavior in
previous versions of Oracle. If you don’t want immediate segment creation, use the SEGMENT CREATION
DEFERRED clause. If the newly created MV has any rows in it, then segments are created and extents are
allocated regardless of whether you use SEGMENT CREATION DEFERRED.
Let’s look at the USER_MVIEWS data dictionary to verify that the MV was created as expected. Here’s
the query to run:
select
mview_name
,refresh_method
,refresh_mode
,build_mode
,fast_refreshable
from user_mviews
where mview_name = 'SALES_MV';
Here’s the output for this MV:
MVIEW_NAME REFRESH_ REFRESH_MODE BUILD_MOD FAST_REFRESHABLE
------------ -------- ------------ --------- --------------------
SALES_MV COMPLETE DEMAND IMMEDIATE NO
It’s also informative to inspect the USER_OBJECTS and USER_SEGMENTS views to see what has been
created. When you query USER_OBJECTS, notice that several objects have been created:
select
object_name
,object_type
from user_objects
where object_name like 'SALES%'
order by object_name;
Here’s the corresponding output:
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
SALES TABLE
SALES_MV MATERIALIZED VIEW
SALES_MV TABLE
SALES_PK INDEX
The MV is a logical container that stores data in a regular database table. Querying the
USER_SEGMENTS view shows the base table, its primary-key index, and the table that stores data returned
by the MV query:
select
segment_name
,segment_type
from user_segments
where segment_name like 'SALES%'
order by segment_name;
Here’s the output for this example:
SEGMENT_NAME SEGMENT_TYPE
-------------------- --------------------
SALES TABLE
SALES_PK INDEX
SALES_MV TABLE
Note Oracle sometimes automatically creates an index on the MV. In this scenario, no index for the MV
was created
Next, insert some additional data into the base SALES table:
SQL> insert into sales values(4,99,200,sysdate);
SQL> insert into sales values(5,127,300,sysdate);
Now you attempt to initiate a fast refresh of the MV using the REFRESH procedure of the DBMS_MVIEW
package. This example passes two parameters to the REFRESH procedure: the name and the refresh
method. The name is SALES_MV, and the parameter is F (for fast):
SQL> exec dbms_mview.refresh('SALES_MV','F');
Because this MV wasn’t created in conjunction with an MV log, a fast refresh isn’t possible. The
following error is thrown:
ORA-12004: REFRESH FAST cannot be used for materialized view "MV"."SALES_MV"
Creating a Fast-Refreshable Materialized View
When you create a fast-refreshable MV, it initially populates the MV table with the entire result set of the
MV query. After the initial result set is in place, only data modified (in the base table) since the last
refresh needs to be applied to the MV. In other words, any updates, inserts, or deletes from the master
table that have occurred since the last refresh are copied over. This feature is appropriate when you have
a small amount of changes to a base table over a period of time compared to the total number of rows in
the table.
Here are the steps to implement a fast-refreshable MV:
1. Create a base table (if it’s not already created).
2. Create an MV log on the base table.
3. Create an MV as fast-refreshable.
This example first creates a base table. In most environments, you already have a base table in
place. For illustrative purposes, here’s the table-creation script and some sample data:
create table sales(
sales_id number
,sales_amt number
,region_id number
,sales_dtt timestamp
,constraint sales_pk primary key(sales_id)
);
--
insert into sales values(1,101,100,sysdate-50);
insert into sales values(2,511,200,sysdate-20);
insert into sales values(3,11,100,sysdate);
commit;
A fast-refreshable MV requires an MV log on the base table. When a fast refresh occurs, the MV log
must have a unique way to identify which records have been modified and thus need to be refreshed.
You can do this two different approaches. One method is to specify the PRIMARY KEY clause when you
create the MV log, and the other is to specify the ROWID clause. If the underlying base table has a primary
key, then use the primary key–based MV log. If the underlying base table has no primary key, then you
have to create the MV log using ROWID. In most cases, you’ll probably have a primary key defined for
every base table. However, the reality is that some systems are poorly designed or have some rare reason
for a table not to have a primary key.
In this example, a primary key is defined on the base table, so you create the MV log with the
PRIMARY KEY clause:
SQL> create materialized view log on sales with primary key;
If there was no primary key defined on the base table, this error is thrown when attempting to create
the MV log:
ORA-12014: table does not contain a primary key constraint
If the base table has no primary key, and you don’t have the option to add one, you must specify
ROWID when you create the MV log:
SQL> create materialized view log on sales with rowid;
Now that you’ve created the MV log, you can create the fast-refreshable MV. There are a couple of
interesting architectural considerations to point out. If there is no MV log on the base table, then Oracle
doesn’t allow you to create a fast-refreshable MV. This error is thrown:
ORA-23413: table does not have a materialized view log.
Also, when you create the fast-refreshable MV, you must specify whether it’s refreshed via the
PRIMARY KEY (which is the default) or via the ROWID. This example uses a table with a primary key and an
MV log created with a primary key. In this example, the MV is fast-refreshed via a primary key. When you
use a primary key–based fast-refreshable MV, the primary-key column(s) of the base table must be part
of the fast-refreshable MV SELECT statement:
create materialized view sales_mv
segment creation immediate
refresh
with primary key
fast
on demand
as
select
sales_id
,sales_amt
,sales_dtt
from sales;
At this point, it’s useful to inspect the objects that are associated with the MV. The following query
selects from USER_OBJECTS:
select
object_name
,object_type
from user_objects
where object_name like '%SALES%'
order by object_name;
Here are the objects that have been created:
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
MLOG$_SALES TABLE
RUPD$_SALES TABLE
SALES TABLE
SALES_MV MATERIALIZED VIEW
SALES_MV TABLE
SALES_PK INDEX
SALES_PK1 INDEX
SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;
Here’s the output:
SALES_AMT TO_CHAR(SALES_DTT
---------- -----------------
101 12-jun-2010
511 12-jul-2010
11 01-aug-2010
Let’s add two records to the base SALES table:
insert into sales values(4,99,200,sysdate);
insert into sales values(5,127,300,sysdate);
commit;
At this point, it’s instructional to inspect the M$LOG table. You should see two records that identify
how the data in the SALES table has changed:
SQL> select count(*) from mlog$_sales;
There are two records:
COUNT(*)
----------
2
Next, let’s refresh the MV. This MV is fast-refreshable, so you call the REFRESH procedure of the
DBMS_MVIEW package with the F (for fast) parameter:
SQL> exec dbms_mview.refresh('SALES_MV','F');
A quick inspection of the MV shows two new records:
SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;
SALES_AMT TO_CHAR(SALES_DTT
---------- -----------------
101 12-jun-2010
511 12-jul-2010
11 01-aug-2010
99 01-aug-2010
127 01-aug-2010
In addition, the count of the MLOG$ has dropped to zero. After the MV refresh is complete, those
records are no longer required:
SQL> select count(*) from mlog$_sales;
Here’s the output:
COUNT(*)
----------
0
You can verify the last method in which an MV was refreshed by querying the USER_MVIEWS view:
select
mview_name
,last_refresh_type
,last_refresh_date
from user_mviews
order by 1,3;
Here’s some sample output:
MVIEW_NAME LAST_REF LAST_REFR
------------------------------ -------- ---------
SALES_MV FAST 01-AUG-10
No comments:
Post a Comment