Oracle 如何给大表添加字段，并带有默认值

原创心在梦在 2023-08-07

1405

Oracle 如何给大表添加字段，并带有默认值

一、需求

你是否遇到过开发人员添加字段，导致数据库锁表问题。但是令开发疑惑的事，他们添加字段，有的时候很快，有的时候很慢？为什么呢？询问得知，加的慢时候是带上了default默认值，如果表的数据量很大，那么会花费很长时间，在此期间，表上还会加6级锁，连select都会被阻塞。这在生产系统是不可接受的。所以，任何DDL操作，都应该询问DBA是否有风险。

从11g开始，这种情况有了明显的改善。Oracle 11g引入了元数据唯一默认值的概念。如果增加的列符合default+not null这两个条件的话，它只会去更新数据字典，不会去更新物理行，所以操作可以很短的时间完成，且不会对生产产生影响。读取列数据的时候优化器重写新列的查询，以确保结果与默认定义一致。注意，这两个条件缺一不可，否则还是会很慢。

从Oracle 12c更进一步，允许元数据默认值not null 可选，因此，在现有表中添加带有默认子句的新列将被作为一个元数据来处理，而不管该列是否被定义not null，两种方式操作都很快。

二、实验

1. 模拟一张大表

SQL> drop table test1;

Table dropped.

SQL> create table test1 as select * from dba_objects;

Table created.

SQL> insert into test1 select * from test1;

79699 rows created
.....
SQL> insert into test1 select * from test1;

1275200 rows created.

SQL> commit;

Commit complete.

--11g 环境下表初始大小：
SQL> select bytes from user_segments where segment_name='TEST1';

  BYTES
----------
 301989888
 
 --19c 环境下表初始大小：
 SQL> select bytes from user_segments where segment_name='TEST1';

 BYTES
----------
 739246080
复制

2. 11g 环境下测试

2.1 增加字段

情况1：只有default值

-- 不加not null约束
SQL> alter table test1 add a varchar2(100) default 'aa';

Table altered.

Elapsed: 00:00:42.62

SQL> select count(*) from test1 where a='aa';

  COUNT(*)
----------
   2550400
Elapsed: 00:00:00.24

SQL> select bytes from user_segments where segment_name='TEST1';

  BYTES
----------
 578813952
 
Elapsed: 00:00:00.01
复制

情况2：default+not null

--加上not null 约束   
SQL> alter table test1 add b varchar2(100) default 'bb' not null;

Table altered.

Elapsed: 00:00:00.04

SQL> select count(*) from test1 where b='bb';

  COUNT(*)
----------
   2550400

Elapsed: 00:00:00.23

SQL> select bytes from user_segments where segment_name='TEST1';

  BYTES
----------
 578813952
 
Elapsed: 00:00:00.01
复制

可以看到：

1.default+not null 的执行时间远小于只有default值的情况。

2.default+not null 表的大小也没有变化，说明表并没有实际去更新物理行，只是更新了数据字典。

这是11g对新增一个not null字段带有默认值进行了优化，默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。

2.2 查询test1表中添加的默认值

SQL> select object_id from dba_objects where object_name = 'TEST1' and owner='SXC';

 OBJECT_ID
----------
  97662
     
     
SQL> set line222
SQL> select colnum,binarydefval from ecol$ where tabobj#= 97662;

    COLNUM BINARYDEFVAL
---------- ----------------------------------
        17 6262
SQL> desc ecol$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABOBJ#                                           NUMBER
 COLNUM                                            NUMBER
 BINARYDEFVAL                                        BLOB
 
-- 注意：binarydefval 是blob 类型，所有实际存储的值并不是数值6262，可以通过下面的存储过程查看真实存储的值是bb 
SQL> SET SERVEROUT ON
SQL> 
SQL> DECLARE
  2    V_BLOB VARCHAR2(32767) DEFAULT '6262';
  3    BEGIN
  4    FOR I IN 1..LENGTH(V_BLOB)/2 LOOP
  5    DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I-1) * 2 + 1, 2), 'XXX')));
  6    END LOOP;
  7    DBMS_OUTPUT.NEW_LINE;
  8    END;
  9    /
bb

