Oracle index maximum key length exceeded

Question

I get error ORA-01450: maximum key length (6398) exceeded as I try creating below index

CREATE INDEX FORENAME_SURNAME ON CARD_HOLDER( REGEXP_REPLACE (UPPER( FORENAME ),'\\s|-|_|\\.|\\:|\\,',''), REGEXP_REPLACE (UPPER( SURNAME ),'\\s|-|_|\\.|\\:|\\,','') );

Error code is fair clear but my columns are 100Bytes each so how is possible index exceed the max??

And db_block_size = 8192 not 6398.

Below columns definition

  CREATE TABLE CARD_HOLDER
(   "CARD_HOLDER_ID" NUMBER NOT NULL ENABLE, 
"TITLE" VARCHAR2(10 BYTE), 
"FORENAME" VARCHAR2(100 BYTE), 
"SURNAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
)

Answer 1

The length of 6398 is the maximal length of the index key in a 8KB block size tablespace as the block must contain the key plus some overhead.

Why are you getting the error for your 100 byte columns can be easily demonstrated.

Let's define a view with your additional columns

create view ch2 as
select c.*, REGEXP_REPLACE (UPPER( FORENAME ),'\\s|-|_|\\.|\\:|\\,','') for2,
REGEXP_REPLACE (UPPER( SURNAME ),'\\s|-|_|\\.|\\:|\\,','') sur2 
from CARD_HOLDER c

If you check the data types of the new columns you see

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH
from user_tab_columns where table_name = 'CH2' and column_name in ('FOR2','SUR2');

COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FOR2         VARCHAR2   4000
SUR2         VARCHAR2   4000

Oracle expects the result of the regexp_replace could have the maximal length of 4000, which end in total of 8000.

So what to do?

Ask Tom has a nice overview of workarounding the problem with key length.

Among others solution you could define a tablespace with a larger block size for your index.

You may also step back and use TRANSLATE (or REPLACE) to remove the unwanted characters such as

TRANSLATE(upper(FORENAME),'x'||CHR(9)||CHR(10)||CHR(11)||CHR(12)||CHR(13)||' -_.:,','x')

Here Oracle asumes the result will be only 400 charater long so there will be no problem with the index key.