【新炬網路名師大講堂】Oracle中大表新增帶有預設值列的討論（包括11G,12C新特性）

SQL> select count(*) from t;

COUNT(*)
———-
1207317

對錶加了一列，並設定not null 加預設值

SQL> alter table t add MrDai number default 10000 not null;

Table altered.

Elapsed: 00:00:00.34

速度飛快

這裡其實只是在資料字典中做了一個標記，並不會對塊中行的實際資料進行修改，所以速度飛快

我們查詢的時候，會對新列做一個類似nvl()之類的操作，如果是null，則返回預設的數值。

在設定完成以後，如果是新插入的行，在預設值列，會將資料真正的插入到行

總結一下，這個特性類似於 給表設了一個斷點， 斷點之前的，如果該列是null，則對其轉換為 預設值，而斷點之後的，該列會插入真正的值

這裡存在一個問題，就是我上面標紅的not null。

畢竟一個新特性在口口相傳中可能會漏掉一些東西，比如最重要的not null 如果漏了

那麼將會產生與10G中相同的災難，超大DDL事務，hang死關於該表的一切操作（包括select）

這種誤操作可以說是非常容易發生的，看看下面這兩個語句有多像。

alter table t add MrDai1 number default 10000;
alter table t add MrDai1 number default 10000 not null;

OK。既然提出了可能會存在這種誤操作事故可能性，那麼我就一定要提出一個解決方案，否則這部落格豈不是玩笑了

12C。對這個功能再次做出了增強，這個增強最重要的就是將這個誤操作消除了。也就是說，大表新增預設值新列時，不用加not null 也可以實現metadata only 的增加方式。

實現如下：

百萬表

SQL> select count(*) from t;

COUNT(*)
———-
1454256

對錶直接增加一列（不設定not null）

SQL> alter table t add MrDai number default 10000;

Table altered.

Elapsed: 00:00:00.03

—————————————————————————————————————————–

 

上面主題討論完了，看起來不起眼的更新，其實在我們實際運用過程中影響卻十分巨大，所以有什麼新特性，一定要首先實驗實驗再實驗，將語句寫好，到業務庫上建議複製貼上。減少各種非常不 必要的誤操作。

—————————————————————————————————————————-

上面討論了12C中default列增強的一種，那麼將12C中關於default列的增強的其他部分也討論一下。

12C中default列方面增強了三塊。

1、default列metadata only增強（既我們上面討論過的特性）

2、default值直接使用sequence

3、顯式插入null值，依然將其變成default值特性（on null子句）

第一個特性我們已經討論過了，開始從第二個特性討論。

default值直接使用sequence

12C之前情況

建立sequence

SQL> create sequence t_seq;

Sequence created.

SQL>create table t1 (
id number default  t_seq.nextval,

*
name varchar2(20)
4 );

ERROR at line 2:
ORA-00984: column not allowed here

說明，在12C之前，如果想產生一個順序預設值作為列值，必須在insert裡面做文章，如下

SQL>create table t1 (id number,
name varchar2(20)
);

Table created.

SQL> insert into t1 values(t_seq.nextval,’haha’);

1 row created.

Elapsed: 00:00:00.00

SQL> insert into t1 values(t_seq.nextval,’haha’);

1 row created.

Elapsed: 00:00:00.00

SQL> select * from t1;

ID NAME
———- ——————–
1 haha
2 haha

這裡是11G的情況，到了12C我們可以在表建立的過程中直接指定某列預設值為sequence值的nextval，currval

SQL> create sequence t_seq;

Sequence created.

Elapsed: 00:00:00.03
create table t1 (
id number default t_seq.nextval,
name varchar2(20)
4 );

Table created.

Elapsed: 00:00:00.08

插入資料（僅插入name列，id列不輸入值）

SQL> insert into t1(name) values(‘haha’);

1 row created.

Elapsed: 00:00:00.00

SQL> insert into t1(name) values(‘haha’);

1 row created.

Elapsed: 00:00:00.00

SQL> select * from t1;

ID NAME
———- ——————–
1 haha
2 haha

我們再也不用羨慕sqlserver中列值自增長了。

————————————————————————————-

顯式插入null值，依然將其變成default值特性（on null子句）

該特性不好描述，直接看實驗算了。

建立兩個sequence

SQL> create sequence t_seq;

Sequence created.
SQL> create sequence t2_seq;

Sequence created.

建立表t1，id，name ，id2 三列，並在id2列使用on null 子句
SQL>create table t1 (
id number default t_seq.nextval,
name varchar2(20),
id2 number default on null t2_seq.nextval
5 );

Table created.

插入資料

第一行，顯式指定三列值

SQL> insert into t1(id,name,id2) values(1,’Hank’,101);

1 row created.

第二行，顯式指定name列值

SQL> insert into t1(name) values(‘Hank2′);

1 row created.

第三行，顯式指定id,id2為null，name列值為hank3

SQL> insert into t1(id,name,id2) values(null,’Hank3′,null);

1 row created.

檢視三行分別的結果。

SQL> select * from t1;

ID NAME ID2
———- ——————– ———-
1 Hank 101                                                  —- 三列都為原本指定的值
1 Hank2 1                                                    —–1列和3列分別都為預設的sequence值
Hank3 2                                                   —–顯 式指定null後，id2 on null列 也插入預設值2，而id列則插入顯式指定的null值。

這就是區別，描述的不好，但實驗結果應該是蠻清楚的。

原文連結：