On Fri, 23 Feb, 2024, 1:28 pm yudhi s, <learnerdatabas...@gmail.com> wrote:
> > > On Fri, 23 Feb, 2024, 1:20 pm sud, <suds1...@gmail.com> wrote: > >> >> >> On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, <laurenz.a...@cybertec.at> >> wrote: >> >>> On Fri, 2024-02-23 at 02:05 +0530, yudhi s >>> >>> > 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. > >>
