Ora cle® Database Data Warehousing Guide
10g Release 1 (10.1)
Part Number B10736-01

Book List

Contents

15 Maintaining the Dat a Warehouse

This chapter discusses how to load and refresh a data warehouse, and discusses:

Using Partitioning to Improve Data Warehouse Refresh
Opti mizing DML Operations During Refresh
Refreshing Materialized Views
Using Materialized Views with Partitioned Tables

Using Partitioning to Improve Data Warehouse Refresh

ETL (Extrac tion, Transformation and Loading) is done on a scheduled basis to reflect changes made to the original source system. During this ste p, you physically insert the new, clean data into the production data warehouse schema, and take all of the other steps necessary (su ch as building indexes, validating constraints, taking backups) to make this new data available to the end users. Once all of this da ta has been loaded into the data warehouse, the materialized views have to be updated to reflect the latest data.

The partitioning scheme of the data warehouse is often crucial in determining the efficiency of refresh op erations in the data warehouse load process. In fact, the load process is often the primary consideration in choosing the partitionin g scheme of data warehouse tables and indexes.

The partitioning scheme of the largest data warehouse tables (for example, the fact table in a star schema) should be based upon the loading paradigm of the data warehouse.

Most data warehouses are loaded with new data on a regular schedule. For example, every night, week, or month, new data is brought into the data warehouse. The data being loaded at the end of the week or month typically corresponds to the transactions for the week or month. In this very common sce nario, the data warehouse is being loaded by time. This suggests that the data warehouse tables should be partitioned on a date colum n. In our data warehouse example, suppose the new data is loaded into the sales table every month. Furthermore, the sales table has been partitioned by month. These steps show how the load process will proceed to add the data for a new mont h (January 2001) to the table sales.

Place the new data into a separate table, sales_01_2001. This data can be directly loaded into sales_01_2001 from outside the data warehouse, or this data can be the result of previous data transformation operations that have already occurred in the data warehouse. sales_01_2001 has the exact same columns, datatypes, and so forth, as the sales table. Gather statistics on the sales_01_2 001 table.



Create indexes and add constraints on sales_01_2001. Again, the indexes and constrain
ts on sales_01_2001 should be identical to the indexes and constraints on sales. Indexes can be built in pa
rallel and should use the NOLOGGING and the COMPUTE STATISTICS options. For example:
CREATE BITMAP INDEX sales_01_2001_customer_id_bix
  ON sales_01_2001(customer_id)
      TABLESPACE sales_idx NOL
OGGING PARALLEL 8 COMPUTE STATISTICS;


Apply all constraints to the sales_01_2001 table that are present on th
e sales table. This includes referential integrity constraints. A typical constraint would be:
ALTER TABLE sales_01_2001 ADD CONSTRAINT sales_customer_id
      REFERENCES customer(customer_id) ENABLE NOVALIDATE;



If the partitioned table sales has a primary or unique key that is enforced with a global index structure, ensure that
the constraint on sales_pk_jan01 is validated without the creation of an index structure, as in the following:
ALTER TABLE sales_01_2001 ADD CONSTRAINT sales_pk_jan01
PRIMARY KEY (sales_transaction_id) DISABLE VALIDATE;



The creation of the constraint with ENABLE clause would cause the creation of a unique index, which does not ma
tch a local index structure of the partitioned table. You must not have any index structure built on the nonpartitioned table to be e
xchanged for existing global indexes of the partitioned table. The exchange command would fail.

Add the sales_ 01_2001 table to the sales table. In order to add this new data to the sales table, we need t o do two things. First, we need to add a new partition to the sales table. We will use the ALTER TABL E ... ADD PARTITION statement. This will add an empty partition to the sales table: ALTER TABLE sales ADD PARTITION sales_01_2001 VALUES LESS THAN (TO_DATE('01-FEB-2001', 'DD-MON-YYYY')); Then, we can add our newly created table to this partition using the EXCHANGE PARTITION operatio n. This will exchange the new, empty partition with the newly loaded table. ALTER TABLE sales EXCHANGE PARTITION sales_01_2001 WITH TABLE sales_01_2001 INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
The EXCHANGE operation will preserve the indexes and constraints that were already present on the sales_01_2001 tabl e. For unique constraints (such as the unique constraint on sales_transaction_id), you can use the UPDATE < code>GLOBAL INDEXES clause, as shown previously. This will automatically maintain your global index structures as part of the partition maintenance operation and keep them accessible throughout the whole process. If there were only foreign-key co nstraints, the exchange operation would be instantaneous.

The benefits of this partitioning technique are significan t. First, the new data is loaded with minimal resource utilization. The new data is loaded into an entirely separate table, and the i ndex processing and constraint processing are applied only to the new partition. If the sales table was 50 GB and had 12 partitions, then a new month's worth of data contains approximately 4 GB. Only the new month's worth of data needs to be indexed. No ne of the indexes on the remaining 46 GB of data needs to be modified at all. This partitioning scheme additionally ensures that the load processing time is directly proportional to the amount of new data being loaded, not to the total size of the sales table.

Second, the new data is loaded with minimal impact on concurrent queries. All of the operations associated with data l oading are occurring on a separate sales_01_2001 table. Therefore, none of the existing data or indexes of the sal es table is affected during this data refresh process. The sales table and its indexes remain entirely untouched throughout this refresh process.

Third, in case of the existence of any global indexes, those are incrementally maintained as part of the exchange command. This maintenance does not affect the availability of the existing global index structures.

The e xchange operation can be viewed as a publishing mechanism. Until the data warehouse administrator exchanges the sales_01_2001 table into the sales table, end users cannot see the new data. Once the exchange has occurred, then any end user q uery accessing the sales table will immediately be able to see the sales_01_2001 data.


Partitioning
is useful not only for adding new data but also for removing and archiving data. Many data warehouses maintain a rolling window of da
ta. For example, the data warehouse stores the most recent 36 months of sales data. Just as a new partition can be added
 to the sales table (as described earlier), an old partition can be quickly (and independently) removed from the s
ales table. These two benefits (reduced resources utilization and minimal end-user impact) are just as pertinent to removing a
 partition as they are to adding a partition.
Removing data from a partitioned table does not necessarily mean that the old da
ta is physically deleted from the database. There are two alternatives for removing old data from a partitioned table. First, you can
 physically delete all data from the database by dropping the partition containing the old data, thus freeing the allocated space:
ALTER TABLE sales DROP PARTITION sales_01_1998;



