Monthly Archives: July 2020

Redshift

Zero-Downtime Summary Table Refresh Using AB Rotating Tables in Oracle Exadata and Redshift

Aside Leave a comment

Zero-Downtime Summary Table Refresh Using AB Rotating Tables in Oracle Exadata and Redshift

1. Zero-Downtime Refresh Framework in Oracle Exadata

 

In Enterprise Data Warehouse system, it is quite often some summary tables are required to refresh several times even hourly per day.  Meanwhile most of the summary tables are accessed globally in 24/7 mode . That is, there is no any downtime window allowed for the scheduled  or on-demand refreshes.  Therefore, how to implement the Zero-Downtime table refresh is a challenge.

In our Oracle Exadata-based Enterprise Data Warehouse system, we successfully developed the framework for the Summary Table Refresh process using A/B Rotating strategy to automatically  implement the  Zero-Downtime refresh process.

The Oracle-based framework is outlined below:

      1. Create A/B tables — For each of the summary tables to refresh, say a  sales summary fact table named “F_SLS_SUM”,  create two identical A/B  tables  “F_SLS_SUM_A” and “F_SLS_SUM_B” .
      2. Create a Public Synonym “F_SLS_SUM” pointing to one of the A/B tables appropriately and dynamically during the process.
      3. Configure and Update Refresh Control Table: REFRESH_CRTL_TAB ( Synonym_Name*,  Schema_Name*, Rotate_Table_Name*,  Rotate_Status, Refresh_Status).
      4. The process refresh the “Inactive” Rotate table and update the Rotate and Refresh status correspondingly (columns with * are unique). For example:

2. Zero-Downtime Refresh Implementation  in Redshift

 

In Redshift there is no Public Synonym  concept (at least at this blog writing time). Therefore  we can’t migrate the existing Oracle-based Zero-Downtime refresh framework directly to Redshift by switching the Public Synonym.

As a workaround solution in Redshift, After the refresh process sync the refresh control table,  rather than switch the Public Synonym, we can define a Redshift view using the Refresh Control Table :

CREATE OR REPLACE VIEW EDW.F_SLS_SUM_V  AS
(SELECT * FROM EDW.F_SLS_SUM_A WHERE EXISTS ( SELECT 1 FROM EDW.REFRESH_CNTRL_TAB WHERE ROTATE_TABLE_NAME ='F_SLS_SUM_A' AND ROTATE_STATUS ='ACTIVE' ) )
UNION ALL
(SELECT * FROM EDW.F_SLS_SUM_B
WHERE EXISTS ( SELECT 1 FROM EDW.EFRESH_CNTRL_TAB WHERE ROTATE_TABLE_NAME ='F_SLS_SUM_B' AND ROTATE_STATUS ='ACTIVE' ) );

For example,


CREATE VIEW EDWADM.F_DLY_SLS_REPT_SUM_V AS
(SELECT * from EDWADM.F_DLY_SLS_REPT_SUM_A
WHERE EXISTS (SELECT 1 FROM EDWADM.EDW_REFRESH_CNTRL_TAB WHERE ROTATE_TABLE_NM = 'F_DLY_SLS_REPT_SUM_A' and ROTATE_STATUS = 'ACTIVE')
UNION ALL
SELECT * from EDWADM.F_DLY_SLS_REPT_SUM_B
WHERE EXISTS (SELECT 1 FROM EDWADM.EDW_REFRESH_CNTRL_TAB WHERE ROTATE_TABLE_NM = 'F_DLY_SLS_REPT_SUM_B' and ROTATE_STATUS = 'ACTIVE')
);

In this way, the view F_SLS_SUM_V will return  one of the rotating table A’s or B ‘s data, according to the rotating status of the refresh control table.