偶遇ORA-01450

这几天在测试环境部署一个适合多种操作系统多种数据库多实例的监控脚本的时候,在一台测试库上监控到一个索引失效,于是根据提示尝试online rebuild

alter index NINGOO.IDX_TEST_KPI_NAME rebuild online compute statistics;  

SQL> alter index NINGOO.IDX_TEST_KPI_NAME rebuild online compute statistics;
alter index NINGOO.IDX_TEST_KPI_NAME rebuild online compute statistics
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

看了下索引是创建在一个varchar2(4000)的列上的:

SQL>  select table_name,column_name,column_length from all_ind_columns where index_name='IDX_TEST_KPI_NAME';

TABLE_NAME      COLUMN_NAME            COLUMN_LENGTH
--------------- ---------------------- -------------
TEST            KPI_NAME               4000

我们知道一个index key不能跨多个block,所以key的长度有限制的。但是索引既然创建成功,这个要求肯定是满足的。实际测试下,对于8K的block,这个限制应该是6398字节。

SQL>  create table test1(a varchar2(4000),b varchar2(4000));
Table created.

SQL> create index ix_test1 on test1(a,b);
create index ix_test1 on test1(a,b)
                         *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

那为什么4000字节的key在online rebuild的时候会有问题呢?而创建和正常rebuild操作都是正常的。

SQL>  create index ix_test1 on test1(a);
Index created.

SQL> alter index ix_test1 rebuild online;
alter index ix_test1 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

SQL> alter index ix_test1 rebuild;
Index altered.

原因在于IOT表的一些限制,我们可以创建一个IOT表验证一下:

SQL> create table test_iot1(a varchar2(4000),b varchar2(4000),
  2               constraint iot1_pk primary key(a,b))
  3                organization index;
create table test_iot1(a varchar2(4000),b varchar2(4000),
*
ERROR at line 1:
ORA-01450: maximum key length (3215) exceeded

Online rebuild的过程中需要创建一个临时的IOT表,所以online rebuild的index 的key的长度限制就被大大缩短了。这一点在我们设计系统的索引的时候要特别注意,如果前期设计的索引超过了IOT index key的长度限制,则后期的维护成本会更高,因为无法online rebuild,则rebuild的时候就会锁表导致业务受到较长时间的中断。

当然,实际业务场景中,在varchar2(4000)列上创建索引并不常见,如果真的需要,也可以考虑只创建部分前缀的函数索引,但这需要业务的SQL也做相应修改,这一点上,PostgreSQL直接支持的前缀索引就要灵活得多。

SQL> create index ix_test1_2 on test1(substr(a,1,100));
Index created.

SQL> alter index ix_test1_2 rebuild online;
Index altered.

对于这个问题,旺旺同学和Jonathan Lewis同学很早就有描述,只是没碰到一般很少碰到key这么长的索引而不容易注意到,再次记录下备忘。

无觅相关文章插件,快速提升流量

4 条评论

  • At 2012.03.01 14:40, www.96tz.com said:

    文章写的不错,顶一下。。。。加油

    • At 2012.06.12 10:33, 超性福99 said:

      很的,,顶顶。

      • At 2012.06.12 10:37, cxf99.com said:

        学习了,虽然看着有点晕,希望有更好的文章发表

        • At 2012.11.01 23:51, 陈任司沐 said:

          铭盛卡行(QQ1228557129)所出售卡均为一级卡源,全新无任何交易记录,资料齐全,真实身份办理,亦可指定名字办理,诚信淘宝担保交易!
          例如—可以给你淘宝店刷提高您的淘宝信用记录。
          例如—你想接受汇款,但又不想让对方知道自己的真实姓名。
          例如—您想给领导送礼办点事情,但是又不想让自己的隐私暴露!这时您就需要到我们提供办理的卡,

          我们的卡可以让您随意的a转帐–送礼。给客户保密了隐私的同时也去除了客户的后顾之忧。
          本团队以真实代开卡【QQ:1228557129】办理、
          分别提供7大银行卡;中国银行—工商银行—建设银行—农业银行—交通银行—招商银行—邮政银行[网上银行(口令卡,电子证书,U盾)长期供应,保证全新开户,保证开户资料齐全,保证带真实有效开户原件,承接指定名字开户

          我们的宗旨是:质量+速度+信誉!我们希望与
          有长期需要的客户建立长期合作关系,彼此信任,共同赢利。如有需要请联系!非诚勿扰!
          客户的满意是我们的心愿欢迎光顾

          公司宗旨:
          公司执行国家金融方针和政策,在法律、法规规定的范围内开展业务,
          自主经营、自负盈亏、自我约束、自担风险、切实为小企业、农民、农业及农村经济的发展服务。

          公司文化:
          诚信—用心做事,诚信为人 规范—规范管理,依法治企
          高效—真诚服务,高效便捷 和谐—同舟共济,创建和谐

          公司口号:
          以实力、效率立足于市场 用服务、便捷来赢得口碑
          崇尚职业道德,遵守法律法规  急您所急、思您所思

          温馨提示:在本卡行购买的卡可用于收藏使用!


          (Required)
          (Required, will not be published)