Oracle – Exchange partitions between two List partitioned tables

etloraclepartitioning

Need some help here.

I have an ETL process which loads the data into a target table A. We have created another table B which is same as the target table in structure and this table is accessed by reporting team to generate reports. This is done to minimize the downtime for report generation. This way the reports are always accessing the latest data.

Target table B is a List partition table, partitioned on Client ID. ETL team loads data for each client in the respective partition, in Table A.

I tried doing it with Exchange partition: exchange partition mechanism to swap the segments of A and partitioned table B. But couldn’t do it as both tables are List partitioned and Oracle doesn’t like that. I created partitions so as to avoid creating multiple tables (TableA_ClientId) for each Client.

My other option: whenever data is loaded into target table A, rename the table B as table Temp, table B as table A and table A as table Temp.

Can you please suggest a better approach.

Best Answer

Answer left in a comment by Andrew Sayer:

You can create a non-partitioned staging table with the right structure. You then partition exchange from Table_A to the staging table, then from the staging table to Table_B. What are you going to do with the old data now in Table_A?

Related Solutions

Sql-server – Optimizing queries against a set of tables that contain partitioned data

Before table partitioning was introduced the way of doing the very thing you asked was called Partitioned Views:

A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if from one table.

Partitioned Views are not only a UNION ALL over each table. By using appropriate check constraints on the underlying tables partitioning column the optimizer can do some fancy stuff, including partition elimination.

PS. Obviously you can generate the view pragmatically. You can even use Event Notifications to generate it automatically every time your 3rd party tool creates a new table, assuming you're running on SQL Server 2005 or later.

SQL Error: ORA-14300: While partitioning and subpartitioning

I had to explicitly define all my range partitions. Interval partitioning did not work well with sub-partitioning template.

Oracle does HARD Limit of 1m partitions to my interval partitions.

I defined partitions as

PARTITION jan06 VALUES LESS THAN (TO_DATE('01-FEB-2006','DD-MON-YYYY')),
...
ALL THE WAY FOR NEXT 20 YEARS..
...
PARTITION jan26 VALUES LESS THAN (TO_DATE('01-FEB-2026','DD-MON-YYYY')),

Refer here and there

Best Answer

Related Solutions

Sql-server – Optimizing queries against a set of tables that contain partitioned data

SQL Error: ORA-14300: While partitioning and subpartitioning

Related Question