Delete performance

[Hattusas]

We have some large tables that have many records inside. These tables have reasonable foreign keys for keeping up the RI.
Also the child tables that share the Id of the basic tables have many records too.
We are having difficultly that is to say the performance is extremely low while attemping to delete multiple records from the basic table.
I am plannig to put indexes on child tables to improve the delete performance.BUT those child tables also carry many critical data and they have different indexes for speeding up fetch performance and their fetch query results are good.
What confuses me is that:
Would it be logical if I put more indexes on child tables in order to speed up the delete performance of the basic table(s)?Since these child tables have indexes of their own,I won't have any chance of reorganizing my table with that new index because it may improve my delete performance but will decrease my fetch performance?
As a summary what is the best way to optimize the delete performance without decreasing the fetch performance of my queries in that situation?
Thanks.

Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr

 

[stevexff]

If there is no suitable index on the child tables, then deleting a row from the basic table will cause a tablespace scan of the child table to make sure that RI is enforced.

Try running an EXPLAIN on the DELETE statement to see what results you get.

I suspect that you want to fetch the data many more times than you want to delete it, and so fetch performance is more important than delete performance. But if you add some indexes on the child tables on the foreign key columns, it may help DB2 to find the correct rows when you want to delete. You don't need to reorganise the tables. Consider a child table with 2 million rows. I have a basic row that is an FK for 35 rows on the child table. The child table has no index on the FK column. If you delete the row from the basic table, DB2 has to scan the whole child table to find them. Now imagine what happens when you delete multiple rows from the basic table in one SQL call... Any kind of index on the FK column will allow DB2 find the rows explicitly, and delete them, without slurping in the whole table every time.