Hi Team,

Hope you are doing well.

I have to partition 4 tables in my application as early as possible. This is my first time partitioning tables with that much volume.

Partitioning strategy that I am going to use:

1. tab_P1 : This is Parent table with 60 millions of records and I am partitioning on Range interval(day) partition on trunc(Event_date_time) column and sub partitioning by List partition on category(category may have 10 to 12 distinct values) column.

This table has one Primary key column (Master_id) which is referenced by below 3 child tables.

2. Tab_c1, Tab_c2, Tab_c3 are three child tables with 550 millions, 280 millions and 120 millions of records respectively.

These three child tables have foreign key referenced to tab_P1(Master_id) column.

So I am using REFERENCE PARTITIONING for these 3 tables.

I am creating partition table as

Create table tab_P1_partition(col1,col2,col3...coln)
PARTITION BY RANGE (Event_date_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY LIST (category)
( PARTITION FIRST_PART VALUES LESS THAN (TO_DATE('01-JAN-2010','dd-MON-yyyy'))
(SUBPARTITION p_c1 VALUES (1),
SUBPARTITION p_c2 VALUES (2),
SUBPARTITION p_c3 VALUES (3),
SUBPARTITION p_c4 VALUES (4),
SUBPARTITION p_c5 VALUES (5),
SUBPARTITION p_cat_oth VALUES (DEFAULT)
)
)TABLESPACE t1 NOLOGGING PCTFREE 0 STORAGE (INITIAL 64M) ENABLE ROW MOVEMENT
AS SELECT /*+ PARALLEL(32) */ * FROM tab_P1;

then rename old table tab_P1 to tab_P1_backup, rename new partition table to original table name.

CREATE TABLE Tab_c1_part
(
co1,col2..coln,
CONSTRAINT fk_master_id FOREIGN KEY (col1)
REFERENCES tab_P1_partition(Master_id) ENABLE
)
PARTITION BY REFERENCE (fk_master_id)
TABLESPACE t1 ENABLE ROW MOVEMENT
AS SELECT /*+ PARALLEL(32) */ * FROM Tab_c1;

Since I am creating the table as select * from --- I am not sure howmuch time it will run and I am not sure if I am using the correct strategy.

I thought of loading the data in periodically may be by months by giving WHERE event_data_time between '01-JAN-2010' AND '31-JAN-2010' condition in select statment but this requires more production tickets to be created.

So kindly advise me if there is any better way or any modifications to this approach. If there is any downtime is required we can have it (may be 2-4 days max) as these tables are not required to be up and run 24*7.