Extraneous Unique Index in Oracle DB DDL

Question

I exported the DDL for the EMPLOYEE table in an Oracle DB. What is this index? What columns is this index for?

CREATE UNIQUE INDEX "MY_USER"."SYS_IL0000126968C00005$$" ON "MY_USER"."EMPLOYEE" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
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 "DATA"
PARALLEL (DEGREE 0 INSTANCES 0) ;

This query does not show the above index. How do I query for the above unique index?

select
   TABLE_NAME,
   INDEX_NAME,
   UNIQUENESS,
   NUM_ROWS,
   CONSTRAINT_INDEX,
   OWNER,
   TABLE_OWNER
from
   SYS.ALL_INDEXES;

Answer 1

That is a LOB index, an internal data structure for accessing LOBs. Such indexes are automatically created for LOB columns, which you have in your table.

Interestingly, a LOB index is not listed in ALL_INDEXES by design, but it is listed in DBA_INDEXES or USER_INDEXES (queried as owner).

You can find the column in [DBA|ALL|USER]_LOBS using the name of the index.

select column_name from all_lobs
where index_name = 'SYS_IL0000126968C00005$$';