看到ITPUB上有人提到，如何快速的在一个3亿条记录的表中增加一个包含DEFAULT值的新字段。描述一下如何快速的添加表的默认值。
Oracle11新特性——在线操作功能增强（二） ：http://yangtingkun.itpub.net/post/468/401293
对于这个问题，常规的方法有三个：
一、直接ALTER TABLE ADD COLUMN，这种方法的好处是简单，一个命令就可以完成，缺点是耗时非常长，而且在漫长的执行过长中都会锁定表，导致应用无法修改数据，并且在完成后容易造成大量的行迁移，从而影响后续访问的性能；
二、CREATE TABLE AS SELECT的方式，这种方式相对第一种而言，效率更高，如果是非归档模式，那么完成速度会更快。但是缺点同样很明显，首先需要大量的停机时间，而且表上的索引、约束、触发器以及权限等都需要手工处理，比较麻烦；
三、在线重定义，这种方式应该算是这三种中最佳的，锁表时间非常短，对业务影响最小，但是缺点是整个操作的时间并不会变短，和第二种方式一样，都需要2倍的存储空间，而且操作相对复杂。
如果数据库的版本是11g，那么这就不是问题了，以前写过专门的文章，描述11g是如何快速添加一个包含非空DEFAULT值的列的。那么对于10g及以前版本的数据库而言，增加一个包含默认值的字段难道就真的要忍受漫长的执行时间和大量的REDO、UNDO的占用吗。
其实完全可以仿照11g的方式，来自己实现10g中字段的快速添加。记得Tom在描述数据库的解析时提到过，软解析要优于硬解析，软软解析要优于软解析，而速度最快的莫过于不解析。提高速度的最高境界就是根本不做。
那么对于添加默认值的方式也可以才有这种方法，对于已经存在的记录的默认值，我们根本不去添加，一个简单的例子如下：
SQL> CREATE TABLE T_ADD_COLUMN (ID NUMBER, NAME VARCHAR2(30)); TABLE created. SQL> INSERT INTO T_ADD_COLUMN 2 SELECT ROWNUM, OBJECT_NAME 3 FROM DBA_OBJECTS; 11955 ROWS created. SQL> COMMIT; Commit complete. SQL> ALTER TABLE T_ADD_COLUMN ADD (NEW_COL VARCHAR2(30)); TABLE altered. SQL> ALTER TABLE T_ADD_COLUMN MODIFY (NEW_COL DEFAULT 'OLD VALUE'); TABLE altered. SQL> SELECT * FROM T_ADD_COLUMN WHERE ID = 1; ID NAME NEW_COL ---------- ------------------------------ ------------------------------ 1 ICOL$ SQL> ALTER TABLE T_ADD_COLUMN RENAME TO T_ADD_COLUMN_BASE; TABLE altered. SQL> CREATE VIEW T_ADD_COLUMN 2 (ID, NAME, NEW_COL) 3 AS SELECT ID, NAME, NVL(NEW_COL, 'OLD VALUE') 4 FROM T_ADD_COLUMN_BASE; VIEW created. SQL> SELECT * FROM T_ADD_COLUMN WHERE ID = 1; ID NAME NEW_COL ---------- ------------------------------ ------------------------------ 1 ICOL$ OLD VALUE |
将添加列和增加默认值的DDL分开执行，这样Oracle不会在添加列的同时给现有的记录增加默认值，因此这两个SQL都是秒级完成。随后将表重命名，然后根据原始的名称创建一个视图，在读取表的时候，将新增列所有的空值都转化为默认值即可。整个过程只需要4个DDL，且都是秒级完成，这样就实现了给表增加默认值的需求。
当然表和视图还是有很大区别的，比如由于使用了函数作为查询列，使得这个视图不支持对NEW_COL列的读写。
不过这个问题可以通过INSTEAD OF触发器实现，下面是一个简单的INSERT的例子：
SQL> INSERT INTO T_ADD_COLUMN (ID, NAME) VALUES (12000, 'A'); 1 ROW created. SQL> SELECT * FROM T_ADD_COLUMN WHERE ID = 12000; ID NAME NEW_COL ---------- ------------------------------ ------------------------------ 12000 A OLD VALUE SQL> INSERT INTO T_ADD_COLUMN VALUES (12000, 'A', 'TEST'); INSERT INTO T_ADD_COLUMN VALUES (12000, 'A', 'TEST') * ERROR at line 1: ORA-01733: virtual COLUMN NOT allowed here SQL> CREATE OR REPLACE TRIGGER T_INS_I_TADDCOLUMN 2 INSTEAD OF INSERT ON T_ADD_COLUMN 3 BEGIN 4 INSERT INTO T_ADD_COLUMN_BASE 5 VALUES (:NEW.ID, :NEW.NAME, :NEW.NEW_COL); 6 END; 7 / TRIGGER created. SQL> INSERT INTO T_ADD_COLUMN VALUES (12001, 'B', 'TEST'); 1 ROW created. SQL> SELECT * FROM T_ADD_COLUMN WHERE ID = 12001; ID NAME NEW_COL ---------- ------------------------------ ------------------------------ 12001 B TEST |
视图的更新和修改可以采用类似的方法实现。