首页 » ORACLE, ORACLE [C]系列 » oracle add column xx default value 增强(二)

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 —

打赏

目前这篇文章还没有评论(Rss)

我要评论