Also, you can exchange the old partition with
 an empty table of the same structure; this empty table is created equivalent to step1 and 2 described in the load process. Assuming
the new empty table stub is named sales_archive_01_1998, the following SQL statement will 'empty' partition sales_
01_1998:
ALTER TABLE sales EXCHANGE PARTITION sales_01_1998 WITH TABLE sales_archive_01_1998 INC
LUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES;



Note that the old data is still existent as the exchanged, nonpa
rtitioned table sales_archive_01_1998.
If the partitioned table was setup in a way that every partition is stored
 in a separate tablespace, you can archive (or transport) this table using Oracle Database's transportable tablespace framework befor
e dropping the actual data (the tablespace). See "Transportation Using Transportable Tablespaces" for further details regarding transportable tablespaces.
In some situations, you might not want to drop the old data immedia
tely, but keep it as part of the partitioned table; although the data is no longer of main interest, there are still potential querie
s accessing this old, read-only data. You can use Oracle's data compression to minimize the space usage of the old data. We also assu
me that at least one compressed partition is already part of the partitioned table. See Chapter 3, "
Physical Design in Data Warehouses" for a generic discussion of table compression and Chapter 5, "
 Parallelism and Partitioning in Data Warehouses" for partitioning and table compression.
 
 
Refresh
 Scenarios
 
A typical scenario might not only need to compress old data, but als
o to merge several old partitions to reflect the granularity for a later backup of several merged partitions. Let's assume that a bac
kup (partition) granularity is on a quarterly base for any quarter, where the oldest month is more than 36 months behind the most rec
ent month. In this case, we are therefore compressing and merging sales_01_1998, sales_02_1998, and s
ales_03_1998 into a new, compressed partition sales_q1_1998.


Create the new m
erged partition in parallel another tablespace. The partition will be compressed as part of the MERGE operation:
ALTER TABLE sales MERGE PARTITION sales_01_1998, sales_02_1998, sales_03_1998 INTO PARTITION sales_q1_1998 TAB
LESPACE archive_q1_1998 
COMPRESS UPDATE GLOBAL INDEXES PARALLEL 4;




The partition MERGE operation
invalidates the local indexes for the new merged partition. We therefore have to rebuild them:
ALTER TA
BLE sales MODIFY PARTITION sales_1_1998 
REBUILD UNUSABLE LOCAL INDEXES;



Alternatively, you can choose to creat
e the new compressed table outside the partitioned table and exchange it back. The performance and the temporary space consumption is
 identical for both methods:


Create an intermediate table to hold the new merged information. The
 following statement inherits all NOT NULL constraints from the origin table by default:
CREATE TABLE sales_q1_1998_out TABLESPACE archive_q1_1998 NOLOGGING COMPRESS PARALLEL 4 AS SELECT * FROM sales 
WHERE tim
e_id >=  TO_DATE('01-JAN-1998','dd-mon-yyyy')
  AND time_id < TO_DATE('01-JUN-1998','dd-mon-yyyy');




Creat
e the equivalent index structure for table sales_q1_1998_out than for the existing table sales.

Prepare the existing table sales for the exchange with the new compressed table sales_q1_1998_out. Because the tab
le to be exchanged contains data actually covered in three partition, we have to 'create one matching partition, having the range bou
ndaries we are looking for. You simply have to drop two of the existing partitions. Note that you have to drop the lower two partitio
ns sales_01_1998 and sales_02_1998; the lower boundary of a range partition is always defined by the upper
(exclusive) boundary of the previous partition:
ALTER TABLE sales DROP PARTITION sales_01_1998;
ALTER T
ABLE sales DROP PARTITION sales_02_1998;
 



You can now exchange table sales_q1_1998_out with partit
ion sales_03_1998. Unlike what the name of the partition suggests, its boundaries cover Q1-1998.
ALTER TABLE sales EXCHANGE PARTITION sales_03_1998 
WITH TABLE sales_q1_1998_out INCLUDING INDEXES WITHOUT VALIDATION 
UPDATE
GLOBAL INDEXES;



Both methods apply to slightly different business scenarios: Using the MERGE PARTITION approach invalidates the local index structures for the affected partition, but it keeps all data accessible all th
e time. Any attempt to access the affected partition through one of the unusable index structures raises an error. The limited availa
bility time is approximately the time for re-creating the local bitmap index structures. In most cases this can be neglected, since t
his part of the partitioned table shouldn't be touched too often.
The CTAS approach, however, minimizes unavailability of any
index structures close to zero, but there is a specific time window, where the partitioned table does not have all the data, because
we dropped two partitions. The limited availability time is approximately the time for exchanging the table. Depending on the existen
ce and number of global indexes, this time window varies. Without any existing global indexes, this time window is a matter of a frac
tion to few seconds.
These examples are a simplification of the data warehouse rolling window load scenario. Real-world data w
arehouse refresh characteristics are always more complex. However, the advantages of this rolling window approach are not diminished
in more complex scenarios.
Note that before you add single or multiple compressed partitions to a partitioned table for the fi
rst time, all local bitmap indexes must be either dropped or marked unusable. After the first compressed partition is added, no addit
ional actions are necessary for all subsequent operations involving compressed partitions. It is irrelevant how the compressed partit
ions are added to the partitioned table. See Chapter 5, " Parallelism and Partitioning in Data Warehou
ses" for further details about partitioning and table compression.

 
Scenarios for Using Partitioning for Ref
reshing Data Warehouses
This section contains two typical scenarios where partitioning is used with refresh.

 
Refresh Scenario
 1
Data is loaded daily. However, the data warehouse contains two years of data, so that partitioning by day might not
 be desired.
The solution is to partition by week or month (as appropriate). Use INSERT to add the new data to an
 existing partition. The INSERT operation only affects a single partition, so the benefits described previously remain i
ntact. The INSERT operation could occur while the partition remains a part of the table. Inserts into a single partition
 can be parallelized:
INSERT /*+ APPEND*/ INTO sales PARTITION (sales_01_2001) 
SELECT * FROM new_sales
;

The indexes of this sales partition will be maintained in parallel as well. An alternative is to use the < code>EXCHANGE operation. You can do this by exchanging the sales_01_2001 partition of the sales tabl e and then using an INSERT operation. You might prefer this technique when dropping and rebuilding indexes is more effic ient than maintaining them.

Refresh Scenario 2

