ERR-311E0 The estimated size of the index key exceeds the maximum limit.#
Overview#
This document describes the cause of the ERR-311E0 error, which occurs when the index key size exceeds the maximum limit while creating an index on a disk table, and explains how to resolve it.
Version#
All versions
Symptom#
The following error occurs when an index or a PRIMARY KEY / UNIQUE constraint is created or modified on a disk table.
[ERR-311E0 : The estimated size of the index key exceeds the maximum limit.]
This error mainly occurs during the following operations.
CREATE INDEXCREATE TABLEorALTER TABLE ... ADD CONSTRAINTwith aPRIMARY KEY/UNIQUEconstraintALTER TABLEthat increases the size or changes the type of an index key column
Cause#
Before creating an index, Altibase estimates the index key size based on the defined sizes of the key columns. If the estimated size exceeds the maximum allowed key size, this error occurs.
For an index on a disk table, the total size of the key columns cannot exceed 3,386 bytes. The maximum key size may differ slightly depending on the version.
The key size is calculated from the maximum size defined for each column, not from the data actually stored. Therefore, the error occurs even if the table is empty or the actual values are short. In addition, length information and other overhead are added to each column, so the error may occur even when the sum of the defined lengths is slightly below the limit.
Memory tables do not limit the total size of key columns, so this error rarely occurs with typical column sizes.
Example#
iSQL> CREATE TABLE T1( C1 VARCHAR(1000), C2 VARCHAR(1000), C3 VARCHAR(1000), C4 VARCHAR(1000) )
2 TABLESPACE SYS_TBS_DISK_DATA;
Create success.
iSQL> ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY ( C1, C2, C3, C4 );
[ERR-311E0 : The estimated size of the index key exceeds the maximum limit.]
iSQL> ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY ( C1, C2, C3 );
Alter success.
With three key columns, the estimated size is about 3,000 bytes, which is within the limit. With four key columns, it is about 4,000 bytes, which exceeds the limit and causes the operation to fail.
Action#
Reduce the Number of Index Key Columns#
For a composite key index, include only the columns that are essential for search conditions.
Reduce the Defined Size of Key Columns#
Reduce the defined size (precision) of the key columns to match the actual data length.
iSQL> CREATE TABLE T2( C1 CHAR(1000), C2 CHAR(1000), C3 CHAR(1000), C4 CHAR(1000) )
2 TABLESPACE SYS_TBS_DISK_DATA;
Create success.
iSQL> ALTER TABLE T2 ADD CONSTRAINT T2_PK PRIMARY KEY ( C1, C2, C3, C4 );
[ERR-311E0 : The estimated size of the index key exceeds the maximum limit.]
iSQL> CREATE TABLE T3( C1 VARCHAR(500), C2 VARCHAR(500), C3 VARCHAR(500), C4 VARCHAR(500) )
2 TABLESPACE SYS_TBS_DISK_DATA;
Create success.
iSQL> ALTER TABLE T3 ADD CONSTRAINT T3_PK PRIMARY KEY ( C1, C2, C3, C4 );
Alter success.
Four CHAR(1000) columns (T2) are calculated as about 4,000 bytes, the same as the four VARCHAR(1000) columns (T1) in the example above, so the operation fails. If the actual data does not exceed 500 characters, defining the columns as VARCHAR(500) (T3) reduces the estimated size to about 2,000 bytes, and the index is created successfully.
Create the Table as a Memory Table#
Memory tables do not limit the total size of key columns. However, because all table data must be loaded into memory, this option is suitable for tables whose data is not large. Before converting, check that there is enough memory capacity (MEM_MAX_DB_SIZE and the server's physical memory).
Reference#
Error During Queries in Earlier Versions#
In Altibase 6.1.1 or earlier, this error may also occur when performing joins, ORDER BY, GROUP BY, and similar operations on disk tables. In this case, you can avoid the error by using the TEMP_TBS_MEMORY hint, which uses the memory temporary area instead of the disk temporary tablespace.
SELECT /*+ TEMP_TBS_MEMORY */ * FROM table_name ORDER BY column_name;
In Altibase 6.3.1 or later, this error does not occur in this case.