Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpdw\tdpdw

Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpdw\tdpdw_refresh.htm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />
<title>Refreshing a Data Warehouse</title>
<meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1" />
<meta name="date" content="2009-04-27T11:26:23Z" />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content=" Refreshing a Data Warehouse" />
<meta name="relnum" content="11g Release 2 (11.2)" />
<meta name="partnum" content="E10578-01" />
<meta name="topic-id" content="TDPDW00275" />
<link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" />
<link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" />
<link rel="contents" href="toc.htm" title="Contents" type="text/html" />
<link rel="prev" href="tdpdw_sql.htm" title="Previous" type="text/html" />
<link rel="next" href="tdpdw_optimize.htm" title="Next" type="text/html" />
<script src="./callback.js" type="text/javascript"></script>
<noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript>
</head>
<body>
<div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div>
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr valign="bottom">
<td align="left"></td>
<td align="center"><a href="tdpdw_sql.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdpdw_optimize.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
<p><a id="CFHEEBGF" name="CFHEEBGF"></a><a id="TDPDW00275" name="TDPDW00275"></a></p>
<h1><a name="TDPDW090|Refreshing the Data Warehouse"></a>Refreshing a Data Warehouse</h1>
<p><a name="BEGIN" id="BEGIN"></a></p>
<p>This section includes the following topics:</p>
<ul>
<li>
<p><a href="#CFHEEEBH">About Refreshing Your Data Warehouse</a></p>
</li>
<li>
<p><a href="#CBHIIHAI">Using Rolling Windows to Offload Data</a></p>
</li>
</ul>
<p><a id="CFHEEEBH" name="CFHEEEBH"></a><a id="TDPDW0103" name="TDPDW0103"></a></p>
<div class="sect1">
<h2><a name="TDPDW0103|Refreshing the Data Warehouse"></a>About Refreshing Your Data Warehouse</h2>
<p>You must<a id="sthref188" name="sthref188"></a><a id="sthref189" name="sthref189"></a> update your data warehouse on a regular basis to ensure that the information derived from it is current. This process of updating the data is called the <span class="bold">refresh</span> <span class="bold">process</span>.</p>
<p>Extraction, transformation and loading (ETL) is done on a scheduled basis to reflect changes made to the original source system. During this step, you physically insert the new, updated data into the production data warehouse schema, and take all the other steps necessary (such as building indexes, validating constraints, making backup copies) to make this new data available to the users. Once all of this data has been loaded into the data warehouse, the materialized views must be updated to reflect the latest data.</p>
<p>The partitioning scheme of the data warehouse is often crucial in determining the efficiency of refresh operations in the data warehouse loading process. The loading process is often considered when choosing the partitioning scheme of data warehouse tables.</p>
<p>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 scenario, the data warehouse is being loaded by time. This suggests that the data warehouse tables should be partitioned on a date column. In the data warehouse example, suppose the new data is loaded into the sales table every month. Furthermore, the <code>sales</code> table has been partitioned by month. These steps show how the load process will proceed to add the data for a new month (Q1 2006) to the table <code>sales</code>.</p>
<a id="CFHFBJDI" name="CFHFBJDI"></a><a id="TDPDW01031" name="TDPDW01031"></a>
<div class="sect2">
<h3><a name="TDPDW01031|Example of Refreshing a Data Warehouse"></a>Example: Refreshing Your Data Warehouse</h3>
<p>Many queries request few columns from the <code>products</code>, <code>customers</code>, and <code>sales</code> tables, restricting the query by date. Take advantage of a materialized view that will speed up the majority of the queries against the three tables. Use a pre-built table on top of which the materialized view will be created. Choose the partitioning strategy of the materialized view in sync with the <code>sales</code> table's partitioning strategy.</p>
<p>The following example illustrates the refreshing of a materialized view. It uses a partition exchange loading operation. The example is based on the <code>sales</code> table in the <code>sh</code> schema.</p>
<a id="sthref190" name="sthref190"></a>
<p class="subhead2">To refresh a materialized view:</p>
<ol>
<li>
<p>Create a table that will be the basis for the materialized view.</p>
<pre xml:space="preserve" class="oac_no_warn">
CREATE TABLE sales_prod_cust_mv
( time_id DATE
, prod_id NUMBER
, prod_name VARCHAR2(50)
, cust_id NUMBER
, cust_first_name VARCHAR2(20)
, cust_last_name VARCHAR2(40)
, amount_sold NUMBER
, quantity_sold NUMBER
)
PARTITION BY RANGE (time_id)
( PARTITION p1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
, PARTITION p2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
, PARTITION p2001h1 VALUES LESS THAN (TO_DATE('01-JUL-2001','DD-MON-YYYY'))
, PARTITION p2001h2 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
, PARTITION p2001q1 VALUES LESS THAN (TO_DATE('01-APR-2002','DD-MON-YYYY'))
, PARTITION p2002q2 VALUES LESS THAN (TO_DATE('01-JUL-2002','DD-MON-YYYY'))
, PARTITION p2002q3 VALUES LESS THAN (TO_DATE('01-OCT-2002','DD-MON-YYYY'))
, PARTITION p2002q4 VALUES LESS THAN (TO_DATE('01-JAN-2003','DD-MON-YYYY'))
, PARTITION p2003q1 VALUES LESS THAN (TO_DATE('01-APR-2003','DD-MON-YYYY'))
, PARTITION p2003q2 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY'))
, PARTITION p2003q3 VALUES LESS THAN (TO_DATE('01-OCT-2003','DD-MON-YYYY'))
, PARTITION p2003q4 VALUES LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY'))
, PARTITION p2004q1 VALUES LESS THAN (TO_DATE('01-APR-2004','DD-MON-YYYY'))
, PARTITION p2004q2 VALUES LESS THAN (TO_DATE('01-JUL-2004','DD-MON-YYYY'))
, PARTITION p2004q3 VALUES LESS THAN (TO_DATE('01-OCT-2004','DD-MON-YYYY'))
, PARTITION p2004q4 VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY'))
, PARTITION p2005q1 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY'))
, PARTITION p2005q2 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY'))
, PARTITION p2005q3 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY'))
, PARTITION p2005q4 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
, PARTITION p2006q1 VALUES LESS THAN (TO_DATE('01-APR-2006','DD-MON-YYYY'))
) PARALLEL COMPRESS;
</pre></li>
<li>
<p>Load the initial table from the <code>sales</code> table.</p>
<pre xml:space="preserve" class="oac_no_warn">
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL smv */ INTO sales_prod_cust_mv smv
SELECT /*+ PARALLEL s PARALLEL c */ s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, cust_first_name
, c.cust_last_name
, SUM(s.amount_sold)
, SUM(s.quantity_sold)
FROM sales s
, products p
, customers c
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, c.cust_first_name
, c.cust_last_name;
COMMIT;
</pre></li>
<li>
<p>Create a materialized view.</p>
<pre xml:space="preserve" class="oac_no_warn">
CREATE MATERIALIZED VIEW sales_prod_cust_mv
ON PREBUILT TABLE
ENABLE <a id="sthref191" name="sthref191"></a>QUERY REWRITE
AS SELECT s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, c.cust_first_name
, c.cust_last_name
, SUM(s.amount_sold) amount_sold
, SUM(s.quantity_sold) quantity_sold
FROM sales s
, products p
, customers c
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, c.cust_first_name
, c.cust_last_name;
</pre></li>
<li>
<p>Load a separate table to be exchanged with the new partition.</p>
<pre xml:space="preserve" class="oac_no_warn">
CREATE TABLE sales_q1_2006 PARALLEL COMPRESS
AS SELECT * FROM sales
WHERE 0 = 1;
 
