细说ora-01450错误

ora-01450是索引维护中可能会出现的错误。首先通过oerr来看一下01450的相关信息：

SQL> ho oerr ora 01450
01450, 00000, "maximum key length (%s) exceeded"
// *Cause:
// *Action:

呵呵，oracle竟然没有给出合理建议。但是从字面意义可以看出，01450是由于我们的关键字超出了某些限定值而引起的。oracle不推崇单条索引记录占用较大的存储空间，在9i之前的版本，oracle规定每个数据块至少存放两条完整索引记录，在9i之后条件放宽，每个数据块至少存放一条完整索引记录。毫无疑问，在数据块中除去索引记录本身的数据，还存储了一些辅助信息（大概占用了192个字节），除此之外，每条索引记录还包含了除key值之外的附加信息，如rowid等。因此，在创建索引时，被索引列的长度是受限制的，大概范围为（blocksize-192）*80%。

下面我们来验证一下：

在8k的表空间中：被索引列的长度不可以超过 （8192-192）*0.8=6400

SQL> create tablespace ts8k datafile '/oracle/app/oradata/easy/e01.dbf' size 10m blocksize 8192;

表空间已创建。

SQL> create table t8(c1 varchar2(1000),c2 varchar2(2000),c3 varchar2(3000),c4 varchar2(4000)); 

表已创建。

SQL> create index ind8 on t8(c1,c2,c3,c4);
create index ind8 on t8(c1,c2,c3,c4)
                     *
第 1 行出现错误:
ORA-01450: 超出最大的关键字长度 (6398)


SQL> create index ind8 on t8(c2,c3,c4);
create index ind8 on t8(c2,c3,c4)
                     *
第 1 行出现错误:
ORA-01450: 超出最大的关键字长度 (6398)


SQL> create index ind8 on t8(c1,c2,c3);

索引已创建。

在16k的表空间中：被索引列的长度不可以超过 （16384-192）*0.8=12593.6

SQL> create tablespace ts16k datafile '/oracle/app/oradata/easy/s01.dbf' size 10m blocksize 16384;

表空间已创建。


SQL> create table t16(c1 varchar2(4000),c2 varchar2(4000),c3 varchar2(4000),c4 varchar2(4000)) tablespace ts16k;

表已创建。


SQL> create index ind16 on t16(c1,c2,c3,c4) tablespace ts16k;
create index ind16 on t16(c1,c2,c3,c4) tablespace ts16k
                      *
第 1 行出现错误:
ORA-01450: 超出最大的关键字长度 (12958)


SQL> create index ind16 on t16(c1,c2,c3) tablespace ts16k;

索引已创建。

我想到这里，大家肯定对ora-01450有了基本的认识。

下面我们rebuild一下索引：

SQL> alter index ind8 rebuild;

索引已更改。

SQL> alter index ind8 rebuild online;
alter index ind8 rebuild online
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01450: 超出最大的关键字长度 (3215)

非常奇怪的现象，通过online重建索引再次报01450错误，并且长度的上限为3215，而不是与8k对应的6398。这是因为，rebuild online的过程中，oracle会创建索引组织表IOT.下面我们手动创建索引组织表：

CREATE TABLE "TIO8" 
   ("C1" VARCHAR2(4000 BYTE), 
"C2" VARCHAR2(4000 BYTE), 
"C3" VARCHAR2(4000 BYTE), 
"C4" VARCHAR2(4000 BYTE), 
 CONSTRAINT "TIOC" PRIMARY KEY ("C1") ENABLE
   ) Organization Index Nocompress Pctfree 10 Initrans 2 Maxtrans 255 Logging
  Tablespace "TS8K" 
  Pctthreshold 51 Overflow
 10    TABLESPACE "TS8K" ;
  Pctthreshold 51 Overflow
               *
第 9 行出现错误:
ORA-25179: 无效的 PCTTHRESHOLD 存储选项值


 CREATE TABLE "TIO8" 
   ("C1" VARCHAR2(4000 BYTE), 
"C2" VARCHAR2(4000 BYTE), 
"C3" VARCHAR2(4000 BYTE), 
"C4" VARCHAR2(4000 BYTE), 
 CONSTRAINT "TIOC" PRIMARY KEY ("C1") ENABLE
   ) Organization Index Nocompress Pctfree 10 Initrans 2 Maxtrans 255 Logging
  Tablespace "TS8K" 
  Pctthreshold 50 Overflow
 10    TABLESPACE "TS8K" ;
 CREATE TABLE "TIO8"
*
第 1 行出现错误:
ORA-01450: 超出最大的关键字长度 (3215)

我想到这里大家肯定明白了，索引组织表的pctthreshold不可以大于50，即每个数据块中至少包含两条记录，那么索引的长度上限就变为：（8192-192）*0.8*0.5=3200

再来看一下blocksize为16k的情况：

SQL> 
 CREATE TABLE "TIO16" 
   ("C1" VARCHAR2(4000 BYTE), 
"C2" VARCHAR2(4000 BYTE), 
"C3" VARCHAR2(4000 BYTE), 
"C4" VARCHAR2(4000 BYTE), 
 CONSTRAINT "TIOC" PRIMARY KEY ("C1") ENABLE
   ) Organization Index Nocompress Pctfree 10 Initrans 2 Maxtrans 255 Logging
  Tablespace "TS16K" 
  Pctthreshold 50 Overflow
 10    TABLESPACE "TS16K" ;
 CREATE TABLE "TIO16"
*
第 1 行出现错误:
ORA-01450: 超出最大的关键字长度 (3800)

在32k的blocksize下，最大关键字长度也是3800

SQL> 
 CREATE TABLE "TIO32" 
   ("C1" VARCHAR2(4000 BYTE), 
"C2" VARCHAR2(4000 BYTE), 
"C3" VARCHAR2(4000 BYTE), 
"C4" VARCHAR2(4000 BYTE), 
 CONSTRAINT "TIOC" PRIMARY KEY ("C1") ENABLE
   ) Organization Index Nocompress Pctfree 10 Initrans 2 Maxtrans 255 Logging
  Tablespace "TS32K" 
  Pctthreshold 50 Overflow
 10    TABLESPACE "TS32K" ;
 CREATE TABLE "TIO32"
*
第 1 行出现错误:
ORA-01450: 超出最大的关键字长度 (3800)

看来在索引组织表中，被索引关键字的长度具有一个‘hard limit’即3800.