Kbase P110614: Why does the bi file grow larger than the size of a table that is being deleted via the Data Diction
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
1/9/2009 |
|
Status: Verified
GOAL:
How come when a table is deleted using the Data Dictionary the bi file grows larger than the table being removed?
GOAL:
Why does a bi file grow so large when deleting a table via the Data Dictionary?
GOAL:
How to delete a table without the bi file growing larger than the table being deleted or dropped?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge Category: Database
OpenEdge 10.x
CAUSE:
The scope of the transaction in the Data Dictionary associated with the deletion of a table is a single transaction. This means that there is no way to reuse space within the dictionary.
FIX:
Write a transaction that is narrowly scoped so that space may be reused within the bi file. One way to minimize the size of the bi file growth would be to write a smaller scoped transaction to delete a given amount of records in the table. Then have that code execute in some sort of a repeat loop. This will keep the size of the transaction minimal and allow us to reuse space within the bi file while deleting all the records in the table. Now you can delete the empty table via the schema editor.
If the table were in its own area, simply truncate the area.
Another option, if space was not a concern, is you can dump the .df for the given table that you want to delete. Then go into the schema editor, and modify the name of the table to something like newtablename. Then load in the .df of the table dumped prior to changing the table name. This will now create an empty table in your database with the original table name. Your CRCs will not change as a result of this change. Now people can start adding more records into the original table name which is now empty (if your purpose for deleting the table was to just clear it out). Now you can write code as stated above to delete records from newtablename and keep the scope of the transaction minimal to allow us to reuse space in the bi file. Once all records are deleted, you can delete the empty table via the schema editor.
In OpenEdge Release 10 and making use of Type II storage areas, you could write SQL-92 code to drop the table which is almost instantaneous.