While creating a composite index in the oracle database, got

SQL> create index EMP_TAB_IDX1 on EMP_TAB ( EMPNAME,LOCATION,DEPT);
create index EMP_TAB_IDX1 on EMP_TAB ( EMPNAME,LOCATION,DEPT)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

PROBLEM:

SQL> create index EMP_TAB_IDX1 on EMP_TAB ( EMPNAME,LOCATION,DEPT);
create index EMP_TAB_IDX1 on EMP_TAB ( EMPNAME,LOCATION,DEPT)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

SOLUTION:

As per oracle documentation – For a database with block size of 8K , Index length can be maximum of 6398.
What is index length then?

Lets describe the table

SQL> desc EMP_TAB
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 EMPNO                                              NUMBER
 EMPNAME                                            VARCHAR2(3000)
 LOCATION                                           VARCHAR2(3000)
 DEPT                                               VARCHAR2(2000)

    Here we are trying to create composite index on empname varchar2(3000) , location varchar2(3000) , dept varchar2(2000).
If we add the length 3000+3000+2000=9000 > 6398 . It is greater than the allowed value.

So to avoid this error, either we have to create the index with length less than 6398 . Else we can create a tablespace with higher block size value( like 16k/32k) and create the index in that tablespace . 

SQL> create index EMP_TAB_IDX1 on EMP_TAB ( EMPNAME,LOCATION);

Index created.

Above index creation worked, because 3000 + 3000 < 6398 .