CODEWITHSUNDEEP
databaseoracle-databaseindexinglob
rchojn
rchojn

Reputation: 39

Oracle SYS.ILOxxx indexes created automatically even though there is no LOB object

During creation of tables in OracleDB there are indexes connnected with table created automatically. They are LOB indexes as SYS.ILOxxx. What is the reason? How to delete those indexes? 

    create table test_table (
    very_long_title varchar2 (1500 char)
);

Upvotes: 1

Views: 3374

Answers (1)

Jon Heller
Jon Heller

Reputation: 36808

Your table is using an extended data type. When you ask for 1500 char with a UTF8 character set, each character could use up to 4 bytes, which means you're really asking for a 6,000 byte column. For strings over 4,000 bytes Oracle secretly uses LOBs to store the data. LOBs are stored in a separate segment from the table, and need an index to access their values.

So while Oracle supports 32K bytes of data for a VARCHAR2, they kind of cheated, and you will run into these weird issues.

This issue may not be reproducible for others because if their MAX_STRING_SIZE is set to STANDARD, the 1500 char will max out at a smaller size and not use LOBs.

select value
from v$parameter
where name like 'max_string_size';

Upvotes: 4

Related Questions