New data feeds, although consisting primarily of data for the most recent day, week, and month, also contain some data from previous time periods.

Solution 1

Use parallel SQL operations (such as CREATE TABLE ... AS SELECT) to separate the new data from th e data in previous time periods. Process the old data separately using other techniques.

New data feeds are not solely time ba sed. You can also feed new data into a data warehouse with data from multiple operational systems on a business need basis. For examp le, the sales data from direct channels may come into the data warehouse separately from the data from indirect channels. For busines s reasons, it may furthermore make sense to keep the direct and indirect data in separate partitions.

Opt imizing DML Operations During Refresh

You can optimize DML performance through the following techniques:

Implementing an Efficient MERGE Operation
Ma intaining Referential Integrity
Purging Data

Implementing an Efficient MERGE Operation

Commonly, the data that is extracted from a source system is not simply a list of new records that needs to be inserted into the data warehouse. Instead, this new data set is a combination of new records as well as modified records. For example, suppose that most of data extracted from the O LTP systems will be new sales transactions. These records will be inserted into the warehouse's sales table, but some re cords may reflect modifications of previous transactions, such as returned merchandise or transactions that were incomplete or incorr ect when initially loaded into the data warehouse. These records require updates to the sales table.

As a typical scenario, suppose that there is a table called new_sales that contains both inserts and updates that will be applied to the sales table. When designing the entire data warehouse load process, it was determined that the new_sales table would contain records with the following semantics:




If a given sales_transaction_id of a record in new_sales already exists in sales, then update the sales table by adding the
sales_dollar_amount and sales_quantity_sold values from the new_sales table to the existing ro
w in the sales table.

Otherwise, insert the entire new record from the new_sales table into the sales table.

This UPDATE-ELSE-INSERT operation is often called a merge.
A merge can be executed using one SQL statement.
 
Example 15-1 MERGE Operation
MERGE INTO sales s USING new_sales n
ON (s.sales_transaction_id = n.sales_transaction_id)
WHEN MATCHED THEN
UP
DATE s_quantity = s_quantity + n_quantity, s_dollar = s_dollar + n_dollar
WHEN NOT MATCHED THEN INSERT (sales_quantity_sold, sales_do
llar_amount)
VALUES (n.sales_quantity_sold, n.sales_dollar_amount);



In addition to using the MERGE statement
 for unconditional UPDATE ELSE INSERT functionality into a target table, you can also use it t
o:



Perform an UPDATE only or INSERT only statem
ent.

Apply additional WHERE conditions for the UPDATE or INSERT p
ortion of the MERGE statement.

The UPDATE operation can even delete rows if a
specific condition yields true.
 
Example 15-2 Omitting the INSERT Clause
In some data warehouse applicatio
ns, it is not allowed to add new rows to historical information, but only to update them. It may also happen that you don't want to u
pdate but only insert new information. The following examples demonstrate the INSERT-only respective the UPDATE-only functionality:

MERGE USING Product_Changes S     -- Source/Delta table
INTO Products D1
            -- Destination table 1
ON (D1.PROD_ID = S.PROD_ID)       -- Search/Join condition
WHEN MATCHED THEN UPDATE          -- up
date if join
SET D1.PROD_STATUS = S.PROD_NEW_STATUS



 
Example 15-3 Omitting the UPDATE Clause

The following statement illustrates an example of omitting an UPDATE:
MERGE US
ING New_Product S           -- Source/Delta table
INTO Products D2                    -- Destination table 2
ON (D2.PROD_ID = S.PROD_
ID)         -- Search/Join condition
WHEN NOT MATCHED THEN               -- insert if no join
INSERT (PROD_ID, PROD_STATUS) VALUES (S
.PROD_ID, S.PROD_NEW_STATUS)



When the INSERT clause is omitted, Oracle performs a regular join of the source
 and the target tables. When the UPDATE clause is omitted, Oracle performs an antijoin of the source and the target tabl
es. This makes the join between the source and target table more efficient.


<
a id="sthref753" name="sthref753"> 
Example 15-4 Skipping the UPDATE Cla
use
In some situations, you may want to skip the UPDATE operation when merging a given row i
nto the table. In this case, you can use an optional WHERE clause in the UPDATE clause of the MERGE. As a result, the UPDATE operation only executes when a given condition is true. The following statement illustrat
es an example of skipping the UPDATE operation:

MERGE 
USING Product_Changes S
          -- Source/Delta table 
INTO Products P                              -- Destination table 1 
ON (P.PROD_ID = S.PROD_ID)
              -- Search/Join condition 
WHEN MATCHED THEN 
UPDATE                                       -- update if join 
SET P.PROD
_LIST_PRICE = S.PROD_NEW_PRICE 
WHERE P.PROD_STATUS <> "OBSOLETE"            -- Conditional UPDATE



This shows how t
he UPDATE operation would be skipped if the condition P.PROD_STATUS <> "OBSOLETE" is not true. The co
ndition predicate can refer to both the target and the source table.


 
Example 15-5 Conditional Inserts with MERGE
 Statements
You may want to skip the INSERT operation when merging a given row into the tabl
e. So an optional WHERE clause is added to the INSERT clause of the MERGE. As a result, the INSERT operation only executes when a given condition is true. The following statement offers an example:
MERGE USING Product_Changes S        -- Source/Delta table
INTO Products P                      -- Destination table 1
ON (P.PROD_ID = S.PROD_ID)           -- Search/Join condition
WHEN MATCHED THEN UPDATE             -- update if join
SET P.PROD_LIST_
PRICE = S.PROD_NEW_PRICE
WHERE P.PROD_STATUS <> "OBSOLETE"    -- Conditional
UPDATE WHEN NOT MATCHED THEN
INSERT
                -- insert if not join
SET P.PROD_LIST_PRICE = S.PROD_NEW_PRICE
WHERE S.PROD_STATUS <> "OBSOLETE"    -- Conditio
nal INSERT



This example shows that the INSERT operation would be skipped if the condition S.PROD_STATU
S <> "OBSOLETE" is not true, and INSERT will only occur if the condition is true. The condition predicate c
an refer to the source table only. This predicate would be most likely a column filter.

 
Example 15-6 Using the D
ELETE Clause with MERGE Statements
You may want to cleanse tables while populating or updating them. To d
o this, you may want to consider using the DELETE clause in a MERGE statement, as in the following example:

