Elaborate on ORA-01450 errors

Ora-01450 is an error that may occur during index maintenance. First, let's take a look at the relevant information of 01450 through oerr:

Ora-01450 is an error that may occur during index maintenance. First, let's take a look at the relevant information of 01450 through oerr:

Ora-01450 is an error that may occur during index maintenance. First, let's take a look at the relevant information of 01450 through oerr:

SQL> ho oerr ora 01450
01450,000 00, "maximum key length (% s) exceeded"
// * Cause:
// * Action:

Haha, Oracle didn't give reasonable suggestions. But it can be seen from the literal meaning that 01450 is caused by our keyword exceeding certain limits. Oracle does not recommend that a single index record occupy a large storage space. In versions earlier than 9i, oracle requires that each data block should store at least two complete index records, which can be relaxed after 9i, each data block must have at least one complete index record. Without a doubt, some auxiliary information (about 192 bytes) is stored in the data block except the data of the index record, each index record also contains additional information except the key value, such as rowid. Therefore, when creating an index, the length of the indexed column is limited, probably in the range of (blocksize-192) * 80%.

Let's verify it:

In an 8 K tablespace, the length of the indexed Column cannot exceed (8192-192) * 0.8 = 6400

SQL> create tablespace ts8k datafile '/oracle/app/oradata/easy/e01.dbf' size 10 m blocksize 8192;

The tablespace has been created.

SQL> create table t8 (c1 varchar2 (1000), c2 varchar2 (2000), c3 varchar2 (3000), c4 varchar2 (4000 ));

The table has been created.

SQL> create index ind8 on t8 (c1, c2, c3, c4 );
Create index ind8 on t8 (c1, c2, c3, c4)
*
Row 3 has an error:
ORA-01450: exceeds the maximum keyword length (6398)

SQL> create index ind8 on t8 (c2, c3, c4 );
Create index ind8 on t8 (c2, c3, c4)
*
Row 3 has an error:
ORA-01450: exceeds the maximum keyword length (6398)

SQL> create index ind8 on t8 (c1, c2, c3 );

The index has been created.

In a 16 K tablespace, the length of the indexed Column cannot exceed (16384-192) * 0.8 = 12593.6

SQL> create tablespace ts16k datafile '/oracle/app/oradata/easy/s01.dbf' size 10 m blocksize 16384;

The tablespace has been created.

SQL> create table t16 (c1 varchar2 (4000), c2 varchar2 (4000), c3 varchar2 (4000), c4 varchar2 (4000) tablespace ts16k;

The table has been created.

SQL> create index ind16 on t16 (c1, c2, c3, c4) tablespace ts16k;
Create index ind16 on t16 (c1, c2, c3, c4) tablespace ts16k
*
Row 3 has an error:
ORA-01450: exceeds the maximum keyword length (12958)

SQL> create index ind16 on t16 (c1, c2, c3) tablespace ts16k;

The index has been created.

For more details, please continue to read the highlights on the next page:

Related reading:

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store