The smallest unit of data storage in Oracle Database is the block. This defaults to 8k (8,192 bytes).
Each index entry must fit within one block. So the maximum size of an indexed value must be less than the block size. Minus some storage overheads.
In a default installation this means the largest indexable value is around 6,400 bytes.
In older releases of Oracle Database, the maximum size of a varchar2 was 4,000 bytes. So you could only hit this problem when creating an index on many of these columns:
create index acco_tab_col_index
on acco_table (
vc_4000_byte_1, vc_4000_byte_2
);
ORA-01450: maximum key length (6398) exceeded
This changed in Oracle Database 12c with the introduction of extended data types. This increased the maximum size of a varchar2 to 32,767 bytes.
Critically, if you’re using a UTF8 character set for your database, each character could consume up to 4 bytes of storage. So if you declare a varchar2 with character semantics,
the maximum size string could be 16,000 bytes.
Below are the Solutions to overcome
Use smaller varchar2 limits
Store the index with a larger block size
Create a standard_hash function-based index
Create a substr function-based index
Define a virtual column
Use Oracle Text
