Hi,
We are using Oracle 12c Release 12.1.0.2.0 - 64bit
We have some historical tables (which are not partitioned, not indexed since we don't query these tables much, purpose is just to keep archived data after monthly consumption from separate landing tables).
We need to partition all these tables by adding a new date column and partitioning on this new column.
For existing rows, we need to populate this new column, with value of different (existing) date column.
After this change, above new field will be populated by application separately.
Some of these tables have hundreds of millions of rows, 2-3 tables even have upto 3 billion rows each.
The approach I am following to partition these tables is:
CREATE TABLE schema.table_bkp
PARTITION BY RANGE ( month_end_date ) INTERVAL ( numtoyminterval(1,'MONTH') )
( PARTITION part_01 VALUES LESS THAN ( TO_DATE('01/01/2015','DD/MM/YYYY') ) )
AS
SELECT
a.load_date AS month_end_date, --this is the new column being added and populated from existing column
a.column1, a.column2......, a.load_date,....
FROM
schema.table a
;
then rename the original table to different name, and rename the above partitioned table to original name.
Above CTAS is taking lot of time for bigger tables (approx 3 hrs for one 1 billion rows table).
We cannot create an empty table and do partition exchange (split that partition later) because we need to add new column and populate this value for existing rows as well.
Can we use export/import? Not sure if it works, for same reason, new table structure is not same as old one.
Is there any other better approach?
Thanks
Praveen