Oracle: Missing foreign key index on ancestors table

The ancestors.hierarchy_id has no index.

Foreign key must be indexed to prevent Oracle TM contention and ORA-00060 dead lock on concurrent DML.

The SQL fix:

CREATE INDEX ANCESTORS_HIERARCHY_ID_IDX ON ANCESTORS(hierarchy_id);

Note that JPBM has lots of unindexed FK.