Oracle大表添加字段
在添加字段时,如果表数据量特别大。给添加的字段设置默认值,会消耗特别长的时间,而且所有行都要被锁住。
在生产系统上,如果没有安排足够长的变更时间,可能会对业务带来严重的影响。
对于这种情况,可以使用下面的语句:
alter table table_name add new_column data_type(precision) default \'value_string\' not null;
也就是default + not null 这两个关键词组合的方式。
使用这种方法添加的字段,并不会真的修改每行的值,而是在sys.ecol$ 里添加一行数据,记录下该字段的默认值。
虽然没有进行物理存储,但是对查询、数据泵等操作都没有影响 。
可以看下面示例:
SQL> col tabobj# for 999999999 SQL> col column for a20 SQL> col binarydefval for 99999999999999999999 SQL> select * from sys.ecol$; 未选定行 SQL> alter table test_object add create_user varchar2(30) default \'halberd\' not null; 表已更改。 SQL> col tabobj# for 999999999 SQL> col column for a20 SQL> col defval for a15 SQL> select tabobj#,COLNUM, utl_raw.cast_to_varchar2(binarydefval) as defval from sys.ecol$; TABOBJ# COLNUM DEFVAL ---------- ---------- --------------- 247703 27 halberd SQL> select object_id,object_name from dba_objects where object_name=\'TEST_OBJECT\'; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 247703 TEST_OBJECT SQL> select column_id from dba_tab_cols where owner=\'SYSTEM\' and table_name=\'TEST_OBJECT\' AND COLUMN_NAME=\'CREATE_USER\'; COLUMN_ID ---------- 27
这里我们验证了,该方式添加字段后,在sys.ecol$ 里插入了一行数据 。
下面验证,数据并没有存储到物理行中。
SQL> select max(column_id) from dba_tab_cols where table_name=\'TEST_OBJECT\' AND OWNER=\'SYSTEM\'; MAX(COLUMN_ID) -------------- 27 SQL> update test_object set create_user=\'halberd\' where rownum=1; 已更新 1 行。 SQL> commit; 提交完成。 SQL> select rowid,dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) rfno, 2 3 dbms_rowid.rowid_block_number(rowid) rdbno, 4 dbms_rowid.rowid_row_number(rowid) rno from test_object WHERE ROWNUM<=2; ROWID OBJECT_ID RFNO RDBNO RNO ------------------ ---------- ---------- ---------- ---------- AAA8eXAABAADz9BAAA 247703 1 999233 0 AAA8eXAABAADz9BAAB 247703 1 999233 1 SQL> select file_id,relative_fno from cdb_data_files where relative_fno=1; FILE_ID RELATIVE_FNO ---------- ------------ 199 1 SQL> alter system dump datafile 199 block 999233; system altered.
从上面的查询结果来看,表test_object 有27个字段。我添加的新字段,就是第27个字段。如果是dump数据块出来,显示的就是row 26, 因为dump 结果是从row 0 开始的。
下面是dump的数据块中前两行的的内容:
block_row_dump: tab 0, row 0, @0x3f5 tl: 114 fb: --H-FL-- lb: 0x3 cc: 27 col 0: [ 3] 53 59 53 col 1: [14] 49 5f 46 49 4c 45 23 5f 42 4c 4f 43 4b 23 col 2: *NULL* col 3: [ 2] c1 0a col 4: [ 2] c1 0a col 5: [ 5] 49 4e 44 45 58 col 6: [ 7] 78 77 04 11 01 39 0f col 7: [ 7] 78 77 04 11 01 39 0f col 8: [19] 32 30 31 39 2d 30 34 2d 31 37 3a 30 30 3a 35 36 3a 31 34 col 9: [ 5] 56 41 4c 49 44 col 10: [ 1] 4e col 11: [ 1] 4e col 12: [ 1] 4e col 13: [ 2] c1 05 col 14: *NULL* col 15: [ 4] 4e 4f 4e 45 col 16: *NULL* col 17: [ 1] 59 col 18: [ 1] 4e col 19: *NULL* col 20: [ 1] 4e col 21: [ 1] 4e col 22: *NULL* col 23: *NULL* col 24: *NULL* col 25: *NULL* col 26: [ 7] 68 61 6c 62 65 72 64 tab 0, row 1, @0x1ec4 tl: 94 fb: --H-FL-- lb: 0x0 cc: 22 col 0: [ 3] 53 59 53 col 1: [ 6] 49 5f 4f 42 4a 33 col 2: *NULL* col 3: [ 2] c1 27 col 4: [ 2] c1 27 col 5: [ 5] 49 4e 44 45 58 col 6: [ 7] 78 77 04 11 01 39 0f col 7: [ 7] 78 77 04 11 01 39 0f col 8: [19] 32 30 31 39 2d 30 34 2d 31 37 3a 30 30 3a 35 36 3a 31 34 col 9: [ 5] 56 41 4c 49 44 col 10: [ 1] 4e col 11: [ 1] 4e col 12: [ 1] 4e col 13: [ 2] c1 05 col 14: *NULL* col 15: [ 4] 4e 4f 4e 45 col 16: *NULL* col 17: [ 1] 59 col 18: [ 1] 4e col 19: *NULL* col 20: [ 1] 4e col 21: [ 1] 4e tab 0, row 2, @0x1e67
第一行, tab 0, row 0, @0x3f5 , 最后一个字段是第27个字段(dump 出来的字段数行数据都是从0 开始的)。
SQL> select utl_raw.cast_to_varchar2(replace(\'68 61 6c 62 65 72 64\',\' \')) as val from dual; VAL -------------------------------------------------------------------------------- halberd
而第二行, tab 0, row 1, @0x1ec4 ,则仅仅dump 出22个字段。说明22个字段之后的内容根本没有存储在数据块中,所以dump的结果没有这些字段的内容。
Created: 2020-09-16 Wed 10:38