Hi Guys,
Need your expert advice and attention as usual.
I have one partitioned table (partition on business date) with unique Index on it. Each partition contains millions of records. So far we have 100 partitions available on this table. We are facing problem while inserting data into new partition in terms of time. The approach what we have followed it given below.
- (1) Create new partition by inserting dummy record for a given business date if does not exist.(Once partition is crated, truncate the partition)
- (2) Drop the global unique index on this table, to speed up the insertion.
- (3) Insert data into a newly created partition.
- (4) Re-create the unique index.
- (5) Gather stats on entire table including index.
Since table has unique index so while re-creating the index its taking big amount of time as it’s getting created on entire data set not just on single partition.
I was just thinking to implement a logic which will affect only partition not the entire table but due to unique index it’s taking lots of time. I just want to avoid re-creating index on entire data set.
Any help on this would be highly appreciated.
Details
Unique Index is created on ITEM_ID.
Table is portioned on BUSINESS_DATE.
Table has local indexes as well.