A Poor Man's Partition exchange

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?

• First we look which MV contains the oldest data, this MV we will use to load with the new data
• Refresh the MV
• Dynamically create a view pointing to the newly refreshed MV
• This view will be used in the reporting layer

 

By switching the dynamic view, we can simulate the partition exchange mechanism.

 

/PV