I have a table of 500million records. The table has few indexes on foreign key columns. I joined new to the team and asked our DBA if we can drop any of these indexes if not being used. DBA mentioned Oracle uses them for blocking contentions or updates/deletes even if they look they're not being used.
Can someone shed their thoughts on this?
Do we need to have indexes of all foreign key columns? Or is this just for the large tables? Or do we really need the indexes (even if not used)?
Using Oracle 19c Production, Windows 10