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?

  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.