Oracle & Starcraft

上一篇文章提到压缩表发生update后会导致行迁移，但是在上篇文章里面没有做过多描述
，这次我们来仔细看一下update compressed table会发生什么事情。此外我们再来看看压
缩表结构修改是怎么处理的。

首先创建测试表

create table test2(a varchar2(10),b varchar2(10),c varchar2(10));

begin
for i in 1000000000..1000100000 loop
insert into test2 values(i,’1′,to_char(mod(i,100)));
commit;
end loop;
end;
/
SQL 10G>create table testcom4 compress as select * from test2 order by c;
Table created.

对压缩表添加一个列

SQL 10G>SQL 10G>SQL 10G>

alter table testcom4 add d number;

Table altered.

定位到一条记录,找出所在文件号，块号，文件号和rowid

SQL 10G>select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file#,
dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,
dbms_rowid.ROWID_ROW_NUMBER(rowid) row# from
testcom4 where rownum<2;

     FILE#     BLOCK#       ROW#
———- ———- ———-
        12      61364          0

SQL 10G>select rowid from testcom4 where rownum<2;                                           

ROWID
——————
AAAT9AAAMAAAO+0AAA

更新这条记录

SQL 10G>update testcom4 set d=1 where rowid=’AAAT9AAAMAAAO+0AAA’;

1 row updated.

SQL 10G>commit;

Commit complete.

SQL 10G>select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file#,
dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,
dbms_rowid.ROWID_ROW_NUMBER(rowid) row# from testcom4
where  rowid=’AAAT9AAAMAAAO+0AAA’;

     FILE#     BLOCK#       ROW#
———- ———- ———-
        12      61364          0
dump这个block看看行迁移是怎么发生的

SQL 10G>alter system dump datafile 12 block 61364;

System altered.

perm_9ir2[3]={ 2 0 1 }
…
block_row_dump:
tab 0, row 0, @0×1f79
tl: 7 fb: –H-FL– lb: 0×0  cc: 2
col  0: [ 1]  31
col  1: [ 1]  30
bindmp: 01 bc 02 c9 31 c9 30
tab 1, row 0, @0×1f69
tl: 9 fb: –H—– lb: 0×2  cc: 0
nrid:  0×0300f085.0  这里指向了新的数据块
bindmp: 20 02 00 03 00 f0 85 00 00
定位新的块

SQL 10G>select dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number(’300f085′,’xxxxxxxxxx’))
file#,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(to_number(’300f085′,’xxxxxxxxxx’))
block# from dual;

     FILE#     BLOCK#
———- ———-
        12      61573

dump新的block

SQL 10G>alter system dump datafile 12 block 61573;

System altered.

block_row_dump:
tab 0, row 0, @0×1f65
tl: 27 fb: —-FL– lb: 0×1  cc: 4
hrid: 0×0300efb4.0
col  0: [10]  31 30 30 30 30 39 33 30 30 30
col  1: [ 1]  31
col  2: [ 1]  30
col  3: [ 2]  c1 02

可以看到新的block里面已经是非压缩的数据格式了，从这里可以看出对压缩表的更新确实是会导致
压缩失效。
那么能不能删除新加的列呢？试一下

SQL 10G>alter table testcom4 drop column d;
alter table testcom4 drop column d
                                 *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

报错了，提示“unsupported add/drop column operation on compressed tables”
metalink上说这是oracle的一个bug，在10g修复，但是在我的10g r2的版本上还是
不通过。9i的版本更加离谱，连add column都不行。

－－－－－－－－－－－－－－－－－－－－－－－－－－－
SQL 9I> alter table testcom4 add d number;
alter table testcom4 add d number
                         *
ERROR at line 1:
ORA-22856: cannot add columns to object tables
－－－－－－－－－－－－－－－－－－－－－－－－－－－
10g可以进行set unused的操作

SQL 10G>alter table testcom4 set unused column d;

Table altered.
但是drop unused columns依然报错，依然是一个bug

SQL 10G>alter table testcom4 drop unused columns;
alter table testcom4 drop unused columns
*
ERROR at line 1:
ORA-12996: cannot drop system-generated virtual column

希望下次下载一个patch可以解决这些问题。