We have a large interface table with more than 12m records, unfortunately, most of them are useless, we’d like to clean it up for performance optimization.
Delete useless data by ‘delete from ’ and subsequently shrink the table to lower the HWM? It’s not a good choice. DBA hates that as delete clause would generate huge log, probably the operation would fail.
Truncate the table? I just can’t do that, some data couldn’t be eliminated now.
Here is the DBA’s suggestion:
Step 1: Select the useful data into a new table table_tmp;
Step 2: rename the large table table_origin to table_old;
Step 3: rename table_tmp to table_origin. Now we have a much smaller table_origin with normal HWM.
Step 4: Take care of the indices and the privileges;
Step 5: ensure the table_origin is healthy and then drop table_old;
Delete useless data by ‘delete from ’ and subsequently shrink the table to lower the HWM? It’s not a good choice. DBA hates that as delete clause would generate huge log, probably the operation would fail.
Truncate the table? I just can’t do that, some data couldn’t be eliminated now.
Here is the DBA’s suggestion:
Step 1: Select the useful data into a new table table_tmp;
Step 2: rename the large table table_origin to table_old;
Step 3: rename table_tmp to table_origin. Now we have a much smaller table_origin with normal HWM.
Step 4: Take care of the indices and the privileges;
Step 5: ensure the table_origin is healthy and then drop table_old;