Unanswered: Truncating Daily partitions with constraints
I am currently working with a vendor that has a check database that we are back loading data into (15yrs worth). The database is Oracle9i, using daily partitions. The problem I am running into is that if a load for a day fails I have to go back and truncate the partition for that specific day. This process is taking ages now that we have a large amount of data loaded. The most time consuming part is re-enabling the constraints on the tables. My usually steps to truncate the failed load is to, disable constraints, truncate partition, enable constraints. The avg time is around 3 hrs to do this now and its very very unacceptable.
Is there any quicker way to truncate these daily partitions that have failed loads?
Would I be better off dropping all the constraints, truncating the partition, and then recreating the constraints?
How many rows are you deleting. If it is a small amount it might be better to do a delete statement instead of truncate as then you dont have to worry about reenabling the constraint. (ALTER TABLE...MODIFY PARTITION...NOLOGGING may reduce the amount of logging aswell).
Also why is your load failing. If it is failing for a particular reason would it be possible to check before hand if this error is going to occur i.e. load the data into a temp table, run a query to see if the load is going to work and then copy the data if it is.
The current database we are loading into has around ~715,000,000 rows at the moment. Each daily partition is made up of ~3,000,000 rows. The load fails occur when there are miss matched dates on the icre / mcre check files. This is something there is really no wa to check on the Oracle side initially, but can be done on the mainframe.
The failed loads have maybe a thousound or so rows loaded, so the delete may be the way to go. I will give that a try later today and see how it goes.
I assume the following syntax should be fine:
Delete * from <tablename> where date='failed load day';