Hello,
I accidentally dropped an index on a table (Oracle 19c) that was constructed on three columns; one of them was a foreign key.
table:
INDEX_DEFINITION:
CREATE INDEX "dba"."INDEX_SOP" ON "dba"."USR_SOP_STD" ("TS_ID", "TS_ASSET_SUBDOMAIN", "TS_TITLE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TT_DATA" ;The column TS_ASSET_SUBDOMAIN is a foreign key.
The impact on the performance was notable. I have a backup of the database before dropping the index. How can I check what exactly happened during the drop index? How can I fix this?
Many thanks.
Kind regards,
Ioana Deaconu
You've got the index definition - recreate it! You can add the ONLINE clause to allow writes to the table while this is running.
TS_ASSET_SUBDOMAIN being a foreign key here is irrelevant; indexes on foreign keys are specifically about reducing locks when updating or deleting parent keys. To do this the foreign key columns must be the first columns in the index. That's not the case here.
So I suspect this is simply a matter of queries switching to full table scans (or possibly less effective indexes).
How can I check what exactly happened during the drop index?
Other than the database removing the index? What are you trying to understand here?