Elaborate on ORA-01450 errors

Source: Internet
Author: User

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:

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

  • 1
  • 2
  • 3
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.