Databases Reference
In-Depth Information
Creating an Unpopulated MV
When you create an MV, you have the option of instructing Oracle whether or not to initially populate the MV with
data. For example, if it takes several hours to initially build an MV, you may want to first define the MV and then
populate it as a separate job.
This example uses the
BUILD DEFERRED
clause to instruct Oracle not to initially populate the MV with the results
of the query:
create materialized view sales_mv
tablespace users
build deferred
refresh complete on demand
as
select sales_id, sales_amt
from sales;
At this point, querying the MV results in zero rows returned. At some later point, you can initiate a complete
refresh to populate the MV with data.
Creating an MV Refreshed on Commit
You may be required, when data are modified in the master table, to have them immediately copied to an MV. In this
scenario, use the
ON COMMIT
clause when you create the MV. The master table must have an MV log created on it for
this technique to work:
SQL> create materialized view log on sales with primary key;
Next, an MV is created that refreshes on commit:
create materialized view sales_mv
refresh
on commit
as
select sales_id, sales_amt from sales;
As data are inserted and committed in the master table, any changes are also available in the MV that would be
selected by the MV query.
The
ON COMMIT
refreshable MV has a few restrictions you need to be aware of:
•
The master table and MV must be in the same database.
•
You can't execute distributed transaction on the base table.
•
This approach isn't supported with MVs that contain object types or Oracle-supplied types.
Also consider the overhead associated with committing data simultaneously in two places; this can affect the
performance of a high-transaction OLTP system. Additionally, if there is any problem with updating the MV, then
the base table can't commit a transaction. For example, if the tablespace in which the MV is created becomes full
(and can't allocate another extent), you see an error such as this when trying to insert into the base table:
ORA-12008: error in materialized view refresh path
ORA-01653: unable to extend table MV_MAINT.SALES_MV by 16 in tablespace...
For these reasons, you should use this feature only when you're sure it won't affect performance or availability.
