如何给Oracle已有数据的表增加字段

时间:2021-08-20 10:53:57

背景:
有个100万数据的TABLE:
SQL> desc t2
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(18)
CREATED                                   NOT NULL DATE
LAST_DDL_TIME                             NOT NULL DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
需要添加一个字段(KONG INT DEFAULT 100)
在如何修改,需要注意什么细节。

模拟测试:
如果有事务在TABLE上,那ALTER 语句执行失败。因为DML语句会加共享锁在table上,而ALTER需要排他锁。故alter失败。
SQL> alter table t2 add (kong int );
alter table t2 add (kong int )
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


Elapsed: 00:00:00.00
那只能rollback transaction 或者kill session;
直接去添加一个字段,注意没有加上缺省值。
SQL> /

Table altered.

Elapsed: 00:00:00.04
执行的速度很快。在掩耳不及迅雷情况执行完毕。
这时可以DUMP BLOCK,看到数据块中的记录没有新添加的字段。(只是执行DDL,修改table的定义)
那这时删除一个字段呢?
SQL> alter table t2 drop column kong;

Table altered.

Elapsed: 00:00:11.42
可以想象,这是做了一个全表扫描。找到kong 字段,把内容搽去。
如果在ALTER中加上了缺省值,那又会怎样?
来来来,上楼上。一起去看看(易中天的语录)

SQL> alter table t2 add (kong int default 10);

Table altered.

Elapsed: 00:01:34.34
可以看到执行时间是1分钟34秒。而同时其他session执行的sql只能被等待。

SQL> select * from v$lock;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
07000000FDDF5C60 07000000FDDF5DD8          9 TX     458782         60          6          0         25          0
07000000FDC5AB28 07000000FDC5AB50          9 TM       6291          0          6          0         25          0
07000000FB9B8308 07000000FB9B8328         13 CU   32906288  117440513          6          0         21          0

(忽略无关的lock。SID=13, 执行了DDL; SID=9,执行DML)
这里有个问题,SID=9, 拥有 table的锁;sid=13也拥有table的锁;那为什么sid=13 要执行那么长的时间,而sid=9 也要
LONG TIME,但是没有看到相关的阻塞。(这个没有阻塞,没有猜到理由)
在DDL时,ORACLE会当作一个原子来执行,sid=13已经修改了数据表的定义,接着更改block的数据;而SID=9只能等待,等待sid=13执行完毕。在SID=9的session可以看到SID=13执行后的结果:
SQL> select * from t2
  2  where owner='KONG2' AND ROWNUM < 2;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED         LAST_DDL_TIME   TIMESTAMP           STATUS  T G S       KONG
--------------- --------------- ------------------- ------- - - - ----------
KONG2                          T1
                                     6290           6290 TABLE
05-NOV-08       05-NOV-08       2008-11-05:22:15:13 VALID   N N N         10

如果在修改同时,加上缺省值,会阻塞其他session;那如果分来呢?先加字段,再加上缺省值。
SQL> alter table t2 add (kong int );

Table altered.

Elapsed: 00:00:00.03
SQL>
SQL> alter table t2 modify (kong default 2000);

Table altered.

Elapsed: 00:00:00.03
看到速度是很快。。。可是和前一个方法有什么区别吗?难道快就没有缺点?

还是有的:
1) 在ALTER sql中有带缺省值,ORACLE会直接刷新全部的记录。
2) 在ALTER sql中没有带缺省值,ORACLE只会影响到后来的记录。(明白快是有隐秘的秘诀)

在选择哪种方法时,要更加具体的情况来实施:是否有INDEX,是否在应用的高并发阶段,等等。