Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

Thanks for the question, Ioana.

Asked: February 13, 2025 - 3:15 pm UTC

Last updated: February 13, 2025 - 5:44 pm UTC

Version: Oracle 19c

Viewed 1000+ times

You Asked

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

and Chris said...

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?

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.