关于ORA-01450错误的一点测试与说明.

昨天team这边有个兄弟遇到错误ORA-01450: “maximum key length (6398) exceeded, 这个错误的发生主要是因为Oracle的index实现中一个index key不能同时跨越多个block, 详细描述可以参考Jonathan Lewis的online-rebuild 以及metalin的notes 136158.1.

This index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. In fact, it is required that any index block must contain at least TWO index entries per block.

不过Metalink的note里面有一点错误, 也就是一个index block在oracle9i之后只要要能够放下一个index key就可以创建了.. 也就是在Oracle9i以后, 可以创建一个index key的长度达到block size的80%左右(参考 Jonathan Lewis的上述链接).
我也针对此做了一些简单的测试..

测试之前先在系统中准备几个不同block size的db_cache 设置以及相应block size的tablespace..

create tablespace test_2k datafile
  "e:\oracle\oradata\james\james\test_2k01.dbf" size 100m
block size 2048
extent management local uniform size 1m
segment space management auto
/
create tablespace test_4k datafile
  "e:\oracle\oradata\james\james\test_4k01.dbf" size 100m
block size 4096
extent management local uniform size 1m
segment space management auto
/
--8k 为系统默认的block size.
create tablespace test_8k datafile
  "e:\oracle\oradata\james\james\test01.dbf" size 100m
extent management local uniform size 1m
segment space management auto
/
create tablespace test_16k datafile
  "e:\oracle\oradata\james\james\test_16k01.dbf" size 100m
block size 16384
extent management local uniform size 1m
segment space management auto
/

1. 测试每种block size下面的index key的limit.

SQL> create table test_16k(
  2    col1 varchar2(4000),
  3    col2 varchar2(4000),
  4    col3 varchar2(4000),
  5    col4 varchar2(4000),
  6    primary key (col1,col2,col3,col4) using index (
  7      create index test_16k_col1_ind on test_16k(col1,col2,col3,col4) tablespace test_16k
  8    )
  9  )
 10  tablespace test_16k;
