Pro Oracle Database 11g Administration (62 page)
Creating a Complete-Refreshable Materialized View
This section explains how to set up an MV that is periodically completely refreshed, which is about the simplest example possible. 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—more on this later in the chapter).
■
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)
);
365
CHAPTER 15 ■ MATERIALIZED VIEWS
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 11
g
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
-------------------- --------------------
366
CHAPTER 15 ■ MATERIALIZED VIEWS
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.
Finally, let’s look at how to refresh the SALES_MV MV. Here’s the data contained in SALES_MV: 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 17-jun-2010
511 17-jul-2010
11 06-aug-2010
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"
367
CHAPTER 15 ■ MATERIALIZED VIEWS
5. Users can query data from the MV, which contains a point-in-time snapshot of the base table’s data.
In the next section, a more complicated example shows you how to set up a fast-refreshable 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
369
CHAPTER 15 ■ MATERIALIZED VIEWS
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
370
CHAPTER 15 ■ MATERIALIZED VIEWS
Several objects in the previous output require some explanation:
• MLOG$_SALES
• RUPD$_SALES
• SALES_PK1
First, when an MV log is created, a corresponding table is also created that stores the rows in the base table that changed and how they changed (insert, update, or delete). The MV log table name is of the format MLOG$_
A table is also created with the format RUPD$_
Oracle Advanced Replication Guide
for more details on updatable MVs). If you’re not using the updatable MV feature, then you can ignore the RUPD$ table.
Oracle also creates an index with the format
Now that you understand the underlying architectural components, let’s look at the data in the MV: 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; Here’s some sample output:
371
CHAPTER 15 ■ MATERIALIZED VIEWS
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: