How to speed up performance of a Delete statement

[hmackintosh]

I have a question on how to speed up the performance of a delete statement. Here is the background: I have one table (TINST_FIN) that has about 10 reference tables attached to it. The key to this table is ORG_UNIQ_NUM and ORG_EFF_DTE. The primary key of this table is a non-primary key in another table (TINST_BR). The RI rules between TINST_FIN and TINST_BR are restrict delete.

So I added one record to TINST_FIN and no dependent records in TINST_BR. Now I try to delete the one record I added with the following statement:

delete
from sim.tinst_fin
where org_uniq_num = 417636
and org_eff_dte = '2001-01-01'

This statement takes 2 minutes and 15 seconds to execute. The DBA ran a reorg and updated the statistics on all the tables.

Is there something else that can speed up the performance besides removing the RI?

 

[FearsomeFastFemale]

Could you make the RI feild between tables a key ?

 

[shef1234]

hello sir how to see the reply to this post????

 

[akshay127]

I take it that the parent table is TINST_FIN, and TINST_BR is the dependent table.

Although you added only one parent record with no children, deleting a parent means checking all children, just to be sure.

Thus you could temporarily disable/drop this constraint! This is of course only dangerous in that others could insert orphans into TINST_BR during your process, this is up to you to manage.

 

[grofaty]

Hi,

1. Have you tried to make an composite index on columns "org_uniq_num" and "org_eff_dte".

create index index_name on sim.tinst_fin (org_uniq_num asc, org_eff_dte asc)

2. Run statistics after creation of indexes.
runstats on table sim.tinst_fin with distribution and detailed indexes all

3. Try delete command again.

Hope this helps,
Grofaty

 

[hmackintosh]

Found out the answer. The RI between the two tables was fine. What was not okay was that the index on the child table was supposed to be by ORG_UNIQ_NUM and then ORG_EFF_DTE (effective date). Instead the index was ORG_EFF_DTE and then ORG_UNIQ_NUM. We switched them and everything is working great. Thanks for those who responded.