Question Fastest Way To Delete Records Of A 100 Gb Table

[catch.saravana]

What is the recommended way to delete a very large table without hampering the regular business operations?
1. DROP TABLE via DF script
2. FOR..EACH and DELETE records (all at once)
3. FOR..EACH and DELETE records (broken into smaller chunks like 10000 records per transaction)
4. PROUTIL TRUNCATE
5. SQL DELETE
or any other?

Note - We have to perform this operation on 9.1E.

 

[Rob Fitzpatrick]

My guess (it's nothing more than that) is that SQL DROP TABLE would be your best bet. The key is to test and document each approach, obviously.

FOR EACH... DELETE will be slow and will generate a lot of BI/AI notes.

 

[catch.saravana]

Okay Rob, will test and get the statistics as you said before I do in PROD.

How about removing the table entry and corresponding index entry from then VST? Will it disintegrate the table from Progress DB? Or will it still be part of our backup and restore?

 

[Rob Fitzpatrick]

How about removing the table entry and corresponding index entry from then VST? Will it disintegrate the table from Progress DB?

You can't remove anything from the VSTs. VSTs are just relational view on in-memory data structures and they are almost entirely read-only.

If you are wondering about removing the table and indexes from the meta-schema, then yes that is what a DROP TABLE (SQL) or Delete Table (Data Dictionary, ABL) does. That deletes the data. In recent OpenEdge releases, either one is fast. But I haven't tested it in 9.1E.

 

[catch.saravana]

Got it @Rob Fitzpatrick. I will test DROP TABLE (SQL) and DELETE TABLE (ABL Data Dict) in 9.1E before testing FOR..EACH..DELETE.

 

[TomBascom]

Truncate area will be fastest with 9.1e

But haven't you said that you are moving to OE 11?

 

[catch.saravana]

Yes Tom, but as these tables are history tables (2 tables each of 100 GB) which is not used by our business anymore we wanted to get rid of these in 9.1E side itself and don't want these tables to be overhead during migration process where in we will have a downtime of 48 hrs. That's our idea on this, please let me know if you would have taken a different approach on this.

I think we may have an issue with TRUNCATE as one of the table is in seperate area which is fine to truncate but the other table is in a area where there are 4 more tables that are required by business. Only after the migration we will have tables categorized properly where large tables will be in separate area, thats not the case in our current 9.1E (it's messy).

As we had some issues with network/data server in our new data center there is another team working on it which is delaying our migration process.

 

[TomBascom]

It sounds like there is a dump & load involved -- so just don't dump the big tables...

If the other tables in the storage area with a mix of tables in it are not too awfully big you could always move them out of that area and then truncate it. The "proutil -C tablemove" utility is very slow, ugly and unpleasant for tables that are more than a modest size (100MB is probably too large) but depending on your situation it might be an option....)

 

[cj_brandt]

If sql drop table will work then use that, but I don't remember if it was available in 9.1E. Make sure you remember to commit after the drop table and make a copy of the table schema.
You don't want delete table via data dictionary.
It might be faster to binary dump the 3 tables that share the same area as the table you want to get rid of. Then after truncating the area, load those tables back.

 

[catch.saravana]

If you are wondering about removing the table and indexes from the meta-schema, then yes that is what a DROP TABLE (SQL) or Delete Table (Data Dictionary, ABL) does. That deletes the data.

@Rob Fitzpatrick - does sqlexp have any impact on AI/BI like how our FOR..EACH..DELETE do? - atleast a minimal impact or doesn't it use AI/BI at all? If it is removing the values from meta-schema and freeing up the blocks/chain, I guess the free space of that area will increase - right? How does the delete work - would like to know more insight about the same? Will those deleted blocks be available for use immediately?

In parallel, I am trying to create couple of tables of 10 GB each and trying to delete just one of them using sqlexp to see how it works; meanwhile thought of posting this question to get a fair bit of idea and compare with the results I get from the exercise that I am doing in my local sandbox/dev area.

 

[TomBascom]

"sqlexp" is a tool that executes SQL commands. SQL update operations write notes to the ai & bi files just like the 4GL does.

Unlike 4GL approaches such as "for each tableName: delete tableName. end" -- DROP TABLE does not delete the individual records. That is why it is fast. It just does some magic to the meta schema and does something equivalent to setting the table's high water mark to zero. Presto! Table gone, blocks freed. (The gory details are probably more interesting than that but you get the idea...) If the transaction doing these things fails for some reason it will be undone by way of BI notes just like any other transaction would be. And once the transaction is committed it can no longer be undone.