1.
Data Dictionary Table Delete is faster than an ABL record delete
- dump .df
- DROP TABLE(S) from the Data Dictionary or with SQL
- load .df (optionally to a different Storage Area)
When the table is in a TYPE II Storage Area, the Data Dictionary Table Delete has an optimization for tables and their indexes which will be much faster than an ABL record delete. When the data are in Type II Storage Areas, the delete is done by (simply) moving the table's or index's chain of clusters onto the Area's free chain. No records are read or deleted individually.
Otherwise an ABL delete or a Dictionary Delete will be on a par for Type I Storage Areas.
For SQL commands to "drop" a table, refer to Article
How to delete a database table and its contents from the command line (proenv)? 2. Use the
TABLE-SCAN option to delete records.
TABLE-SCAN was introduced in OpenEdge 11. When used on a table in a Type II Storage Area it will return rows without using an index, by accessing the record block instead of both record and index blocks, to resolve the records that need deleting. This may be faster than an ABL delete operation that uses the primary or USE-INDEX specified. However if the physical storage order of the records closely match the ordering given by the index otherwise being used, then the TABLE-SCAN option may not result in better delete performance.
3.
Rename the Table:
An alternative approach is to
rename the original table in the Data Dictionary to something other than the original table name (eg. oldTableName)
Load the original table .df back (optionally to a different Storage Area)
Over time the data in the original renamed table may be deleted slowly or all at once.
4. Use the
tablemove truncate command:
In OpenEdge 12.4 and higher the proutil tablemove truncate command, which can also be used online:
https://docs.progress.com/bundle/openedge-database-management/page/Truncate-tables.htmlcan delete all the content of a single table if that table is using type 2 area.
The syntax of the command is:
proutil sports2020 -C tablemove OrderLine "Data Area" "Index Area" "LOB Area" truncate(the existing database areas used by the existing table can be used, the empty table therefore does not need to be moved to new database areas)