PL/SQL procedure successfully completed.     
复制

2.3 对比执行计划

如果我们从表中检索对应的列时候，我们对比一下执行计划：

情况1：只有default值

SQL> select count(*) from test1 where a='aa';

  COUNT(*)
----------
   2550368

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  b91qrsffc9t3d, child number 0
-------------------------------------
select count(*) from test1 where a='aa'

Plan hash value: 3896847026

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time      |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |  9972 (100)|    |
|   1 |  SORT AGGREGATE    |       |     1 |    52 |           |   |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TEST1 |  2886K|   143M|  9972   (1)| 00:02:00 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"='aa')

Note
-----
   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

23 rows selected.
复制

结论：采用filter(“A”=‘aa’) 过滤的方式，直接读取列数据。

情况2：default+not null

SQL> select count(*) from test1 where b='bb';

  COUNT(*)
----------
   2550368

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9xurwt6ytwjf0, child number 0
-------------------------------------
select count(*) from test1 where b='bb'

Plan hash value: 3896847026

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |  9974 (100)|   |
|   1 |  SORT AGGREGATE    |       |     1 |    52 |          |    |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TEST1 |  2886K|   143M|  9974   (1)| 00:02:00 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NVL("B",'bb')='bb')

Note
-----
   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

23 rows selected.
复制

结论：采用filter(NVL(“B”,‘bb’)=‘bb’)方式过滤，读取列数据，但是oracle认为default+not null 的方式添加的列是空列，所以用nvl函数做了一次转换。

思考？

如果我们在列上建索引呢？

SQL> create index idx1 on test1 (b);

Index created.

SQL> select count(*) from test1 where b='bb';

  COUNT(*)
----------
   2550368

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9xurwt6ytwjf0, child number 0
-------------------------------------
select count(*) from test1 where b='bb'

Plan hash value: 325870156

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |  1391 (100)|          |
|   1 |  SORT AGGREGATE       |      |     1 |    52 |            |          |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX FAST FULL SCAN| IDX1 |  2886K|   143M|  1391   (1)| 00:00:17 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NVL("B",'bb')='bb')

Note
-----
   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------

23 rows selected.
复制

结论：虽然从全表扫描变成索引扫描，但是一样是NVL(“B”,‘bb’)='bb’方式过滤数据。

后续新增加的数据呢？

-- 增加2行数据，一个用默认值，一个不用默认值。
SQL> select * from test1 where owner='T';

no rows selected

SQL> insert into test1 (owner,b) values ('T',default);

1 row created.

SQL> insert into test1 (owner,b) values ('T','bbb');

1 row created.

SQL> commit;

Commit complete.

--访问默认值，查看执行计划
SQL> select owner,b from test1 where  owner='T' and b='bb';

OWNER    B
----------------------------------------
T      bb

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dyd1zgs10jr16, child number 0
-------------------------------------
select owner,b from test1 where  owner='T' and b='bb'

Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |  9974 (100)|    |
|*  1 |  TABLE ACCESS FULL| TEST1 |   287 | 19803 |  9974   (1)| 00:02:00  |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("OWNER"='T' AND NVL("B",'bb')='bb'))

Note
-----
   - dynamic sampling used for this statement (level=2)

22 rows selected.


--访问非默认值，查看执行计划
SQL> select owner,b from test1 where  owner='T' and b='bbb';

OWNER    B
----------------------------------------
T      bbb

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5mbna21dqafdw, child number 0
-------------------------------------
 select owner,b from test1 where  owner='T' and b='bbb'

Plan hash value: 2734693028

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     9 (100)  |   |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST1 |   287 | 19803 |     9   (0)| 00:00:01      |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IDX1  |   403 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='T')
   2 - access(NVL("B",'bb')='bbb')

Note
-----

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

24 rows selected.
复制

结论：新增的数据，执行计划中一样都是采用NVL(“B”,‘bb’)='bb’方式。

2.4 表压缩后限制

1. 添加列报错

表压缩之后，default+not null是可以操作的；但是只有default是不行的。

SQL> alter table test1 compress;

Table altered.

SQL> alter table test1 add c number default 3 not null;

Table altered.