/* This would be the regular ETL job */
 
ALTER SESSION ENABLE PARALLEL DML;
 
INSERT /* PARALLEL qs */ INTO sales_q1_2006 qs
SELECT /* PARALLEL s */ prod_id
, cust_id
, add_months(time_id,3)
, channel_id
, promo_id
, quantity_sold
, amount_sold
FROM sales PARTITION(sales_q4_2005) s;
 
COMMIT;
 
CREATE BITMAP INDEX bmp_indx_prod_id ON sales_q1_2006 (prod_id);
CREATE BITMAP INDEX bmp_indx_cust_id ON sales_q1_2006 (cust_id);
CREATE BITMAP INDEX bmp_indx_time_id ON sales_q1_2006 (time_id);
CREATE BITMAP INDEX bmp_indx_channel_id ON sales_q1_2006 (channel_id);
CREATE BITMAP INDEX bmp_indx_promo_id ON sales_q1_2006 (promo_id);
 
ALTER TABLE sales_q1_2006 ADD CONSTRAINT sales_q_prod_fk
FOREIGN KEY (prod_id) REFERENCES products(prod_id) ENABLE NOVALIDATE;
 
ALTER TABLE sales_q1_2006 ADD CONSTRAINT sales_q_cust_fk
FOREIGN KEY (cust_id) REFERENCES customers(cust_id) ENABLE NOVALIDATE;
 
ALTER TABLE sales_q1_2006 ADD CONSTRAINT sales_q_time_fk
FOREIGN KEY (time_id) REFERENCES times(time_id) ENABLE NOVALIDATE;
 