MERGE USING Product_Changes S
INTO Products D ON (D.PROD_ID = S.PROD_ID)
WHEN MATCHED THEN
UPDATE SET
D.PROD_LIST_PRICE =S.PROD_NEW_PRICE, D.PROD_STATUS = S.PROD_NEWSTATUS
DELETE WHERE (D.PROD_STATUS = "OBSOLETE")
WHEN NOT MATCHED THEN

INSERT (PROD_ID, PROD_LIST_PRICE, PROD_STATUS)
VALUES (S.PROD_ID, S.PROD_NEW_PRICE, S.PROD_NEW_STATUS);



Thus when a row
is updated in products, Oracle checks the delete condition D.PROD_STATUS = "OBSOLETE", and deletes the row
if the condition yields true.
The DELETE operation is not as same as that of a complete DELETE state
ment. Only the rows from the destination of the MERGE can be deleted. The only rows that will be affected by the D
ELETE are the ones that are updated by this MERGE statement. Thus, although a give row of the destination table m
eets the delete condition, if it does not join under the ON clause condition, it will not be deleted.

 
Example 15-7 Unconditional Inserts with MERGE Statements
You may want to insert all of the source rows
 into a table. In this case, the join between the source and target table can be avoided. By identifying special constant join condit
ions that always result to FALSE, for example, 1=0, such MERGE statements will be optimized and the join co
ndition will be suppressed.
MERGE USING New_Product S       -- Source/Delta table 
INTO Products P
            -- Destination table 1 
ON (1 = 0)                      -- Search/Join condition 
WHEN NOT MATCHED THEN           -- inse
rt if no join 
INSERT (PROD_ID, PROD_STATUS) VALUES (S.PROD_ID, S.PROD_NEW_STATUS)



 
 
Maintaining Referential Integrity
In some data warehousing environments
, you might want to insert new data into tables in order to guarantee referential integrity. For example, a data warehouse may derive
 sales from an operational system that retrieves data directly from cash registers. sales is refreshed nigh
tly. However, the data for the product dimension table may be derived from a separate operational system. The prod
uct dimension table may only be refreshed once for each week, because the product table changes relatively slowly
. If a new product was introduced on Monday, then it is possible for that product's product_id to appear in the sa
les data of the data warehouse before that product_id has been inserted into the data warehouses product table.

Although the sales transactions of the new product may be valid, this sales data will not satisfy the referential
integrity constraint between the product dimension table and the sales fact table. Rather than disallow the
 new sales transactions, you might choose to insert the sales transactions into the sales table. However, you might also
 wish to maintain the referential integrity relationship between the sales and product tables. This can be
accomplished by inserting new rows into the product table as placeholders for the unknown products.
As in previou
s examples, we assume that the new data for the sales table will be staged in a separate table, new_sales.
Using a single INSERT statement (which can be parallelized), the product table can be altered to reflect th
e new products:
INSERT INTO PRODUCT_ID
  (SELECT sales_product_id, 'Unknown Product Name', NULL, NULL .
..
   FROM new_sales WHERE sales_product_id NOT IN
  (SELECT product_id FROM product));



 
 
Purging Data
Occasio
nally, it is necessary to remove large amounts of data from a data warehouse. A very common sc
enario is the rolling window discussed previously, in which older data is rolled out of the data warehouse to make room for new data.

However, sometimes other data might need to be removed from a data warehouse. Suppose that a retail company has previously so
ld products from MS Software, and that MS Software has subsequently gone out of b
usiness. The business users of the warehouse may decide that they are no longer interested in seeing any data related to MS Software, so this data should be deleted.

One approach to removing a large volume of data is to use parallel
delete as shown in the following statement:
DELETE FROM sales WHERE sales_product_id IN (SELECT product
_id 
   FROM product WHERE product_category = 'MS Software');



This SQL statement will spawn one parallel process for each
 partition. This approach will be much more efficient than a serial DELETE statement, and none of the data in the 
sales table will need to be moved. However, this approach also has some disadvantages. When removing a large percentage of row
s, the DELETE statement will leave many empty row-slots in the existing partitions. If new data is being loaded using a
rolling window technique (or is being loaded using direct-path INSERT or load), then this storage space will not be recl
aimed. Moreover, even though the DELETE statement is parallelized, there might be more efficient methods. An alternative
 method is to re-create the entire sales table, keeping the data for all product categories except MS Software.
CREATE TABLE sales2 AS SELECT * FROM sales, product
WHERE sales.sales_product_id = pr
oduct.product_id 
AND product_category <> 'MS Software'
NOLOGGING PARALLEL (DEGREE 8)
#PARTITION ... ; #create indexes, constra
ints, and so on
DROP TABLE SALES;
RENAME SALES2 TO SALES;



This approach may be more efficient than a parallel delete. How
ever, it is also costly in terms of the amount of disk space, because the sales table must effectively be instantiated t
wice.
An alternative method to utilize less space is to re-create the sales table one partition at a time:
CREATE TABLE sales_temp AS SELECT * FROM sales WHERE 1=0;
INSERT INTO sales_temp PARTITION (sales_99jan)
SELE
CT * FROM sales, product
WHERE sales.sales_product_id = product.product_id
AND product_category <> 'MS Software';
<create ap
propriate indexes and constraints on sales_temp>
ALTER TABLE sales EXCHANGE PARTITION sales_99jan WITH TABLE sales_temp;



Continue this process for each partition in the sales table.

