9876
In this Document
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
In this Document
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
In this Document
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
In this Document
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