細說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.