1、Oracle中值为null的column如何存储

dumporacle块后发现列总数不同

其中发现了*NULL* ，应该为空的存储为 *NULL*。

这样的话就应该所有的行列数都相同，等于表的列数才对。

验证过程：

表结构如下

 

插入不同的数据，其中第二行第三行分别对首列及二三列做了空置处理。

 

  insert into wjf_002 (id,fst_name)values('03','wang');

  insert into wjf_002 (lst_name,fst_name)values('feng','wang');

  insert into wjf_002 (address) values('shanghai');

  commit；

 

之后使用sys用户

altersystem checkpoint;    -----刷表块到磁盘

    alter system dump datafile 6 block 831915;

 

转储的结果如下

结论：

    oracle存储行时，只存储到最后不为空的列。排在最后的空列不存储（即不占空间，这个也可以从每行的tl长度验证）。

   若最后的非空行之前有空行，则空行没有内容，但因为行信息的存在，也需要占用1byte的空间（tl+1）。

另外补充行长度tl的计算方式

 

tl = 行信息+ 列头信息 + 存储数据(空行的列头信息也要占1byte，最后列为空的话不存储任何信息)

左边： 3+   1（列） + 5 = 9

右边： 3+   6（列） + 9 = 18

2、新增列（不添加默认值）

alter table nwd.wjf_ts_num2 add (id3 varchar2(20))

 

3、新增列（添加默认值）

  altertable nwd.wjf_ts_num2 add (id4 varchar2(20) default 0)

 

 

在新增字段并在字段中填写内容后，发现整行数据在块里的位置完全改变，在新的空白区域重新复制行的所有内容。

对应偏移量变化如下

4、删除列

 alter table nwd.wjf_ts_num2 drop column id4

 

 

结论：

   删除列的操作直接修改了块内数据。

 

4、update

 对于update操作，oracle也会不理会原来的数据存储，而是在块上方空白区域重新开辟新的区域填充。

 并把改行的偏移值更改。原数据的位置也会修改标记。

5、通过以上测试，可以总结以下结论：

1、对于表新增字段不指定默认值，则只修改数据字典，数据块不做任何变动。

2、对于表新增字段指定默认值，所有数据块都要被修改。

3、对于表删除字段，所有数据块都要被修改。

而且在对表字段进行增删的过程中，对表加表级排他锁，阻塞一切关于表的操作（包括select）。

6、大表增删字段注意事项

对于大表增删字段，因为可能要修改所有数据块，所以会耗费比较长的时间，这段时间会造成业务中断。

因此，对于大表增字段不加默认值，因为不会动数据块，只会修改数据字典，可以很快完成，对业务影响较小。

对于大表删字段，建议做标识删除(如可以更改字段名字，加一个del_前缀留转为备用字段)，或者alter table column unused。

A、首先设置字段不可用（此过程只修改数据字典，不修改数据，且操作不可回退）

alter table nwd.wjf_ts_num2 set unused column id6;

B、在业务空闲时进行字段删除操作，未避免undo段过大，设置1000条checkpoit一次。

      alter table nwd.wjf_ts_num2 drop unused columns checkpoint 1000;

 对于大表增字段指定默认值，因为会动所有数据块，造成业务中断，不能再业务时间操作。

   可以使用以下两种方式实现：

A、先增加字段不指定默认值

 alter table nwd.wjf_ts_num2 add (id4 varchar2(20))

B、在alter table modify 方式设置默认值（不会为老数据行填充默认值）

  alter table nwd.wjf_ts_num2 modify id4 default 0

C、之后写过程批量更新提交新增字段的数据更新操作。

 

 

以上增删字段也可以使用v$bh视图，通过操作后查看表块是否变脏即可确认增删字段操作是否修改了数据块。