SQL> alter table test1 add d number default 4;
alter table test1 add d number default 4
                      *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
复制

解决办法：但是这种方法，只有之后新插入的行才是有值的，之前已经存在的行是没有的，还是空，所以需要找时间批量提交修改。

SQL> alter table test1 add d number;

Table altered.

SQL> alter table test1 modify d number default 4;

Table altered.

SQL> select count(*) from test1 where d=4;

  COUNT(*)
----------
      0
      
-- 可以批量更改
SQL> update test1 set d=4 where d is null;

2550368 rows updated.

SQL> commit;

Commit complete.
复制

2. 删除列报错

-- 直接drop column报错
SQL> alter table test1 drop column c;
alter table test1 drop column c
                              *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


-- 尝试先把列设成unused方式，再删除一样报错
SQL> alter table test1 set unused column c;

Table altered.

SQL> alter table test1 drop unused columns;
alter table test1 drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
复制

结论：根据Doc ID 1574318.1 描述，暂时没有好的解决方法，只能针对oltp compress 方式可以删除列。

SQL> alter table t2 compress for oltp;  --加上for oltp

Table altered.

SQL> alter table t2 drop column id;

Table altered.

SQL> alter table t2 compress;

Table altered.

SQL> alter table t2 drop column T1_ID;
alter table t2 drop column T1_ID
                           *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
复制

图片.png

3. 19c 环境下测试

3.1 增加字段

情况1：只有default值

-- 不加not null约束
SQL> alter table test1 add a varchar2(100) default 'aa';

Table altered.

Elapsed: 00:00:00.02

SQL> select count(*) from test1 where a='aa';

  COUNT(*)
----------
   4643072

Elapsed: 00:00:01.05

SQL> select bytes from user_segments where segment_name='TEST1';

  BYTES
----------
 739246080
 
Elapsed: 00:00:00.01
复制

情况2：default+not null

--加上not null 约束   
SQL> alter table test1 add b varchar2(100) default 'bb' not null;

Table altered.

Elapsed: 00:00:00.06

SQL> select count(*) from test1 where b='bb';

  COUNT(*)
----------
   4643072

Elapsed: 00:00:00.23

SQL> select bytes from user_segments where segment_name='TEST1';

  BYTES
----------
 739246080
 
Elapsed: 00:00:00.01
复制

可以看到：

1.和11g不一样，default+not null 和只加default 速度一样快。

2.两种方式表的大小都没有变化，说明表都没有实际去更新物理行，都只是更新了数据字典。

3.2 查询test1表中添加的默认值

SQL> select object_id from dba_objects where object_name = 'TEST1' and owner='SXC';

 OBJECT_ID
----------
  73797
     
     
SQL> set line222
SQL> select colnum,binarydefval from ecol$ where tabobj#= 73797;

    COLNUM BINARYDEFVAL
---------- -----------------------
        28 6161
        29 6262

-- 比11g 多出一行，不管是否有加上not null 条件，都被存储到了ecol$表中。

SQL> SET SERVEROUT ON
SQL> DECLARE
  2    V_BLOB VARCHAR2(32767) DEFAULT '6161';
  3    BEGIN
  4    FOR I IN 1..LENGTH(V_BLOB)/2 LOOP
  5    DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I-1) * 2 + 1, 2), 'XXX')));
  6    END LOOP;
  7    DBMS_OUTPUT.NEW_LINE;
  8    END;
  9    /
aa

SQL> DECLARE
  2    V_BLOB VARCHAR2(32767) DEFAULT '6262';
  3    BEGIN
  4    FOR I IN 1..LENGTH(V_BLOB)/2 LOOP
  5    DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I-1) * 2 + 1, 2), 'XXX')));
  6    END LOOP;
  7    DBMS_OUTPUT.NEW_LINE;
  8    END;
  9    /
bb

PL/SQL procedure successfully completed.     
复制

3.3 对比执行计划

如果我们从表中检索对应的列时候，我们对比一下执行计划：

情况1：只有default值

SQL> select count(*) from test1 where a='aa';

  COUNT(*)
----------
   4643072

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  12bwjcp0zanhm, child number 0
-------------------------------------
 select count(*) from test1 where a='aa'

