ORA-01450 When Creating an Index [ID 293599.1]

9876

ID

…

  

ORA-01450 When Creating an Index [ID 293599.1]转到底部

---

修改时间:2013-3-2类型:PROBLEM状态:PUBLISHED优先级:3注释 (0)

In this Document

 Symptoms Cause Solution References

---

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.

Symptoms

When creating an index on an, it fails with the following error:

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

or

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

Cause

Supporting documentation can be found in Adminstrator's Guide:
Managing Indexes

( Estimate Index Size and Set Storage Parameters )

" The maximum size of a single index entry is approximately one-half the data block size "

SQL> select * from v$version;

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

SQL> sho parameter db_block_size
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_block_size integer 8192

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b,c);
create index ind5 on t5(a,b,c)
*
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

With less than 3218 total characters for the index (for 8K block size), the error does not appear:

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b);
Index created.

Solution

There is a restriction on index data length. It depends on the db_block_size. For 8K Oracle block size, it is 3218 on an 8i database, but 6398 on 9.2,10g and 11g databases. So from 9.2 - 11g we have scope to accommodate more, but there is still a restriction and we cannot create the index beyond it.

NOTE:
In fact, the restriction depends on the actual Oracle data block size rather than the DB_BLOCK_SIZE database initialization parameter.
That means that when using tablespaces with a different block size, the restriction depends on the actual block_size of the tablespace used for the index storage

9876

ID

…

  

ORA-01450 When Creating an Index [ID 293599.1]转到底部

---

修改时间:2013-3-2类型:PROBLEM状态:PUBLISHED优先级:3注释 (0)

In this Document

 Symptoms Cause Solution References

---

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.

Symptoms

When creating an index on an, it fails with the following error:

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

or

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

Cause

Supporting documentation can be found in Adminstrator's Guide:
Managing Indexes

( Estimate Index Size and Set Storage Parameters )

" The maximum size of a single index entry is approximately one-half the data block size "

SQL> select * from v$version;

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

SQL> sho parameter db_block_size
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_block_size integer 8192

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b,c);
create index ind5 on t5(a,b,c)
*
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

With less than 3218 total characters for the index (for 8K block size), the error does not appear:

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b);
Index created.

Solution

There is a restriction on index data length. It depends on the db_block_size. For 8K Oracle block size, it is 3218 on an 8i database, but 6398 on 9.2,10g and 11g databases. So from 9.2 - 11g we have scope to accommodate more, but there is still a restriction and we cannot create the index beyond it.

NOTE:
In fact, the restriction depends on the actual Oracle data block size rather than the DB_BLOCK_SIZE database initialization parameter.
That means that when using tablespaces with a different block size, the restriction depends on the actual block_size of the tablespace used for the index storage

9876

ID

…

  

ORA-01450 When Creating an Index [ID 293599.1]转到底部

---

修改时间:2013-3-2类型:PROBLEM状态:PUBLISHED优先级:3注释 (0)

In this Document

 Symptoms Cause Solution References

---

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.

Symptoms

When creating an index on an, it fails with the following error:

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

or

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

Cause

Supporting documentation can be found in Adminstrator's Guide:
Managing Indexes

( Estimate Index Size and Set Storage Parameters )

" The maximum size of a single index entry is approximately one-half the data block size "

SQL> select * from v$version;

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

SQL> sho parameter db_block_size
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_block_size integer 8192

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b,c);
create index ind5 on t5(a,b,c)
*
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

With less than 3218 total characters for the index (for 8K block size), the error does not appear:

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b);
Index created.

Solution

There is a restriction on index data length. It depends on the db_block_size. For 8K Oracle block size, it is 3218 on an 8i database, but 6398 on 9.2,10g and 11g databases. So from 9.2 - 11g we have scope to accommodate more, but there is still a restriction and we cannot create the index beyond it.

NOTE:
In fact, the restriction depends on the actual Oracle data block size rather than the DB_BLOCK_SIZE database initialization parameter.
That means that when using tablespaces with a different block size, the restriction depends on the actual block_size of the tablespace used for the index storage

9876

ID

…

  

ORA-01450 When Creating an Index [ID 293599.1]转到底部

---

修改时间:2013-3-2类型:PROBLEM状态:PUBLISHED优先级:3注释 (0)

In this Document

 Symptoms Cause Solution References

---

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.

Symptoms

When creating an index on an, it fails with the following error:

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

or

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

Cause

Supporting documentation can be found in Adminstrator's Guide:
Managing Indexes

( Estimate Index Size and Set Storage Parameters )

" The maximum size of a single index entry is approximately one-half the data block size "

SQL> select * from v$version;

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

SQL> sho parameter db_block_size
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_block_size integer 8192

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b,c);
create index ind5 on t5(a,b,c)
*
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

With less than 3218 total characters for the index (for 8K block size), the error does not appear:

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b);
Index created.

Solution

There is a restriction on index data length. It depends on the db_block_size. For 8K Oracle block size, it is 3218 on an 8i database, but 6398 on 9.2,10g and 11g databases. So from 9.2 - 11g we have scope to accommodate more, but there is still a restriction and we cannot create the index beyond it.

NOTE:
In fact, the restriction depends on the actual Oracle data block size rather than the DB_BLOCK_SIZE database initialization parameter.
That means that when using tablespaces with a different block size, the restriction depends on the actual block_size of the tablespace used for the index storage.

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.

Symptoms

When creating an index on an, it fails with the following error:

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

or

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

Cause

Supporting documentation can be found in Adminstrator's Guide:
Managing Indexes

( Estimate Index Size and Set Storage Parameters )

" The maximum size of a single index entry is approximately one-half the data block size "

SQL> select * from v$version;

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

SQL> sho parameter db_block_size
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_block_size integer 8192

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b,c);
create index ind5 on t5(a,b,c)
*
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

With less than 3218 total characters for the index (for 8K block size), the error does not appear:

SQL> create table t5(a varchar2(1500), b varchar2(1500),c varchar2(1500));
Table created.

SQL> create index ind5 on t5(a,b);
Index created.

Solution

There is a restriction on index data length. It depends on the db_block_size. For 8K Oracle block size, it is 3218 on an 8i database, but 6398 on 9.2,10g and 11g databases. So from 9.2 - 11g we have scope to accommodate more, but there is still a restriction and we cannot create the index beyond it.

NOTE:
In fact, the restriction depends on the actual Oracle data block size rather than the DB_BLOCK_SIZE database initialization parameter.
That means that when using tablespaces with a different block size, the restriction depends on the actual block_size of the tablespace used for the index storage.

References

BUG:3156663 - A VERY LONG QUERY WITH PARALLELISM HINT FAILS WITH ORA-1467