Oracle使用Online子句创建索引报错ORA-01450: maximum key length (3215) exceeded

 

Oracle使用Online子句创建索引报错ORA-01450: maximum key length (3215) exceeded

 

版本：11.2.0.4.0

生产有一张表使用online方式创建索引的时候报错，如下：

SQL> create index idx_name on test(name) online;
create index idx_name on test(name) online
                         *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

 

如果不使用online子句创建则正常。

 

这种一般来说需要借助10046功能，由于11g默认有个10046 trace文件无法显示完整create table的bug，需要先打上补丁18705302。

详见：Oracle Trace文件的创建表(create table)语句显示不完整

实际Oracle Trace文件的创建表(create table)语句显示不完整这篇文章示例语句正是以online创建索引为例子的。

可以看出，create index ... online会内部会创建临时的索引组织表。

（PS：当然表不能是空段，也就是延迟段创建刚创建的没有数据的新表，不然即使是online也不会有IOT表产生）。

结合报错ORA-01450: maximum key length (3215) exceeded一开始还以为是存储的字段内容不能超过3215长度，实际上不是这样。

 

报错重现

创建测试表

由于使用了延迟段创建，即使online也不会报错。

SQL> create table test ( name varchar2(4000));

Table created.

SQL> select * from user_segments where segment_name='TEST';

no rows selected

SQL> create index idx_test on test (name) online;

Index created.

SQL> drop index idx_test;

Index dropped.

 

 

插入数据申请表段，回滚，online创建索引。

可以看出即使没有数据，在存在表段的前提下online创建索引也会报错ORA-01450。

SQL> insert into test values ('a');

1 row created.

SQL> rollback;

Rollback complete.

SQL> select count(*) from test;

  COUNT(*)
----------
         0

SQL> select count(*) from user_segments where segment_name='TEST';

  COUNT(*)
----------
         1

SQL> create index idx_test on test (name) online;
create index idx_test on test (name) online
                         *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

 

 

开启10046，重新online创建索引。

SQL> select value from v$diag_info where name like '%De%';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/zkm/zkm/trace/zkm_ora_2867.trc

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> create index idx_test on test (name) online;
create index idx_test on test (name) online
                         *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded


SQL> alter session set events '10046 trace name context off';

Session altered.

 

 

查看10046，可以看到创建IOT表的语句：

create table "ZKM"."SYS_JOURNAL_87429" (C0 VARCHAR2(4000),  opcode char(1), partno number,  rid rowid, primary key( C0 , rid )) organization index TABLESPACE "USERS"

 

 

实际上，经过测试可以发现，IOT表的字段长度不能超过3212字节。

SQL> create table iot_test ( name varchar2(3213),primary key(name)) organization index;
create table iot_test ( name varchar2(3213),primary key(name)) organization index
*
ERROR at line 1:
ORA-01450: maximum key length (3215) exceeded


SQL> create table iot_test ( name varchar2(3212),primary key(name)) organization index;

Table created.

 

对于narchar2也同样遵循，由于1个字符长度是2字节，所以，IOT表的nvarchar2不能超过3212/2=1606。

SQL> create table iot_test (name nvarchar2(1607),primary key(name)) organization index;
create table iot_test (name nvarchar2(1607),primary key(name)) organization index
*
ERROR at line 1:
ORA-01450: maximum key length (3215) exceeded


SQL> create table iot_test (name nvarchar2(1606),primary key(name)) organization index;

Table created.

 

 

由于IOT表的这种限制，如果表字段本身定义的长度超过3212，则无法使用online子句创建索引。

这种情况下，只能去掉online了。去掉online的话需要注意创建索引期间会阻塞DML操作。