oracle add column xx default value 增强(二)
oracle11g add default values columns(增加默认值列的改进)11年前 学习oracle初期测试过oracle 11g相对oracle 10g的增强， 对于增加列default not null 时只增加数据字典定义，而不有update 表现有数据，给对于大表比如上亿记录的列增加带来不小的提升， 今天看到同事在使用ogg 从19c to 11g同步DDL 又看到了这个现象。
source database 19c 上亿记录的表使用alter table xxx add xx default xxx （without “not null”）， 增加了一列操作很快(秒级), 但是在target db 11g OGG replicat时延时10几个小时，而且还有library cache lock 等待， 发现正在同步DDL ，执行增加列操作, 而且11g 是在更新全表 update xxx set newcolumn= default value.
add column default 增强
| 增加列默认值 | 10g | 11g | 12c+ |
| add column default | 回写表数据 | 回写表数据 | 只增加数据字段定义 不更新表数据 |
| add column default not null | 回写表数据 | 只增加数据字段定义 不更新表数据 |
只增加数据字段定义 不更新表数据 |
在19c 增加列，我们dump block 观察一下。
SQL> drop table anbob.t1;
Table dropped.
SQL> create table anbob.t1 as select * from dba_objects;
Table created.
SQL> insert into anbob.t1 select * from anbob.t1;
76390 rows created.
-- repeat xx times
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> @seg anbob.t1
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
96 ANBOB T1 TABLE USERS 12288 12 27458
Elapsed: 00:00:01.15
SQL> select count(*) from anbob.t1;
COUNT(*)
----------
611120
SQL> alter table anbob.t1 add c1 varchar2(10) default 'a';
Table altered.
Elapsed: 00:00:02.18
SQL> select c1,rowid rn,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk# from anbob.t1 where rownum<11; C1 RN FILE# BLK# ---------- ------------------ ---------- ---------- a AAATfeAAMAAAGtDAAA 12 27459 a AAATfeAAMAAAGtDAAB 12 27459 a AAATfeAAMAAAGtDAAC 12 27459 a AAATfeAAMAAAGtDAAD 12 27459 a AAATfeAAMAAAGtDAAE 12 27459 a AAATfeAAMAAAGtDAAF 12 27459 a AAATfeAAMAAAGtDAAG 12 27459 a AAATfeAAMAAAGtDAAH 12 27459 a AAATfeAAMAAAGtDAAI 12 27459 a AAATfeAAMAAAGtDAAJ 12 27459 10 rows selected. Elapsed: 00:00:00.25 SQL> alter system checkpoint;
System altered.
SQL> select c1,rowid rn,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk#,dbms_rowid.rowid_row_number(rowid) row#
2 from anbob.t1 where rownum<11; C1 RN FILE# BLK# ROW# ---------- ------------------ ---------- ---------- ---------- a AAATfeAAMAAAGtDAAA 12 27459 0 a AAATfeAAMAAAGtDAAB 12 27459 1 a AAATfeAAMAAAGtDAAC 12 27459 2 a AAATfeAAMAAAGtDAAD 12 27459 3 a AAATfeAAMAAAGtDAAE 12 27459 4 a AAATfeAAMAAAGtDAAF 12 27459 5 a AAATfeAAMAAAGtDAAG 12 27459 6 a AAATfeAAMAAAGtDAAH 12 27459 7 a AAATfeAAMAAAGtDAAI 12 27459 8 a AAATfeAAMAAAGtDAAJ 12 27459 9 10 rows selected. SQL> @desc anbob.t1
Name Null? Type
------------------------------- -------- ----------------------------
1 OWNER VARCHAR2(128)
2 OBJECT_NAME VARCHAR2(128)
3 SUBOBJECT_NAME VARCHAR2(128)
4 OBJECT_ID NUMBER
5 DATA_OBJECT_ID NUMBER
6 OBJECT_TYPE VARCHAR2(23)
7 CREATED DATE
8 LAST_DDL_TIME DATE
9 TIMESTAMP VARCHAR2(19)
10 STATUS VARCHAR2(7)
11 TEMPORARY VARCHAR2(1)
12 GENERATED VARCHAR2(1)
13 SECONDARY VARCHAR2(1)
14 NAMESPACE NUMBER
15 EDITION_NAME VARCHAR2(128)
16 SHARING VARCHAR2(18)
17 EDITIONABLE VARCHAR2(1)
18 ORACLE_MAINTAINED VARCHAR2(1)
19 APPLICATION VARCHAR2(1)
20 DEFAULT_COLLATION VARCHAR2(100)
21 DUPLICATED VARCHAR2(1)
22 SHARDED VARCHAR2(1)
23 CREATED_APPID NUMBER
24 CREATED_VSNID NUMBER
25 MODIFIED_APPID NUMBER
26 MODIFIED_VSNID NUMBER
27 C1 VARCHAR2(10)
SQL> select column_name,data_default from dba_tab_cols where table_name='T1' AND OWNER='ANBOB' AND COLUMN_NAME='C1';
COLUMN_NAME DATA_DEFAU
------------------------- ----------
C1 'a'
-- trace file
Block header dump: 0x03006b43
Object id on Block? Y
seg/obj: 0x137de csc: 0x00000000024ad328 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3006b40 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x00000000024ad328
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x03006b43
data_block_dump,data header at 0x7f2a5522507c
===============
tab 0, row 0, @0x1f1a
tl: 102 fb: --H-FL-- lb: 0x0 cc: 22
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
tab 0, row 1, @0x1ebc
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
...
SQL> @hex 137de
DEC HEX
----------------------------------- --------------------
79838.000000 137DE
SQL> @oid 79838
owner object_name object_type SUBOBJECT_NAME CREATED LAST_DDL_TIME status DA TA_OBJECT_ID
------------------------- ------------------------------ ------------------ ------------------------------ ------------------- ------------------- --------- -- ------------
ANBOB T1 TABLE 2022-09-20 15:24:36 2022-09-20 15:26:34 VALID 79838
SQL> select UTL_RAW.CAST_TO_VARCHAR2(replace('49 5f 46 49 4c 45 23 5f 42 4c 4f 43 4b 23',' ','')) from dual;
UTL_RAW.CAST_TO_VARCHAR2(REPLACE('495F46494C45235F424C4F434B23','',''))
--------------------------------------------------------------------------------
I_FILE#_BLOCK#
SQL> select object_name from anbob.t1 where rowid='AAATfeAAMAAAGtDAAA';
OBJECT_NAME
------------------------------
I_FILE#_BLOCK#
Note:
table block中并没有增加的default C1列值， 另外21列后面都无值，是因为oracle尾部几列是null值，连占位符都不需要。
可能相关的隐藏参数
-- for default not null
SQL> @pd col_optim
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ------------------------------------------------------ ---------------------------------------------
4097 1001 _add_col_optim_enabled TRUE Allows new add column optimization
-- for default without not null
SQL> @pd default_optim
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ------------------------------------------------------ ---------------------------------------------
4293 10C5 _add_nullable_column_with_default_optim TRUE Allows add of a nullable column with default optimizat ion
注意相关如多表关连有wrong result时需要确认是否有相关bug.
— over —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。