create table test_16k(
*
ERROR at line 1:
ORA-01450: maximum key length (12958) exceeded

SQL> create table test_8k(
  2    col1 varchar2(4000),
  3    col2 varchar2(4000),
  4    col3 varchar2(4000),
  5    col4 varchar2(4000),
  6    primary key (col1,col2,col3,col4) using index (
  7      create index test_8k_col1_ind on test_8k(col1,col2,col3,col4) tablespace test
  8    )
  9  )
 10  tablespace test;
create table test_8k(
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

SQL> create table test_4k(
  2    col1 varchar2(4000),
  3    col2 varchar2(4000),
  4    col3 varchar2(4000),
  5    col4 varchar2(4000),
  6    primary key (col1,col2,col3,col4) using index (
  7      create index test_4k_col1_ind on test_4k(col1,col2,col3,col4) tablespace test_4k
  8    )
  9  )
 10  tablespace test_4k;
create table test_4k(
*
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded

SQL> create table test_2k(
  2    col1 varchar2(4000),
  3    col2 varchar2(4000),
  4    col3 varchar2(4000),
  5    col4 varchar2(4000),
  6    primary key (col1,col2,col3,col4) using index (
  7      create index test_2k_col1_ind on test_2k(col1,col2,col3,col4) tablespace test_2k
  8    )
  9  )
 10  tablespace test_2k;
create table test_2k(
*
ERROR at line 1:
ORA-01450: maximum key length (1478) exceeded

2. 测试每种block size可以支持的index , 仅以varchar2类型来测试.

SQL> begin
  2    for i in 1..4000 loop
  3      begin
  4             execute immediate 'create table test_16k(col1 varchar2('||( 4000 - i)||'),col2 varchar2(4000),col3 varchar2(4000),col4 varchar2(4000),primary key (col1,col2,col3,col4) using index (create index test_16k_col1_ind on test_16k(col1,col2,col3,col4) tablespace test_16k) ) tablespace test_16k';
  5          exit;
  6       exception when others then
  7             null;
  8      end;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> desc test_16k
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COL1                                      NOT NULL VARCHAR2(943)
 COL2                                      NOT NULL VARCHAR2(4000)
 COL3                                      NOT NULL VARCHAR2(4000)
 COL4                                      NOT NULL VARCHAR2(4000)

SQL> begin
  2    for i in 1..4000 loop
  3      begin
  4             execute immediate 'create table test_8k(col1 varchar2('||( 4000- i)||'),col2 varchar2(4000),primary key (col1,col2) using index (create index test_8k_col1_ind on test_8k(col1,col2) tablespace test) ) tablespace test';
  5          exit;
  6       exception when others then
  7             null;
  8      end;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> desc test_8k
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COL1                                      NOT NULL VARCHAR2(2387)
 COL2                                      NOT NULL VARCHAR2(4000)

SQL> begin
  2    for i in 1..4000 loop
  3      begin
  4             execute immediate 'create table test_4k(col1 varchar2('||( 4000- i)||'),primary key (col1) using index (create index test_4k_col1_ind on test_4k(col1) tablespace test_4k) ) tablespace test_4k';
  5          exit;
  6       exception when others then
  7             null;
  8      end;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> desc test_4k
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COL1                                      NOT NULL VARCHAR2(3109)

SQL> begin
  2    for i in 1..2000 loop
  3      begin
  4             execute immediate 'create table test_2k(col1 varchar2('||( 2000- i)||'),primary key (col1) using index (create index test_2k_col1_ind on test_2k(col1) tablespace test_2k) ) tablespace test_2k';
  5          exit;
  6       exception when others then
  7             null;
  8      end;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> desc test_2k
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COL1                                      NOT NULL VARCHAR2(1469)

3. 由于IOT表也即rebuild online可能遇到的那个IOT表的问题, 我也根据Jonathan Lewis blog中的内容做了以下测试.

SQL> create table iot1(
  2     v1      varchar2(4000),
  3     v2      varchar2(2380),
  4     v3      varchar2(100),
  5     constraint iot1_pk primary key(v1,v2)
  6  )
  7  organization index
  8  tablespace test_2k
  9  overflow
 10  ;
create table iot1(
*
ERROR at line 1:
ORA-01450: maximum key length (755) exceeded

SQL> create table iot1(
  2     v1      varchar2(4000),
  3     v2      varchar2(2380),
  4     v3      varchar2(100),
  5     constraint iot1_pk primary key(v1,v2)
  6  )
  7  organization index
  8  tablespace test_4k
  9  overflow
 10  ;
create table iot1(
*
ERROR at line 1:
ORA-01450: maximum key length (1575) exceeded

SQL> create table iot1(
  2     v1      varchar2(4000),
  3     v2      varchar2(2380),
  4     v3      varchar2(100),
  5     constraint iot1_pk primary key(v1,v2)
  6  )
  7  organization index
  8  tablespace test
  9  overflow
 10  ;
create table iot1(
*
ERROR at line 1:
ORA-01450: maximum key length (3215) exceeded

SQL> create table iot1(
  2     v1      varchar2(4000),
  3     v2      varchar2(2380),
  4     v3      varchar2(100),
  5     constraint iot1_pk primary key(v1,v2)
  6  )
  7  organization index
  8  tablespace test_16k
  9  overflow
 10  ;
create table iot1(
*
ERROR at line 1:
ORA-01450: maximum key length (3800) exceeded

经过上述测试可以发现, 如果是普通表, index key的长度基本上可以线性的随着block size的增长而增长.基本上可以创建的index key的长度为( block_size – 192 ) * 80%, 192 差不多为block header, cache header, ITL list, 以及pct free的信息..
而IOT表每个Index Key能够支持的length并没有完全的线性增长关系, 在block_size小于8k的时候, 基本上可以按照公式( block_size – 192 ) * 40来计算, 而当block_size为16k的时候, 只有20%左右. (原因我也不大清楚).

No related posts.

4 comments to 关于ORA-01450错误的一点测试与说明.

  • [...] 第一种情况比较简单,是由于Oracle的一个index key不能跨越多个block而存在造成的,也即一个index key只能在一个block中,这样我们就有一个最直观的结论:如果索引键值的总长度超过了block size大小,这个索引肯定是不可能创建成功的。 实际上,一个index key的总长度只能达到block size的80%左右,计算公式为:max(index_key_value)=(block_size – 192 ) * 80%。这个可以自己进行测试来验证不同block size允许的最大index key,比如4k block是3118。或者参考这篇文章. [...]

  • 你的博客的内容都不错啊, 不过从介绍看不出其它的东西. :-)

  • 呵呵,谢谢鼓励,你的博客很好,从你这里学到很多。你指的其他东西是?

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

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>