Refreshing Materialized Views When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT. In the case of ON COMMIT, the materialized view is changed every time a transaction commits, which changes data used by the materialized view, thus ensuring that the materializ ed view always contains the latest data. Alternatively, you can control the time when refresh of the materialized views occurs by spe cifying ON DEMAND. In this case, the materialized view can only be refreshed by calling one of the procedur es in the DBMS_MVIEW package. DBMS_MVIEW provides three different types of refresh operations. < ul> DBMS_MVIEW.REFRESH Refresh one or more materialize d views. DBMS_MVIEW.REFRESH_ALL_MVIEWS Refres h all materialized views. DBMS_MVIEW.REFRESH_DEPENDENT Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or mat erialized views. Performing a refresh operation requires temporary space to rebuild t he indexes and can require additional space for performing the refresh operation itself. Some sites might prefer not to refresh all o f their materialized views at the same time: as soon as some underlying detail data has been updated, all materialized views using th is data will become stale. Therefore, if you defer refreshing your materialized views, you can either rely on your chosen rewrite int egrity level to determine whether or not a stale materialized view can be used for query rewrite, or you can temporarily disable quer y rewrite with an ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FALSE statement. After refreshing the materialized views, you can re-enable query rewrite as the default for all sessions in the current database instance by specifying ALTER SYSTEM SET QUERY_REWRITE_ENABLED as TRUE. Refres hing a materialized view automatically updates all of its indexes. In the case of full refresh, this requires temporary sort space to rebuild all indexes during refresh. This is because the full refresh truncates or deletes the table before inserting the new full da ta volume. If insufficient temporary space is available to rebuild the indexes, then you must explicitly drop each index or mark it < code>UNUSABLE prior to performing the refresh operation. If you anticipate performing insert, update or delete operatio ns on tables referenced by a materialized view concurrently with the refresh of that materialized view, and that materialized view in cludes joins and aggregation, Oracle recommends you use ON COMMIT fast refresh rather than ON DEMAND fast refresh. Complete Refresh A complete refresh occurs when the materialized view is initially defined as BUILD IMMEDIATE, unless th e materialized view references a prebuilt table. For materialized views using BUILD DEFERRED, a complete re fresh must be requested before it can be used for the first time. A complete refresh may be requested at any time during the life of any materialized view. The refresh involves reading the detail tables to compute the results for the materialized view. This can be a very time-consuming process, especially if there are huge amounts of data to be read and processed. Therefore, you should always con sider the time required to process a complete refresh before requesting it. There are, however, cases when the only refresh me thod available for an already built materialized view is complete refresh because the materialized view does not satisfy the conditio ns specified in the following section for a fast refresh. Fast Refresh Most data warehouses have periodic incremental updates to their detail data. As described in "Materialized View Schema Design", you can use the SQL*Loader or any bulk load utility to perform incremental loads of detai l data. Fast refresh of your materialized views is usually efficient, because instead of having to recompute the entire materialized view, the changes are applied to the existing data. Thus, processing only the changes can result in a very fast refresh time. Partition Change Tracking (PCT) Refresh When there have been som e partition maintenance operations on the detail tables, this is the only method of fast refresh that can be used. PCT-based refresh on a materialized view is enabled only if all the conditions described in "Partition Change Tracking" are satisfied. In the absence of partition maintenance operations on detail tables, when you request for a FAST method (method => 'F') of refresh through procedures in DBMS_MVIEW package, Oracle will choose PCT refr esh if it is enabled on the materialized view and is determined to be the better than log-based fast refresh. Similarly, when you req uest for a FORCE method (method => '?'), Oracle will choose PCT refresh if it is enabled on the material ized view and is calculated to be the better than log-based fast refresh and complete refresh. Alternatively, you can request the PCT method (method => 'P'), and Oracle will use the PCT method provided all PCT requirements are satisfied. Oracl e can use TRUNCATE PARTITION on a materialized view if it satisfies the conditions in "Bene fits of Partitioning a Materialized View" and hence, make the PCT refresh process more efficient. Manual Refresh Using the DBMS_MVIEW Package When a materialized view is refreshed ON DEMAND, one of four refresh methods can be specified as shown in the following table. You can define a defaul t option during the creation of the materialized view. Table 15-1 details the refresh options. Table 15-1 ON DEMAND Refresh Methods See Also:< /p> "Manual Refresh Using the DBMS_MVIEW Package" for more information about this package Refresh Option Parameter Description COMPLETE C Refreshes by recalculating the defining query of the materi alized view FAST F Refreshes by incrementally applying changes to the materialized view. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT. FAST_PCT P Refreshes by recomputing the rows in the materialized view affec ted by changed partitions in the detail tables. FORCE ? Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refre sh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE. Three refresh procedures are available in the DBMS_MVIEW< /code> package for performing ON DEMAND refresh. Each has its own unique set of parameters. See Also: PL/SQL Packages and Types Reference for detailed information ab out the DBMS_MVIEW package and Oracle Database Adva nced Replication for information showing how to use it in a replication environment < !-- class="inftblnotealso --> Refresh Specific Materialized Views with REFRESH Use the DBMS_MVIEW.REFRESH procedure to refresh one or more materialized views. Some parameters are used only for replication, so they are not mentioned here. The required parameters to use this procedure are: The comma-delimited list of materialized views to refresh The refresh method: F- Fast, P-Fast_PCT, ?-Force, C-Complete The rollback segment to use Refresh after errors (TRUE or FALSE) A Boolean parameter. If set to TRUE, the number_of_failures output parameter will be set to the number of refreshes that failed, and a gener ic error message will indicate that failures occurred. The alert log for the instance will give details of refresh errors. If set to FALSE, the default, then refresh will stop after it encounters the first error, and any remaining materialized views in the list will not be refreshed. The following four parameters are used by the replication process. For w arehouse refresh, set them to FALSE, 0,0,0. Atomic refresh (TRUE or FALS E) If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction. If set to FALSE, Oracle can optimize refr esh by using parallel DML and truncate DDL on a materialized views. For example, to perform a fast refresh on the ma terialized view cal_month_sales_mv, the DBMS_MVIEW package would be called as follows: DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', 'F', '', TRUE, FALSE, 0,0,0, FALSE); Multiple materialized views can b e refreshed at the same time, and they do not all have to use the same refresh method. To give them different refresh methods, specif y multiple method codes in the same order as the list of materialized views (without commas). For example, the following specifies th at cal_month_sales_mv be completely refreshed and fweek_pscat_sales_mv receive a fast refresh: DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV', 'CF', '', TRUE, FALSE, 0,0,0, FALSE); If the refresh method is not specified, the default refresh method as specified in the materialized view definition will be used. Refresh All Materialized Views with REFRESH_ALL_MVIEWS An a lternative to specifying the materialized views to refresh is to use the procedure DBMS_MVIEW.REFRESH_ALL_MVIEWS. This p rocedure refreshes all materialized views. If any of the materialized views fails to refresh, then the number of failures is reported . The parameters for this procedure are: The number of failures (this is an OUT vari able) The refresh method: F-Fast, P-Fast_PCT, ?-Force, C-Complete Refresh after errors (TRUE or FALSE) A Boolean parameter. If set to TRUE, the number_of_failures output parameter will be set to the number of refreshes that failed , and a generic error message will indicate that failures occurred. The alert log for the instance will give details of refresh error s. If set to FALSE, the default, then refresh will stop after it encounters the first error, and any remaining materiali zed views in the list will not be refreshed. Atomic refresh (TRUE or FALSE) If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of e ach specified materialized view is done in a separate transaction. If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views. An example of refreshing all materialized views is the follo wing: DBMS_MVIEW.REFRESH_ALL_MVIEWS(failures,'C','', TRUE, FALSE); Refresh De pendent Materialized Views with REFRESH_DEPENDENT The third procedure, DB MS_MVIEW.REFRESH_DEPENDENT, refreshes only those materialized views that depend on a specific table or list of tables. For exa mple, suppose the changes have been received for the orders table but not for customer payments. The refres h dependent procedure can be called to refresh only those materialized views that reference the orders table. The parameters for this procedure are: The number of failures (this is an OUT variable) The dependent table The refresh method: F-Fast, P-Fast_ PCT, ?-Force, C-Complete The rollback segment to use Refresh after errors (TRUE or FALSE) A Boolean parameter. If set to TRUE, the number_of_failures output parameter will be set to the number of refreshes that failed, and a generic error message will indi cate that failures occurred. The alert log for the instance will give details of refresh errors. If set to FALSE, the de fault, then refresh will stop after it encounters the first error, and any remaining materialized views in the list will not be refre shed. Atomic refresh (TRUE or FALSE) If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction. If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a mat erialized views. Whether it is nested or not If set to TRUE, refresh all the depende nt materialized views of the specified set of tables based on a dependency order to ensure the materialized views are truly fresh wit h respect to the underlying base tables. To perform a full refresh on all materialized views that reference the customers table, specify: DBMS_MVIEW.REFRESH_DEPENDENT(failures, 'CUSTOMERS', 'C', '', FALSE, FALSE ); To obtain the list of materialized views that are directly dependent on a given object (table or materialized vi ew), use the procedure DBMS_MVIEW.GET_MV_DEPENDENCIES to determine the dependent materialized views for a given table, o r for deciding the order to refresh nested materialized views. DBMS_MVIEW.GET_MV_DEPENDENCIES(mvlist IN VARCHAR2, deplist OUT VARCHAR2) The input to this function is the name or names of the materialized view. The output i s a comma separated list of the materialized views that are defined on it. For example, the following statement: DBMS_MVIEW.GET_MV_DEPENDENCIES("JOHN.SALES_REG, SCOTT.PROD_TIME", deplist) This populates deplist with the list of materialized views defined on the input arguments. For example: deplist <= "JOHN.SU M_SALES_WEST, JOHN.SUM_SALES_EAST, SCOTT.SUM_PROD_MONTH". Using Job Queues for Refresh Job q ueues can be used to refresh multiple materialized views in parallel. If queues are not available, fast refresh will sequentially ref resh each view in the foreground process. To make queues available, you must set the JOB_QUEUE_PROCESSES parameter. This parameter defines the number of background job queue p rocesses and determines how many materialized views can be refreshed concurrently. Oracle tries to balance the number of concurrent r efreshes with the degree of parallelism of each refresh. The order in which the materialized views are refreshed is determined by dep endencies imposed by nested materialized views and potential for efficient refresh by using query rewrite against other materialized views (See "Scheduling Refresh" for details). This parameter is only effective when atomic_refresh is set to FALSE. If the process that is executing DBMS_MVIEW.REFR ESH is interrupted or the instance is shut down, any refresh jobs that were executing in job queue processes will be requeued and will continue running. To remove these jobs, use the DBMS_JOB.REMOVE procedure. When Fast Refr esh is Possible Not all materialized views may be fast refreshable. Therefore, use the package DBMS_MVIEW.EXPLAI N_MVIEW to determine what refresh methods are available for a materialized view. See Chapter 8, " Basic Materialized Views" for further information about the DBMS_MVIEW package. If you are not sure how to make a materialized view fast refreshable, you can use the DBMS_ADVISOR.TUNE_MVIEW procedure, which will provide a scri pt containing the statements required to create a fast refreshable materialized view. See "Tuning Mate rialized Views for Fast Refresh and Query Rewrite".

