create table fenbu as select 1 id,'Y' flag from dba_objects where rownum<100001;

insert into fenbu values(1,'N');
commit;

create index IDX_FENBU_FLAG on fenbu(flag);

analyze table fenbu compute statistics for table for all columns for all indexes;

var a varchar2(32);
exec :a:='N';

SQL 10G>set autotrace trace exp;

SQL 10G>alter session set events'10046 trace name context forever,level 12';  

Session altered.

SQL 10G>select * from fenbu    where   flag=:a;

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 50001 |   244K|   202  (80)|
|*  1 |  TABLE ACCESS FULL| FENBU | 50001 |   244K|   202  (80)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG"=:A)

Note
-----
   - 'PLAN_TABLE' is old version

 

SQL 10G>alter session set events'10046 trace name context off';

Session altered.

 

很显然可以看到set autotrace的执行计划是错的，这是因为set auotrace,explain plan等操作
并不会发生bind peeking,它并不会把绑定变量的值反映到执行计划里面，不会去看直方图的
数据分布，所以它生成的计划并不可信，我们可以来看一下10046的真实计划。

select *
from
 fenbu    where   flag=:a

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID FENBU (cr=4 pr=0 pw=0 time=102 us)
      1   INDEX RANGE SCAN IDX_FENBU_FLAG (cr=3 pr=0 pw=0 time=78 us)(object id 83455)

上一篇文章提到压缩表发生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&lt;2;

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

SQL 10G>select rowid from testcom4 where rownum&lt;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, @0x1f79
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 31
col 1: [ 1] 30
bindmp: 01 bc 02 c9 31 c9 30
tab 1, row 0, @0x1f69
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x0300f085.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, @0x1f65
tl: 27 fb: ----FL-- lb: 0x1 cc: 4
hrid: 0x0300efb4.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可以解决这些问题。