A Poor Man's Partition exchange

Tuesday, April 25, 2017

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

 

New from the blog

  • Vaadin  NavigationBuilder
    Friday, May 19, 2017

    Developing your navigation needs in default Vaadin is ok, but I thought it could be enhanced a bit. Hence the idea of creating a new addon, NavigationBuilder.

     

  • Hadoop
    Tuesday, May 2, 2017

    This article covers

    • Why use Hadoop?
    • What is Hadoop?
    • Recognizing Use Cases
    • Some Use Cases

     

    Apache Hadoop has evolved into the standard platform solution for data storage and analysis.

    Two key aspects of Hadoop have driven its rapid adoption by companies hungry for improved insights into the data they collect:

    • Hadoop can store data of any type and from any source‚ÄĒinexpensively and at very large scale.
    • Hadoop enables the sophisticated analysis of even very large data sets, easily and quickly.
  • OLAP and Hadoop
    Tuesday, March 28, 2017

    On-line analytical processing (OLAP) is a category of data processing solutions designed to enable fast queries against raw, structured, multi-dimensional data. OLAP offerings are available from vendors including SAP, Microsoft, IBM, HP and Oracle.

    OLAP solutions are typically used for business intelligence and reporting, business process management, sales management, reporting and forecasting, and financial reporting of high-value, Big Data. OLAP systems are chosen for the ability to analyze multidimensional data in near real-time, support for complex analytical queries, the ability to organize data at query time, and reduced time-to-reporting.