> 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?