一、讲故事
你是否遇到过开发人员添加字段，导致数据库锁表问题？
但是令开发疑惑的事，他们添加字段，有的时候很快，有的时候很慢？ 为什么呢？
询问得知，**加的慢时候是带上了default默认值，如果表的数据量很大，那么会花费很长时间。在加字段期间，表上还会加6级锁，连select都会被阻塞。**这在生产系统是不可接受的。
所以，任何DDL操作，都应该询问DBA是否有风险。
从11g开始，这种情况有了明显的改善。Oracle 11g引入了元数据唯一默认值的概念。如果增加的列符合default+not null这两个条件的话，它只会去更新数据字典，不会去更新物理行，所以操作可以很短的时间完成，且不会对生产产生影响。读取列数据的时候优化器重写新列的查询，以确保结果与默认定义一致。
注意：这两个条件（列符合default且not null）缺一不可，否则还是会很慢。
从Oracle 12c更进一步，允许元数据默认值not null 可选，因此，在现有表中添加带有默认子句的新列将被作为一个元数据来处理，而不管该列是否被定义not null，两种方式操作都很快。
二、讲实战
1. 模拟大表
SQL> drop table test1; #执行这个sql前，需要确认test1表是不是能删除。表名可以自己换成别的表。
Table dropped.
SQL> create table test as select * from dba_objects;
Table created.
SQL> insert into test1 select * from test;
79699 rows created
.....
SQL> insert into test1 select * from test;
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函数做了一次转换。
※思考？
1、如果我们在列上建索引呢？
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、后续新增加的数据呢？
-- 增加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
官方文档：
3. 在Oracle19C 环境下测试
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函数。
※思考？
1、如果我们在列上建索引呢？
2、后续新增加的数据呢？
结论：测试下来，大体结果和11g 一样。 都会使用隐藏列SYS_NC00027$和NVL函数转化。
3.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之后， 两种方式都不能添加列 。文章来源：https://www.toymoban.com/news/detail-678826.html
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 方式可以删除列。文章来源地址https://www.toymoban.com/news/detail-678826.html
到了这里，关于Oracle 如何给大表添加带有默认值的字段的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
