语法
ALTER TABLE 'schema'.'tabname' ADD 'colname' [ 'coltype(length)' [DEFAULT] [CONSTRAINT] | INVISIBLE ];
语法示例
SQL> create table scott.emp(empname varchar2(32));
SQL> desc emp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNAME VARCHAR2(32)
ALTER TABLE scott.emp ADD empno number(12);
ALTER TABLE scott.emp ADD (empno number(12),gender char(2)...);
ALTER TABLE scott.emp ADD empno number(12) DEFAULT 0;
ALTER TABLE scott.emp ADD empno number(12) NOT NULL;
ALTER TABLE scott.emp ADD empno number(12) DEFAULT 0 NULL;
ALTER TABLE scott.emp ADD empno INVISIBLE;
概述
加字段的需求比较简单，但除了字段类型长度是否合理外，还要明确以下几方面：
- 新增字段后，表中已存在数据的新增字段值要如何处理？
- 新增字段带默认值时，字段值会被默认值填充，如何填充？
- 新增字段带约束时，慎重判断约束是否能加上；
1、填充原始数据新增字段值
需要填充
例如，给scott.emp表新增F_ID字段，并且要通过序列SEQ$EMP给F_ID赋值，有以下两种方式可以给原始数据的新增字段赋值：
# ADD 语句中直接设置DEFAULT
SQL> ALTER TABLE scott.emp ADD (F_ID NUMBER(8) DEFAULT SEQ$EMP.NEXTVAL);
# 使用PL/SQL程序进行批量更新
SQL> @update_fid
# SQL脚本 update_fid.sql
DECLARE
CURSOR C_ROWID_TABLE IS SELECT ROWID FROM SCOTT.EMP;
V_ROWID ROWID;
V_COUNT NUMBER := 0;
BEGIN
OPEN C_ROWID_TABLE;
LOOP
FETCH C_ROWID_TABLE INTO V_ROWID;
EXIT WHEN C_ROWID_TABLE%NOTFOUND;
UPDATE SCOTT.EMP SET F_PK_ID = SEQ$EMP.NEXTVAL WHERE ROWID = V_ROWID;
V_COUNT := V_COUNT+1;
IF V_COUNT = 100000 THEN
COMMIT;
V_COUNT := 0;
END IF;
END LOOP;
CLOSE C_ROWID_TABLE;
COMMIT;
END;
/
无需填充
原始数据的该字段值不需要处理的话，那么就会存在NULL值，需要关注新增字段的约束，是否要创建相关索引等，根据NULL的数量情况，具体进行调整；
2、默认值
新增带默认值的字段，表中已有数据的该字段值，会被默认值填充，其中还有一个11g新增的快速添加非空默认值的特性；
快速添加非空默认值的特性(11g)
# 官方文档原文：
If a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause. When you specify a default value, the database immediately updates each row with the default value. Note that this can take some time, and that during the update, there is an exclusive DML lock on the table. For some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML.
You can add a column with a NOT NULL constraint only if the table does not contain any rows, or you specify a default value.
# 译文：
如果向表中添加了新列，则除非指定DEFAULT子句，否则该列最初为NULL。指定默认值后，数据库会立即使用默认值更新每一行。请注意，这可能需要一些时间，并且在更新期间，表上有一个独占DML锁。对于某些类型的表（例如，没有LOB列的表），如果同时指定NOT NULL约束和默认值，则数据库可以优化列添加操作，并大大减少表因DML而锁定的时间。
只有当表中不包含任何行或指定了默认值时，才能添加具有NOT NULL约束的列。
11g版本，在新增字段时同时指定DEFAULT和NOT NULL约束，并不会更改(UPDATE)表中实际列的新增字段值，而是将默认值记录在数据字典ecol$中;
# 11g
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test(id number(2));
Table created.
SQL> select * from SYS.ECOL$;
no rows selected
# 新增一个带默认值且为NULL的列，ecol$数据字典中无对应记录;
SQL> alter table test add str1 char(1) default '0';
Table altered.
SQL> select * from SYS.ECOL$;
no rows selected
# 新增一个带默认值且为NOT NULL的列，ecol$数据字典中出现对应记录;
SQL> alter table test add str2 char(1) default '0' not null;
Table altered.
SQL> select * from SYS.ECOL$;
TABOBJ# COLNUM BINARYDEFVAL
---------- ---------- -------------
91932 3 30
SQL> select object_id,object_name,owner from dba_objects where object_id='91932';
OBJECT_ID OBJECT_NAME OWNER
---------- ------------ ---------
91932 TEST SYS
优化快速添加默认值特性(12c及以上)
# 官方文档原文：
Optimized ALTER TABLE...ADD COLUMN with default value for nullable columns
A nullable column is a column created without using the NOT NULL constraint. For certain types of tables, when adding a nullable column that has a default value, the database can optimize the resource usage and storage requirements for the operation. It does so by storing the default value for the new column as table metadata, avoiding the need to store the value in all existing records.
# 译文：
优化了为可为null的列添加具有默认值的ALTER TABLE...ADD COLUMN
可为NULL的列是在不使用NOT NULL约束的情况下创建的列。对于某些类型的表，当添加具有默认值的可为Null的列时，数据库可以优化操作的资源使用情况和存储要求。它通过将新列的默认值存储为表元数据来实现这一点，从而避免了将该值存储在所有现有记录中的需要。
12c版本及以后，新增带默认值字段时无论是否指定NOT NULL约束，都不会进行实际的UPDATE，会直接将默认值记录在数据字典中，后续的DML操作才会实际修改数据；
# 19c
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> create table test(id number(2));
Table created.
SQL> select * from SYS.ECOL$;
no rows selected
# 新增一个带默认值且为NULL的列，ecol$数据字典中出现对应记录;
SQL> alter table test add str1 char(1) default '0';
Table altered.
SQL> select * from SYS.ECOL$;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- ------------ --------
81066 3 30 0
# 新增一个带默认值且为NOT NULL的列，ecol$数据字典中出现对应记录;
SQL> alter table test add str2 char(1) default '0' not null;
Table altered.
SQL> select * from SYS.ECOL$;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- ------------ --------
81066 3 30 0
81066 4 30
SQL> select object_id,object_name,owner from dba_objects where object_id='81066';
OBJECT_ID OBJECT_NAME OWNER
---------- ----------- --------
81066 TEST SYS
用隐藏列辅助显示字段默认值
12c版本及以后，新增带默认值的字段未指定NOT NULL时，由于不会对新增字段进行实际的UPDATE，为了正确显示默认值，表中会出现隐藏列来辅助显示字段的默认值；
SQL> drop table test;
Table dropped.
SQL> create table test(id number(2));
Table created.
SQL> select COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN,VIRTUAL_COLUMN from dba_tab_cols where table_name='TEST' and owner='SYS';
COLUMN_NAME DATA_TYPE HIDDEN_COLUMN VIRTUAL_COLUMN
-------------------- ---------- ------------- --------------
ID NUMBER NO NO
# 新增一个带默认值且为NOT NULL的列，并未出现隐藏列;
SQL> alter table test add str1 char(1) default '0' not null;
Table altered.
SQL> select COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN,VIRTUAL_COLUMN from dba_tab_cols where table_name='TEST' and owner='SYS';
COLUMN_NAME DATA_TYPE HIDDEN_COLUMN VIRTUAL_COLUMN
-------------------- ---------- ------------- --------------
ID NUMBER NO NO
STR1 CHAR NO NO
# 新增一个带默认值且为NULL的列，增加了隐藏列;
SQL> alter table test add str2 char(1) default '0';
Table altered.
SQL> select COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN,VIRTUAL_COLUMN from dba_tab_cols where table_name='TEST' and owner='SYS';
COLUMN_NAME DATA_TYPE HIDDEN_COLUMN VIRTUAL_COLUMN
-------------------- ---------- ------------- --------------
ID NUMBER NO NO
STR1 CHAR NO NO
SYS_NC00003$ RAW YES NO
STR2 CHAR NO NO
# 继续新增一个带默认值且为NULL的列，隐藏列并未继续增加
SQL> alter table test add num1 number(2) default 00;
Table altered.
SQL> select COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN,VIRTUAL_COLUMN from dba_tab_cols where table_name='TEST' and owner='SYS';
COLUMN_NAME DATA_TYPE HIDDEN_COLUMN VIRTUAL_COLUMN
-------------------- ---------- ------------- --------------
ID NUMBER NO NO
STR1 CHAR NO NO
SYS_NC00003$ RAW YES NO
STR2 CHAR NO NO
NUM1 NUMBER NO NO
# 根据Column Projection Information发现同一表中多个带默认值且为NULL的列，共用一个隐藏列;
SQL> select * from test;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'projection'));
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| TEST | 3 | 114 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."ID"[NUMBER,22], NVL("TEST"."STR1",'0')[1],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("TEST"."STR2",'
0'),'0',NVL("TEST"."STR2",'0'),'1',"TEST"."STR2")[1],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",1)),NULL,NVL("TEST"."NUM1",0
),'0',NVL("TEST"."NUM1",0),'1',"TEST"."NUM1")[22],
NVL("TEST"."STR3",'0')[1], NVL("TEST"."STR4",'0')[1]
3、约束
新增的字段带主键或唯一约束(包含了NOT NULL条件)，在表中有数据时，不能直接添加，需要填充原始数据的该字段值;
- 要么带默认值;
- 要么先只加字段，给原始数据该字段赋值后，再加约束;
最后修改时间：2024-05-29 10:34:34
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
