> 2)Should we be creating composite indexes on each foreign key for table2 and table3, because > any update or delete on parent is going to take lock on all child tables?
Every foreign key needs its own index. A composite index is only appropriate if the foreign key spans multiple columns.
From the DDL which OP posted it's using composite foreign key thus a composite index would be needed.
However, if someone doesn't delete or update the parent table PK , is it still advisable to have all the FK indexed? Like in general I think transaction id should not get updated in a normal scenario unless some special case.
Thank you. I can double check if we have confirmed use case of deleting the parent table or updating PK in the parent table. But anyway it can happen for data fix for sure in some scenario.
But yes, we are certainly going to drop/purge partition from all the parent and child table after specific days. So isn't that need the FK to be indexed or else it will scan whole parent table partition?
I am not sure if drop partition of parent table, will have a lock or will do a full scan on the child table while doing the partition maintenance or dropping the partitions, in absence of foreign key index. Others may comment here.
Can you please help me understand, If it's true that all the statements like Delete, Update and Drop partition of parent table will take lock on the child table and Full scan the child table , and thus foreign key index on all the child table is necessary irrespective of the performance overhead it has on all the INSERT queries into the child tables?