ALTER table sales_q1_2006 ADD CONSTRAINT sales_q_channel_fk
FOREIGN KEY (channel_id) REFERENCES channels(channel_id) ENABLE NOVALIDATE;
 
ALTER table sales_q1_2006 ADD CONSTRAINT sales_q_promo_fk
FOREIGN KEY (promo_id) REFERENCES promotions(promo_id) ENABLE NOVALIDATE;
 
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('SH','SALES_Q1_2006');
END;
/
</pre></li>
<li>
<p>Create and load a separate table to be exchanged with a partition in the materialized view.</p>
<pre xml:space="preserve" class="oac_no_warn">
CREATE TABLE sales_mv_q1_2006 PARALLEL COMPRESS
AS SELECT * FROM sales_prod_cust_mv
WHERE 1 = 0;

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL smv */ INTO sales_mv_q1_2006 smv
SELECT /*+ PARALLEL s PARALLEL c */ s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, cust_first_name
, c.cust_last_name
, SUM(s.amount_sold)
, SUM(s.quantity_sold)
FROM sales_q1_2006 s
, products p
, customers c 
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY s.time_id
, s.prod_id
, p.prod_name
, s.cust_id 
, c.cust_first_name
, c.cust_last_name;

COMMIT;
</pre></li>
<li>
<p>Gather statistics.</p>
<pre xml:space="preserve" class="oac_no_warn">
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('SH','SALES_MV_Q1_2006');
END;
</pre></li>
<li>
<p>Exchange the partitions.</p>
<pre xml:space="preserve" class="oac_no_warn">
ALTER TABLE sales
EXCHANGE PARTITION sales_q1_2006
WITH TABLE sales_q1_2006
INCLUDING INDEXES WITHOUT VALIDATION;
 
ALTER TABLE sales_prod_cust_mv
EXCHANGE PARTITION p2006q1
WITH TABLE sales_mv_q1_2006
INCLUDING INDEXES WITHOUT VALIDATION;
</pre></li>
<li>
<p>Inform the database that the materialized view is fresh again.</p>
<pre xml:space="preserve" class="oac_no_warn">
ALTER MATERIALIZED VIEW sales_prod_cust_mv CONSIDER FRESH;
</pre></li>
</ol>
<p>Note that because this scenario uses a prebuilt table and, because the constraints are not <code>RELY</code> constraints, the <a id="sthref192" name="sthref192"></a>query rewrite feature will work only with <code>query_rewrite_integrity</code> set to <code>STALE_TOLERATED</code>.</p>
</div>
</div>

<p><a id="CBHIIHAI" name="CBHIIHAI"></a><a id="TDPDW0104" name="TDPDW0104"></a></p>
<div class="sect1">
<h2><a name="TDPDW0104|Using Rolling Windows"></a>Using Rolling Windows to Offload Data</h2>
<p>A particularly<a id="sthref193" name="sthref193"></a><a id="sthref194" name="sthref194"></a> effective way of removing and archiving your data is through the use of a rolling window. An example of using a rolling window is when the data warehouse stores the most recent 36 months of sales data. A new partition can be added to the <code>sales</code> table for each new month, and an old partition can be removed from the <code>sales</code> table. This way, you will always maintain 36 months of data in the warehouse.</p>
<a id="TDPDW01041" name="TDPDW01041"></a>
<div class="sect2"><a id="sthref195" name="sthref195"></a>
<h3><a name="TDPDW01041|Example of Using a Rolling Window"></a>Example: Using a Rolling Window</h3>
<p>The following example illustrates a rolling window for the <code>sales</code> table in the <code>sh</code> schema.</p>
<a id="sthref196" name="sthref196"></a>
<p class="subhead2">To use a rolling window:</p>
<ol>
<li>
<p>Add the sales for December 2005.</p>
<pre xml:space="preserve" class="oac_no_warn">
ALTER TABLE sales
ADD PARTITION sales_12_2005 VALUES LESS THAN ('01-JAN-2006');
</pre>
<p>Note that you must rebuild any existing indexes.</p>
</li>
<li>
<p>Drop the partition for 1999.</p>
<pre xml:space="preserve" class="oac_no_warn">
ALTER TABLE sales
DROP PARTITION sales_1999;
</pre></li>
</ol>
</div>
</div>


<div class="footer">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr>
<td align="left"><span class="copyrightlogo">Copyright&nbsp;&copy;&nbsp;2007, 2009,&nbsp;Oracle&nbsp;and/or&nbsp;its&nbsp;affiliates.&nbsp;All&nbsp;rights&nbsp;reserved.</span><br />
<a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td>
<td align="center"><a href="tdpdw_sql.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdpdw_optimize.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
</div>

</body>
</html>

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de