I have a table XYZ_TBL. The properties of the table are as follows.
No of Columns: 27
No of Rows: Approx 3.4 Milliion Rows
No of new rows inserted when the script runs: approx 250K
No of rows deleted/purged (older than 12 months): 250K approx
There are indexes on following 6 columns with discount of each column approx
acct_period 16 --- this is basically year/month in YYYYMM format
run_date_tag 14 -- year/month YYYYMM format
the part of the script in the stored procedure to delete the records is as follows:
DELETE FROM XYZ_TBL
WHERE run_date_tag = v_date (eg '201010')
OR run_date_tag < vpurgethreshold; (eg '200909')
Deleting the records in the table takes a very long time. May be the operation has to generate large redo files. Also drop all the indexes and rebuild the indexes. I think having too many indexes is also a problem there.
Are there any better alternatives to delete the records from the table.
May be deleting the records in a loop with deleting around 10000 records at a time. I am thinking this will also take pretty much the same time as it has to commit sereral times and do operations multiple times to re-construct the indexes.
May be other alternative is to partition the table based on acct_period (YYYYMM) and delete the partition itself which is not required. I have read some where that this is a good method to delete large no of records in a big table.
Please let me know what your thoughts are? Incase partitioning the table and deleting a partition is the better method, I need to incorporate script in my stored procedure to create partition and delete the partitions when ever the procedure executes. Please provide me with a script to do this. I have not done this before
Also comment on the use of indexes. Are so many indexes required. This application was written by someone and i am tweaking. This is a stand alone table which is used for some reporting purposes and pretty much do not have relation with other tables..