Partition Exchange is a handy mechanism to load new data into a partioned table in the reporting layer that can be directly used for creating reports.
The new data gets loaded in some kind of temp table.
Once the data is loaded, the partition gets switched from this temp table to the tabel in the reporting layer.
Using this mechanism prevents you from downtime when loading new data in the reporting layer.
But auwch, for this you need the Partitioning Option (https://www.oracle.com/database/partitioning/index.html), which does not come for free, and you will also need an Enterprise Edition License. (http://www.oracle.com/us/corporate/pricing/price-lists/index.html)
So here is a technique to refresh the data without downtime, and without partitioning:
We create two identical materialized views
CREATE MATERIALIZED VIEW MV1
AS
Select f.REFPIECE,
f.REFDOSS,
f.DW_TIMESTAMP,
to_char(sysdate , 'DD-MON-YYYY HH24:MI:SS') MV_LOAD_DATE
from imx_g_piece_facture f ;
and
CREATE MATERIALIZED VIEW MV2
AS
Select f.REFPIECE,
f.REFDOSS,
f.DW_TIMESTAMP,
to_char(sysdate , 'DD-MON-YYYY HH24:MI:SS') MV_LOAD_DATE
from imx_g_piece_facture f ;
Load them both with the data
Make a stored procedure to refresh the data
CREATE OR REPLACE PROCEDURE MV_DEMO
AUTHID CURRENT_USER IS
mv_module VARCHAR2(64);
mv_action VARCHAR2(64);
mv_mview_owner VARCHAR2(128) ;
mv_mview_name VARCHAR2(128);
v_sql CLOB;
BEGIN
mv_mview_owner := 'IL';
-- check which one is the oldest MV
WITH CHK_MV_TIMESTAMP AS (
SELECT ROW_NUMBER() OVER (ORDER BY last_refresh_date) as ranking
, mview_name, last_refresh_date
FROM user_mviews
WHERE owner = mv_mview_owner
AND mview_name IN('MV1','MV2')
)
SELECT mview_name
INTO mv_mview_name
FROM CHK_MV_TIMESTAMP
WHERE ranking = 1;
--- Load the new data
DBMS_MVIEW.REFRESH(mv_mview_name,'C', atomic_refresh=>FALSE);
dbms_stats.gather_Table_stats(ownname=>mv_mview_owner, tabname=>mv_mview_name, cascade=>TRUE);
Create the report view dynamically, pointing to the just filled materialized view
v_sql:= 'CREATE OR REPLACE VIEW VW_RAPPORT AS SELECT * FROM '||mv_mview_name;
EXECUTE IMMEDIATE v_sql;
END;
What does this procedure do?
By switching the dynamic view, we can simulate the partition exchange mechanism.
/PV