Refresh Option	Parameter	Description
`COMPLETE`	`C`	Refreshes by recalculating the defining query of the materi alized view
`FAST`	`F`	Refreshes by incrementally applying changes to the materialized view. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based `FAST` and `FAST_PCT`.
`FAST_PCT`	`P`	Refreshes by recomputing the rows in the materialized view affec ted by changed partitions in the detail tables.
`FORCE`	`?`	Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refre sh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based `FAST`, `FAST_PCT`, and `COMPLETE`.

Recommended Initialization Parameters for Parallelism The following initialization parameters need to be set properly for parallelism to be effective: PARALLEL_MAX_SERVERS should be set high enough to take care of parallelism. You need to consider the number of slaves needed for the refresh statement. For example, wit h a DOP of eight, you need 16 slave processes. PGA_AGGREGA< a id="sthref796" name="sthref796">TE_TARGET should be set for the instance to manage the memory usage for sorts and joins automatically. If the memory parameters are set manually, SORT_AREA_SIZE should be less than HASH_AREA_SIZE . OPTIMIZER_MODE should equal all_rows. Remember to analyze all tables and indexes for better optimization. S ee Chapter 24, " Using Parallel Execution" for further information. Checking the Status of a Materialized View Three views are provided for checking the status of a materialized view: DBA_MVEIWS< /code>, ALL_MVIEWS, and USER_MVIEWS. To check if a materialized view is fresh or stale, issue the following statement: SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY M VIEW_NAME; MVIEW_NAME STALENESS LAST_REF COMPILE_STATE ---------- --------- -------- --- ---------- CUST_MTH_SALES_MV NEEDS_COMPILE FAST NEEDS_COMPILE PROD_YR_SALES_MV FRESH FAST VALI D If the compile_state column shows NEEDS COMPILE, the other displayed column valu es cannot be trusted as reflecting the true status. To revalidate the materialized view, issue the following statement: ALTER MATERIALIZED VIEW [materialized_view_name] COMPILE; Then reissue the SELECT statement . Scheduling Refresh Very often you will have multiple materialized views in the database. Some of these can be computed by rewriting against others. T his is very common in data warehousing environment where you may have nested materialized views or materialized views at different le vels of some hierarchy. In such cases, you should create the materialized views as BUILD DEFERRED, and then issue one of the refresh procedures in DBMS_MVIEW package to refresh all the materialized views. Oracle Database will compute the dependencies and refresh the materialized views in the right order. Consider the example of a complete hierarchical cube describ ed in "Examples of Hierarchical Cube Materialized Views". Suppose all the materialized views have b een created as BUILD DEFERRED. Creating the materialized views as BUILD DEFERRED will only create the metad ata for all the materialized views. And, then, you can just call one of the refresh procedures in DBMS_MVIEW package to refresh all the materialized views in the right order: EXECUTE DBMS_MVIEW.REFRESH_DEPENDENT(list=>'S ALES', method => 'C'); The procedure will refresh the materialized views in the order of their dependencies (first sales_hierarchical_mon_cube_mv, followed by sales_hierarchical_qtr_cube_mv, then, sales_hierarchical_yr_ cube_mv and finally, sales_hierarchical_all_cube_mv). Each of these materialized views will get rewritten against the one prior to it in the list). The same kind of rewrite can also be used while doing PCT refresh. PCT refresh recomputes r ows in a materialized view corresponding to changed rows in the detail tables. And, if there are other fresh materialized views avail able at the time of refresh, it can go directly against them as opposed to going against the detail tables. Hence, it is alway s beneficial to pass a list of materialized views to any of the refresh procedures in DBMS_MVIEW package (irrespective o f the method specified) and let the procedure figure out the order of doing refresh on materialized views. Tips for Refreshing Materialized Views with Aggregates Following are some guidelines for using the refresh mechanism for m aterialized views with aggregates. For fast refresh, create materialized view logs on all detail tables involved in a materialized view with the ROWID, SEQUENCE and INCLUDING NEW VALUES clauses. Include all columns from the table likely to be used in materialized views in the materialized view log s. Fast refresh may be possible even if the SEQUENCE option is omitted from the materialized view log. If it can be determined that only inserts or deletes will occur on all the detail tables, then the materialized view log does not require the < code>SEQUENCE clause. However, if updates to multiple tables are likely or required or if the specific update scenarios are un known, make sure the SEQUENCE clause is included. Use Oracle's bulk loader utility or direc t-path INSERT (INSERT with the APPEND hint for loads). This is a lot more efficient tha n conventional insert. During loading, disable all constraints and re-enable when finished loading. Note that materialized view logs are required regardless of whether you use direct load or conventional DML. Try to optimize the sequence of conventional mixed DML operations, direct-path INSERT and the fast refresh of materialized views. You can use fast refresh with a mixture of conventional DML and direct loads. Fast refresh can perform significant optimizations if it finds that only direct loads have occu rred, as illustrated in the following: Direct-path INSERT (SQL*Loader or INSER T /*+ APPEND */) into the detail table Refresh materialized view Conventional mixed DML< /li> Refresh materialized view You can use fast refresh with conventional mixed DML (INSERT, UPDATE, and DELETE) to the detail tables. However, fast refresh will be able to perform significant optimizat ions in its processing if it detects that only inserts or deletes have been done to the tables, such as: DML INSERT or DELETE to the detail table Refresh materialized views DML update to the detail table Refresh materialized view Even more optimal is the separation of INSERT and DELETE. If possible, refresh should be performed after each type of data change (as shown earlier) rather than issuing only one refresh at the end. If that is not possible, restrict the convent ional DML to the table to inserts only, to get much better refresh performance. Avoid mixing deletes and direct loads. Further more, for refresh ON COMMIT, Oracle keeps track of the type of DML done in the committed transaction. There fore, do not perform direct-path INSERT and DML to other tables in the same transaction, as Oracle may not be able to op timize the refresh phase. For ON COMMIT materialized views, where refreshes automatically occur at t he end of each transaction, it may not be possible to isolate the DML statements, in which case keeping the transactions short will h elp. However, if you plan to make numerous modifications to the detail table, it may be better to perform them in one transaction, so that refresh of the materialized view will be performed just once at commit time rather than after each update. Oracle recommends partitioning the tables because it enables you to use: Parallel DML Fo r large loads or refresh, enabling parallel DML will help shorten the length of time for the operation. Partition Change Tracking (PCT) fast refresh You can refresh your materialized views fast after partition maintenance operatio ns on the detail tables. "Partition Change Tracking" for details on enabling PCT for materialized vi ews. Partitioning the materialized view will also help refresh performance as refresh can upda te the materialized view using parallel DML. For example, assume that the detail tables and materialized view are partitioned and hav e a parallel clause. The following sequence would enable Oracle to parallelize the refresh of the materialized view. Bulk load into the detail table. Enable parallel DML with an ALTER SESSION ENABLE PARALLEL DML statement. Refresh the materialized view. For refresh using DBMS_MVIEW.REFRESH, set the parameter atomic_refresh to FALSE. For COMPLETE refresh, this will TRUNCATE to delete existing ro ws in the materialized view, which is faster than a delete. For PCT refresh, if the materia lized view is partitioned appropriately, this will use TRUNCATE PARTITION to delete rows in the affected partitions of t he materialized view, which is faster than a delete. For FAST or FORCE refresh , if COMPLETE or PCT refresh is chosen, this will be able to use the TRUNCATE optimizations described earli er. When using DBMS_MVIEW.REFRESH with JOB_QUEUES, remember to set < code>atomic to FALSE. Otherwise, JOB_QUEUES will not get used. Set the number of job queue processes greater than the number of processors. If job queues are enabled and there are many materialized views to refresh, it is fast er to refresh all of them in a single command than to call them individually. Use REFRESH < code>FORCE to ensure refreshing a materialized view so that it can definitely be used for query rewrite. The best refresh meth od will be chosen. If a fast refresh cannot be done, a complete refresh will be performed. Refresh all t he materialized views in a single procedure call. This gives Oracle an opportunity to schedule refresh of all the materialized views in the right order taking into account dependencies imposed by nested materialized views and potential for efficient refresh by using query rewrite against other materialized views. Tips for Refreshing Materialized Views Without Aggre gates If a materialized view contains joins but no aggregates, then having an index on each of the join column rowids in the detail table will enhance refresh performance greatly, because this type of materialized view tends to be much larger than mat erialized views containing aggregates. For example, consider the following materialized view: CREATE MA TERIALIZED VIEW detail_fact_mv BUILD IMMEDIATE AS SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "cust_rid", c.cust_stat e_province, t.week_ending_day, s.amount_sold FROM sales s, times t, customers c WHERE s.time_id = t.time_id AND s.cust_id = c.cust_i d; Indexes should be created on columns sales_rid, times_rid and cust_rid. Partit ioning is highly recommended, as is enabling parallel DML in the session before invoking refresh, because it will greatly enhance ref resh performance. This type of materialized view can also be fast refreshed if DML is performed on the detail table. It is rec ommended that the same procedure be applied to this type of materialized view as for a single table aggregate. That is, perform one t ype of change (direct-path INSERT or DML) and then refresh the materialized view. This is because Oracle Database can pe rform significant optimizations if it detects that only one type of change has been done. Also, Oracle recommends that the ref resh be invoked after each table is loaded, rather than load all the tables and then perform the refresh. For refresh ON COMMIT, Oracle keeps track of the type of DML done in the committed transaction. Oracle therefore recommends tha t you do not perform direct-path and conventional DML to other tables in the same transaction because Oracle may not be able to optim ize the refresh phase. For example, the following is not recommended: Direct load new data into t he fact table DML into the store table Commit Also, try not to mix different types of conventional DML statements if possible. This would again prevent using various optimizations during fast refresh. For example, t ry to avoid the following: Insert into the fact table Delete from the fact table Commit If many updates are needed, try to group them all into one transaction because refresh will be performed just once at commit time, rather than after each update. When you use the DBMS_MVIEW package to ref resh a number of materialized views containing only joins with the ATOMIC parameter set to TRUE, if you dis able parallel DML, refresh performance may degrade. In a data warehousing environment, assuming that the materialized view has a parallel clause, the following sequence of steps is recommended: Bulk load into the fact table Enable parallel DML An ALTER SESSION ENABLE PARALLEL< /code> DML statement Refresh the materialized view Tips for Refreshing Nested Materialized Views All underlying objects are treated as ordinary tables when refreshing materialized views. If the ON COMMIT refresh option is specified, then all the materialized views are refreshed in the appropriate order at commit time. In other words, Oracle builds a partially ordered set of materialized views and refreshes them such that, after the successful completion of the refresh, all the ma terialized views are fresh. The status of the materialized views can be checked by querying the appropriate USER_, DBA_, or ALL_MVIEWS view. If any of the materialized views are defined as ON DEMAND refresh (irrespective of whether the refresh method is FAST, FORCE, or COMPLETE), you wil l need to refresh them in the correct order (taking into account the dependencies between the materialized views) because the nested materialized view will be refreshed with respect to the current contents of the other materialized views (whether fresh or not). This can be achieved by invoking the refresh procedure against the materialized view at the top of the nested hierarchy and specifying th e nested parameter as TRUE. If a refresh fails during commit time, the list of materialized views that has not been refreshed is written to the alert log, and you must manually refresh them along with all their dependent materialized views. Use the sam e DBMS_MVIEW procedures on nested materialized views that you use on regular materialized views. These procedures have the following behavior when used with nested materialized views: If REFRESH is applie d to a materialized view my_mv that is built on other materialized views, then my_mv will be refreshed with respect to the current contents of the other materialized views (that is, the other materialized views will not be made fresh first) unless you specify nested => TRUE. If REFRESH_DEPENDENT is applied to materialized view my_mv, then only materialized views that directly depend on my_mv will be refreshed (that is, a materializ ed view that depends on a materialized view that depends on my_mv will not be refreshed) unless you specify nested => TRUE. If REFRESH_ALL_MVIEWS is used, the order in which the materialized views will be ref reshed is guaranteed to respect the dependencies between nested materialized views. GET_MV_DEPENDE NCIES provides a list of the immediate (or direct) materialized view dependencies for an object. Tips for Fast Refresh with UNION ALL You can use fast refresh for materialized views that use the UNION ALL operator by providing a maintenance column in the definition of the materialized view. For example, a materialized view with a UNION ALL operat or can be made fast refreshable as follows: CREATE MATERIALIZED VIEW fast_rf_union_all_mv AS SELECT x.r owid AS r1, y.rowid AS r2, a, b, c, 1 AS marker FROM x, y WHERE x.a = y.b UNION ALL SELECT p.rowid, r.rowid, a, c, d, 2 AS marker F ROM p, r WHERE p.a = r.y; The form of a maintenance marker column, column MARKER in the example, must be numeric_or_string_literal AS column_alias, where each UNION ALL member has a distinct value for numeric_or_string_literal. Tips After Refreshing Materialized Views After you have performed a load or incremental load and rebuilt the detail table indexes, you need to re-enable integrity c onstraints (if any) and refresh the materialized views and materialized view indexes that are derived from that detail data. In a dat a warehouse environment, referential integrity constraints are normally enabled with the NOVALIDATE or RELY options. An important decision to make before performing a refresh operation is whether the refresh needs to be recoverable. Because materialized view data is redundant and can always be reconstructed from the detail tables, it might be preferable to disable loggin g on the materialized view. To disable logging and run incremental refresh non-recoverably, use the ALTER MATERIAL IZED VIEW ... NOLOGGING statement prior to refreshing. If the materialized view is being refr eshed using the ON COMMIT method, then, following refresh operations, consult the alert log alert_SID.log and the trace file ora_SID_number.trc to check that no errors ha ve occurred.

