[20151112]ORA-01450?3215?.txt
[20151112]ORA-01450 maximum key length (3215) exceeded.txt
--如果建立的索引键值长度达到一定程度,在做rebuild online时会出现ora-01450错误.今天被别人问一个问题,我重复了测试:
--测试出它的边界,例子:
1.测试环境:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> create table t1(a varchar2(3000),b varchar2(200));
Table created.
SCOTT@test> create index i_t1_a_b on t1(a,b);
Index created.
SCOTT@test> alter index i_t1_a_b rebuild online ;
alter index i_t1_a_b rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
--如果执行如下:
SCOTT@test> drop table t1 purge ;
Table dropped.
SCOTT@test> create table t1(a varchar2(3000),b varchar2(199));
Table created.
SCOTT@test> create index i_t1_a_b on t1(a,b);
Index created.
SCOTT@test> alter index i_t1_a_b rebuild online ;
Index altered.
-- 很明显边界在这个位置,这个主要是做rebuild online时要建立一个IOT表.别人问我问题是这个3215是如何算出来的.
2.如何计算:
--索引的结构与表记录很相似.不同的地方没有字段数量的记数字段。
SCOTT@test> insert into t1 values (lpad('1',3000,'1'),lpad('a',199,'a'));
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select segment_name ,header_file,header_block from dba_segments where owner=user and segment_name='I_T1_A_B';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
I_T1_A_B 4 419
SCOTT@test> alter system checkpoint;
System altered.
SCOTT@test> alter system dump datafile 4 block 420 ;
System altered.
--检查转储文件:
Leaf block dump
===============
header address 290329188=0x114e1264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 4820=0x12d4
kdxcoavs 4782
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[4820] flag: ------, lock: 2, len=3212
col 0; len 3000; (3000):
31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31
... snip...
col 1; len 199; (199):
... snip...
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
col 2; len 6; (6): 01 00 01 8e 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 420 maxblk 420
-- 2+2+3000+2+199+1+6=3212
--注意索引键值长度大于等于128,长度指示器占用2个字节.参考http://blog.itpub.net/267265/viewspace-1291526/.
--很明显算出来的是3212. 并不是3215.即使不考虑前面2个字节，那更加小了。
--参考自己以前写的http://blog.itpub.net/267265/viewspace-732488/,才想起来应该考虑的IOT索引.
3.跟踪看看建立IOT索引：
SCOTT@test> @ &r/10046on 12
Session altered.
SCOTT@test> alter index i_t1_a_b rebuild online ;
Index altered.
SCOTT@test> @ &r/10046off
Session altered.
--检查跟踪文件存在如下语句：
create table "SCOTT"."SYS_JOURNAL_54110" (C0 VARCHAR2(3000), C1 VARCHAR2(199), opcode char(1), partno number, rid
rowid, primary key( C0, C1 , rid )) organization index TABLESPACE "USERS"
SCOTT@test> select dump(rowid,16) c40,rowid from t1 ;
C40
----------------------------------------
Typ=69 Len=10: 0,0,d3,5d,1,0,1,8e,0,0
--按照这个计算:注意IOP表仅仅逻辑rowid的概念，没有物理rowid。
--2+2+3000+2+199+1+10=3216?多了2个字节，如果不包括前面的flag,lock等信息的2个字节,长度就是3214.没有超出范围。
--如果在增加1个字节就超出范围了。也许这3215就是这样计算出来的。
4.继续验证:
-- 2+3000+2+199+1+10=3214
SCOTT@book> CREATE table t2_iot (C0 VARCHAR2(3000), C1 VARCHAR2(211), primary key( C0, C1 )) organization index;
CREATE table t2_iot (C0 VARCHAR2(3000), C1 VARCHAR2(211), primary key( C0, C1 )) organization index
*
ERROR at line 1:
ORA-01450: maximum key length (3215) exceeded
SCOTT@book> CREATE table t2_iot (C0 VARCHAR2(3000), C1 VARCHAR2(210), primary key( C0, C1 )) organization index;
Table created.
--正好在这个边界发生ORA-01450。
insert into t2_iot values (lpad('1',3000,'1'),lpad('a',210,'a'));
insert into t2_iot values (lpad('2',3000,'2'),lpad('b',210,'b'));
commit ;
--IOT真正保存数据信息的是索引,先查询索引,再查询索引段.
SCOTT@book> select index_name from dba_indexes where owner=user and table_name='T2_IOT';
INDEX_NAME
------------------------------
SYS_IOT_TOP_88797
SCOTT@book> select segment_name ,header_file,header_block from dba_segments where owner=user and segment_name='SYS_IOT_TOP_88797';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
SYS_IOT_TOP_88797 4 682
SCOTT@book> alter system dump datafile 4 block 683 ;
System altered.
Block header dump: 0x010002ab
Object id on Block? Y
seg/obj: 0x15ade csc: 0x00.18923b itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x10002a8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0007.017.000003bc 0x00c006aa.012b.02 --U- 2 fsc 0x0000.00189279
Leaf block dump
===============
header address 140593372824164=0x7fde720b8264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 1600=0x640
kdxcoavs 1560
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[4816] flag: ------, lock: 2, len=3216
col 0; len 3000; (3000):
31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31
...
31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31
col 1; len 210; (210):
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
61 61 61 61 61 61 61 61 61 61
row#1[1600] flag: ------, lock: 2, len=3216
col 0; len 3000; (3000):
32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32
...
32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32 32
col 1; len 210; (210):
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
...
62 62 62 62 62 62 62 62 62 62
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 683 maxblk 683
BBED> set dba 4,683
DBA 0x010002ab (16777899 4,683)
BBED> map
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 683 Dba:0x010002ab
------------------------------------------------------------
KTB Data Block (Index Leaf)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdxle, 32 bytes @100
sb2 kd_off[2] @132
ub1 freespace[1560] @136
ub1 rowdata[6432] @1696
ub4 tailchk @8188
--剩余空间1560.至于为什么选择3215作为IOT索引maximum key length，我就不知道了。
