学习Oracle的索引、表的存储

每个索引键值 占用1024 字节 ，同样表的每一行也占用1024字节， 如果超过1024字节之后呢？ 找时间试试[@more@]

create table t1(x char(1024));
create index ti on t1(x);
insert into t1 values(1);
commit;

begin
for i in 2..7 loop
insert into t1 values(i);
end loop;
end;
/
commit;

begin
for i in 2..7 loop
for j in 3..9 loop
insert into t1 values(i*10+j);
end loop;
end loop;
end;
/
commit;

for i in 2..7 loop
for j in 3..9 loop
insert into t1 values(i*100+j*10+1);
end loop;
end loop;
end;
/
commit;

begin
for i in 22..27 loop
insert into t1 values(i);
end loop;
end;
/
commit;

select file_id,extent_id,block_id,blocks from dba_extents where segment_name='TI';

ANALYZE INDEX TI VALIDATE STRUCTURE;

select btree_space,used_space,pct_used,blocks,lf_blks,br_blks from index_stats;

alter system dump datafile 11 block 858;

alter system dump datafile 11 block 877;
alter system dump datafile 11 block 878;

alter system dump datafile 11 block 88
alter system dump datafile 11 block 879;

每个索引键值 占用1024 字节 ， row#0[1822] flag: -----, lock: 0 col 0; len 1024; (1024): 35 34 31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
....
col 1; len 6; (6): 02 c0 03 5d 00 00
同样表的每一行也占用1024字节，
tl: 1030 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [1024]
35 34 31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
如果超过1024字节之后呢？
tl: 1585 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [1576]
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

参考文章：

http://www.itpub.net/viewthread.php?tid=237710&extra=&page=1

http://www.eygle.com/archives/2005/02/ecibtreeeeoaeoe.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9614263/viewspace-1007520/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9614263/viewspace-1007520/