|
sp; 64 31 02 c1 0b 06 02 c0 1d a5 00 00
--//而shrink space后,索引的键值发生了变化,变为如下:
--//key: (12): 03 c2 64 31 ff 06 02 c0 1d a5 00 00
--//0xff表示NULL,参考链接:http://blog.itpub.net/267265/viewspace-2120439/=>[20160619]NULL在数据库的存储.txt
--//也就是索引的第2字段oracle认为是NULL,也就是遇到这样的情况shrink space时.oracle错误的认为Y=null,
--//因为这样的情况Y=10的值并没有保存在数据块中,而是放在sys.ecol$中.
SCOTT@test01p> SELECT * FROM sys.ecol$ WHERE tabobj# IN (SELECT DATA_OBJECT_ID FROM dba_objects WHERE owner = USER AND object_name = 'T');
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- ------------------------------ ----------
27978 3 C10B
--//c10b对应number类型是数字10.
--//对于这样的情况如果要降低HWM,仅仅ctas建立表以及索引.
--//如果增加字段时写入数据块中,应该不会出现这样的情况.看了一下隐含参数,应该是_add_col_optim_enabled.
SYS@test> @ hide _add_col_optim_enabled
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------- ---------------------------------- ------------- ------------- ------------ ----- ---------
_add_col_optim_enabled Allows new add column optimization TRUE TRUE TRUE TRUE IMMEDIATE
SCOTT@test01p> alter session set "_add_col_optim_enabled"=false;
Session altered.
create table t1(x int, pad varchar2(100)) enable row movement;
insert /*+ append*/ into t1 select level, lpad('x', 100, 'x') from dual connect by level<=1e4;
alter table t1 add y int default 10 not null;
create index i_t1_xy on t1(x,y);
delete t1 where x<=5000;
commit ;
alter table t1 shrink space;
SCOTT@test01p> alter table t1 shrink space;
Table altered.
--//当然这样增加字段就很慢!!
总结:
如果要做shrink space,最好先检查看看是否曾经这样增加过新字段.
|