ERR-311E0

The estimated size of the index key exceeds the maximum limit

Version
Explanation
Cause
Action
Reference

Version

6.1.1 or below

This error message is not output for 6.3.1 or above.

Explanation

This error occurs when an ORDER BY, GROUP BY or JOIN is used for disk tables.

Cause

The following error description can be viewed with the AltiErr utility:

$ altierr 0x311E0
0x311E0 ( 201184) qpERR_ABORT_QDX_MAXIMUM_KEY_SIZE_EXCEED The estimated size of the index key exceeds the maximum limit.

# *Cause:

#- The estimated size of the index key exceeds the maximum limit.

# *Action:

# – Please reduce the number of key columns.

The temporary tablespace is used when an ORDER BY, GROUP BY or JOIN is executed on a disk table.

The temporary tablespace is a disk tablespace and has the fixed page size of 8K.

However, this error occurs when a record that is bigger than 8K was created while using the temporary tablespace.

Note: If the size of a single page (8K) of the disk tablespace is exceeded, the data length is approximately greater than or equal to 3000 bytes.

# Example

iSQL> CREATE TABLE T1( I1 CHAR(6000) ) TABLESPACE SYS_TBS_DISK_DATA;
Create success.
iSQL> insert into t1 values(1);
1 row inserted.
iSQL> insert into t1 values(2);
1 row inserted.
iSQL> insert into t1 values(3);
1 row inserted.
iSQL> SELECT * FROM T1 ORDER BY I1;

ERR-311E0 : The estimated size of the index key exceeds the maximum limit.

# Example(6.3.1)

iSQL> CREATE TABLE T1( I1 CHAR(6000) ) TABLESPACE SYS_TBS_DISK_DATA;
Create success.
iSQL> insert into t1 values(1);
1 row inserted.
iSQL> insert into t1 values(2);
1 row inserted.
iSQL> insert into t1 values(3);
1 row inserted.
iSQL> SELECT * FROM T1 ORDER BY I1;
I1
--------------
1
2
3
3 rows selected.

Action

1. You can use the hint ‘TEMP_TBS_MEMORY’ to avoid the size limit (8K) of disk temporary tablespaces.

When this hint is used, the temporary area is used in memory instead of disk. Thus, query performance is also improved.

iSQL> SELECT  /*+ TEMP_TBS_MEMORY */ * FROM T1 ORDER BY I1;
I1
--------------
1
2
3
3 rows selected.

2. Upgrade to 6.3.1 or above. This error does not occur in 6.3.1 or above.

Reference

How to use a hint

iSQL>SELECT * FROM t1 ORDER BY c2;
iSQL>SELECT /*+ TEMP_TBS_MEMORY */ * FROM t1 ORDER BY c2;

These documents are for informational purposes only. These information contained herein is not warranted to be error-free and is subject to change without notice. Decisions pertaining to Altibase’s product characteristics, features and development roadmap are at the sole discretion of Altibase. Altibase may own related patents, trademarks, copyright or other intellectual property rights of products and/or features discussed in this document.