Hi -

Database - UDB 7.1
OS - AIX 4.3

I am having a problem with deleting records that is taking a long time. The delete is quite simple.

Here is the statement:

delete from CTS$.DEBT where DEBT_NO = 0;

There are about 47 millions rows in this table. The column DEBT_NO is indexed. If I make this into a select statement, it comes back in less than 10 seconds. There are no triggers on this table. I do have logging turn on for the database. Does anyone know if logging could cause the slow response time or could it be something else ?

Thank you for your help.

If you have integrity constraints defined with CTS$.DEBT as a parent that could be the reason.

Logging, indexes, and referential constraints are some of the factors that slows down delete in a table. The more indexes you have in a table the slower.

Logging, indexes, and referential constraints are some of the factors that slows down delete in a table. The more indexes you have in a table the slower.

Thank you for all your help. Disabling the constraint and then running the delete work.

When you are doing bulk deletes, inserts, or updates, it may be helpful to know that it is often much slower to update an index over and over (as must be done for each and every row if the index is active), than it is to rebuild the whole darned index from scratch. So, switch off the index, do the bulk operation, and turn the index back on again. When you re-activate the index, it will be rebuilt in a single pass through the table.

Constraints are the same. If you know that the constraint will never be violated by the bulk-op, it is significantly faster to disable the constraint for the duration.

It is also helpful to break large bulk inserts and updates into smaller chunks which can be committed as separate transactions. The larger a transaction becomes, the more a "million-pound elephant" it becomes. And the larger and heavier it becomes, the more vulnerable it is to being slowed down by all sorts of external factors. And certainly, as it becomes disproportionate in size to the other operations that are being done ont the server, the more it impacts (splat!) other users.

Is there actually a way to disable and enable an index? Or do you really mean drop and recreate?