Maximum key length (6398) exceeded

Document ID : KB000004026
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

The following error is received when running the script ims_oracle_report.sql:

ERROR at line 1: ORA-01450: maximum key length (6398) exceeded

Resolution:

In addition to the key length error, there is another question/issue here besides rebuilding the database with a larger blocksize so that the script ims_oracle_report.sql can execute successfully-- the oracle optimizer is not likely to use an index with a long key.

From site
http://www.dba-oracle.com/t_ora_01450_maximum_key_length_exceeded.htm
Indexes with long index keys are rarely used!

As a general rule, indexing on very large columns (raw, long, clob) is rarely useful because the optimizer will almost always find a full-table scan cheaper than invoking an index on a long column value.

And it is not recommended to rebuild this index in a larger blocksize. While building indexes in a larger blocksize has some marginal benefits for super high-volume databases (a flatter tree structure, and faster throughput for index range scans), the Oracle CBO will almost always choose a full-table scan over an index on a very large key.

Setting nls_length_semantics in the Oracle database resolves this issue.