Plan hash value: 3896847026

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |   394 (100)|   |
|   1 |  SORT AGGREGATE    |       |     1 |     3 |            |  |
|*  2 |   TABLE ACCESS FULL| TEST1 | 72548 |   212K|   394   (1)| 00:00:01  |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00027$",0)),NULL,NVL("
              A",'aa'),'0',NVL("A",'aa'),'1',"A")='aa')


20 rows selected.

Elapsed: 00:00:14.13
复制

结论：和11g 不一样，这里即采用了nvl函数转化，也多了一个隐藏列SYS_NC00027$，通过下面的SQL可以查询到。这个隐藏列是在添加default的列的时候增加的，添加default+not null 的列没有。

SQL> select column_name,column_id,hidden_column,virtual_column from dba_tab_cols where table_name='TEST1' and owner='SXC';

COLUMN_NAME                     COLUMN_ID HIDDEN VIRTUA
---------------------------------------- ------ ------------------
OWNER                          1      NO      NO
...
MODIFIED_VSNID                    26     NO      NO
SYS_NC00027$                            YES     NO    -- 隐藏列
A                             27      NO     NO
B                             28      NO     NO
复制

情况2：default+not null

SQL> select count(*) from test1 where b='bb';

  COUNT(*)
----------
   4643072

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9xurwt6ytwjf0, child number 0
-------------------------------------
select count(*) from test1 where b='bb'

Plan hash value: 3896847026

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |   394 (100)|   |
|   1 |  SORT AGGREGATE    |       |     1 |     3 |        |      |
|*  2 |   TABLE ACCESS FULL| TEST1 | 72548 |   212K|   394   (1)| 00:00:01  |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NVL("B",'bb')='bb')

19 rows selected.

Elapsed: 00:00:01.22
复制

结论：和11g 方式一样，都是采用filter(NVL(“B”,‘bb’)=‘bb’)过滤，读取列数据，并且使用了nvl函数。

思考？

如果我们在列上建索引呢？
后续新增加的数据呢？

结论：测试下来，大体结果和11g 一样。都会使用隐藏列SYS_NC00027$和NVL函数转化。

4.4 表压缩后限制

1. 添加列

SQL> alter table test1 compress;

Table altered.

SQL> alter table test1 add c number default 3 not null;

Table altered.

SQL> alter table test1 add d number default 4;

Table altered.
复制

结论：和11g不一样，19c表compress之后，两种方式都能添加列。

但是，在12c环境下，表compress之后，两种方式都不能添加列。

SQL> create table T1 ( a number, b varchar2(10) ) compress ;

Table created.

SQL> alter table T1 add c varchar2(20) default 'test' not null ;
alter table T1 add c varchar2(20) default 'test' not null
                   *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table T1 add c varchar2(20) default 'test' ;
alter table T1 add c varchar2(20) default 'test'
                   *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
复制

2. 删除列报错

-- 直接drop column报错
SQL> alter table test1 drop column c;
alter table test1 drop column c
                              *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


-- 尝试先把列设成unused方式，再删除一样报错
SQL> alter table test1 set unused column c;

Table altered.

SQL> alter table test1 drop unused columns;
alter table test1 drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
复制

结论：和11g一样，暂时没有好的解决方法，只能针对oltp compress 方式可以删除列。

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

拨开乌云见阳光

1年前

好文章，点赞

1年前

Oracle 如何给大表添加字段，并带有默认值

Oracle 如何给大表添加字段，并带有默认值

一、需求

二、实验

1. 模拟一张大表

2. 11g 环境下测试

2.1 增加字段

情况1：只有default值

情况2：default+not null

2.2 查询test1表中添加的默认值

2.3 对比执行计划

情况1：只有default值

情况2：default+not null

思考？

2.4 表压缩后限制

1. 添加列报错

2. 删除列报错

3. 19c 环境下测试

3.1 增加字段

情况1：只有default值

情况2：default+not null

3.2 查询test1表中添加的默认值

3.3 对比执行计划

情况1：只有default值

情况2：default+not null

思考？

4.4 表压缩后限制

1. 添加列

2. 删除列报错

评论

相关阅读