Refresh Scenario 1

Refresh Scenario 2

Solution 1

Refresh Specific Materialized Views with REFRESH

Fast Refresh with Partit ion Change Tracking

PCT Fast Refresh Scen ario 1

PCT Fast Refresh Scenario 2

PCT Fast Refresh Scenario 3

Fast Refresh with CONSIDER FRESH

15 Maintaining the Dat a Warehouse

Using Partitioning to Improve Data Warehouse Refresh

Refresh Scenarios

Scenarios for Using Partitioning for Ref reshing Data Warehouses

Opt imizing DML Operations During Refresh

Implementing an Efficient MERGE Operation

Maintaining Referential Integrity

Purging Data

Refreshing Materialized Views

Complete Refresh

Fast Refresh

Partition Change Tracking (PCT) Refresh

Manual Refresh Using the DBMS_MVIEW Package

Refresh All Materialized Views with REFRESH_ALL_MVIEWS

Refresh De pendent Materialized Views with REFRESH_DEPENDENT

Using Job Queues for Refresh

When Fast Refr esh is Possible

Recommended Initialization Parameters for Parallelism

Checking the Status of a Materialized View

Scheduling Refresh

Tips for Refreshing Materialized Views with Aggregates

Tips for Refreshing Materialized Views Without Aggre gates

Tips for Refreshing Nested Materialized Views

Tips for Fast Refresh with UNION ALL

Tips After Refreshing Materialized Views

Using Materialized Views with Partitioned Tables