Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table in Oracle 10.2g that I'm trying to import roughly 11 million rows into. The table contains an SDO_GEOMETRY column. It previously had NOT NULL constraints on all 3 columns, a primary key constraint on a NUMBER(38,0) column, and a spatial index. I have removed all of these to speed up the import. I removed the primary key constraint using ALTER TABLE DROP CONSTRAINT with the additional parameter DROP INDEX.

However, selecting on USER_INDEXES reveals there are currently two indexes on the table with very strange names: SYS_IL0000471920C00009$$ and SYS_IL0000471920C00010$$. Their INDEX_TYPEs are both LOB, and they both have UNIQUE for their UNIQUENESS column. COMPRESSION is DISABLED. They are both VALID, non-TEMPORARY, and GENERATED (though I'm not sure what GENERATED means).

Where might these indexes have come from? Could dropping them cause problems?

share|improve this question
GENERATED shows whether the object name was automatically generated by the system or assigned by you explicitly when you created the constraint (index). See ALL_INDEXES in Database Reference. – Yasir Arsanukaev May 30 '13 at 7:45
So basically, these indexes come from some constraint I placed on the table, and the way Oracle implements that constraint involves an index. So it automatically generated one, and for some reason, they weren't deleted when I removed the constraint? – jpmc26 May 30 '13 at 19:30

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.