oracle表添加带有默认值的列（11g&&12c）

最新推荐文章于 2024-05-04 09:38:05 发布

conghe6716

最新推荐文章于 2024-05-04 09:38:05 发布

阅读量669

点赞数

文章标签：数据库

有时需要对表增加带有默认值的列，这时候如果表的数据量很大，那么会花费很长时间，在此期间，表上会加6级锁，连select都会被阻塞。这在生产系统是不可接受的。从11g开始，这种情况有了明显的改善。如果增加的列复合default+not null这两个条件的话，它只会去更新数据字典，不会去更新物理行，所以操作几乎可以很短的时间就可以完成，且不会对生产产生影响。注意，这两个条件缺一不可，否则还是会很慢。12c就无所谓了，两种操作都很快。

11g环境：

SQL> select * from v$version where rownum<=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

创建测试表：

create table ming as select * from dba_objects;

插入测试数据：

begin

for i in 1..40

loop

insert into ming select * from dba_objects;

commit;

end loop;

end;

表的信息：

04:00:06 SQL> select count(*) from ming;

COUNT(*)

----------

2981602

1 row selected.

Elapsed: 00:00:00.58

04:00:23 SQL> select bytes from user_segments where segment_name='MING';

BYTES

----------

352321536

1 row selected.

Elapsed: 00:00:00.26

增加一列：

04:01:47 SQL> alter table ming add test1 number default 11111111111111111111111111;

Table altered.

Elapsed: 00:02:45.83

SQL> select bytes from user_segments where segment_name='MING';

BYTES

----------

377487360

1 row selected.

04:20:43 SQL> alter table ming add test2 number default 22222222222222222222222222 not null;

Table altered.

Elapsed: 00:00:01.52

SQL> select bytes from user_segments where segment_name='MING';

BYTES

----------

377487360

1 row selected.

可以看到：

1.default+not null的时间远小于default。

2.表的大小也没有变化，表并没有实际去更新物理行，只是更新了数据字典。

检查表的信息：

SQL> select COLUMN_NAME,COLUMN_ID,HIDDEN_COLUMN,VIRTUAL_COLUMN from dba_tab_cols where table_name='MING' AND owner='SCOTT';

COLUMN_NAME COLUMN_ID HID VIR

------------------------------ ---------- --- ---

OWNER 1 NO NO

OBJECT_NAME 2 NO NO

SUBOBJECT_NAME 3 NO NO

OBJECT_ID 4 NO NO

DATA_OBJECT_ID 5 NO NO

OBJECT_TYPE 6 NO NO

CREATED 7 NO NO

LAST_DDL_TIME 8 NO NO

TIMESTAMP 9 NO NO

STATUS 10 NO NO

TEMPORARY 11 NO NO

COLUMN_NAME COLUMN_ID HID VIR

------------------------------ ---------- --- ---

GENERATED 12 NO NO

SECONDARY 13 NO NO

NAMESPACE 14 NO NO

EDITION_NAME 15 NO NO

TEST1 16 NO NO

TEST2 17 NO NO

17 rows selected.

20:11:57 SQL> select count(*) from ming where test1=11111111111111111111111111;

COUNT(*)

----------

2983037

1 row selected.

Elapsed: 00:00:00.28

20:16:22 SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID 8wfj873dafr1y, child number 0

-------------------------------------

select count(*) from ming where test1=11111111111111111111111111

Plan hash value: 4100609557

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 11630 (100)| |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | TABLE ACCESS FULL| MING | 2381K| 29M| 11630 (1)| 00:02:20 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("TEST1"=11111111111111111111111111)

Note

-----

- dynamic sampling used for this statement (level=2)

23 rows selected.

Elapsed: 00:00:00.51

20:16:25 SQL> select count(*) from ming where test2=22222222222222222222222222;

COUNT(*)

----------

2983037

1 row selected.

Elapsed: 00:00:00.37

20:16:47 SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID dx63n3g9j3zuk, child number 0

-------------------------------------

select count(*) from ming where test2=22222222222222222222222222

Plan hash value: 4100609557

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 11631 (100)| |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | TABLE ACCESS FULL| MING | 2381K| 29M| 11631 (1)| 00:02:20 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(NVL("TEST2",22222222222222222222222222)=22222222222222222222222222)

Note

-----

- dynamic sampling used for this statement (level=2)

23 rows selected.

Elapsed: 00:00:00.02

所以，oracle认为default+not null 的方式添加的列是空列，所以用nvl函数做了一次转换。

表压缩之后;

SQL> alter table ming compress;

Table altered.

Elapsed: 00:00:00.32

20:37:06 SQL> alter table ming add test3 number default 3 not null;

Table altered.

Elapsed: 00:00:00.25

20:38:09 SQL> alter table ming add tes4 number default 4;

alter table ming add tes4 number default 4

ERROR at line 1:

ORA-39726: unsupported add/drop column operation on compressed tables

Elapsed: 00:00:00.20

所以，表压缩之后，default+not null是可以操作的；但是只有default是不行的。

这个时候可以采用下面的方法：但是这种方法之后插入的行是有值得，之前的行是没有的，所以可以找时间批量提交修改。

20:40:27 SQL> alter table ming add test4 number;

Table altered.

Elapsed: 00:00:00.14

20:41:18 SQL> alter table ming modify test4 number default 4;

Table altered.

Elapsed: 00:00:00.20

下面再来看看12c的情况：

MING@192.168.61.2/ming(MING)> select * from v$version where rownum<=1;

BANNER CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

创建表，插入数据，同上，省略：

MING@192.168.61.2/ming(MING)> select count(*) from ming;

COUNT(*)

----------

2999159

此时表的大小：

MING@192.168.61.2/ming(MING)> select bytes from user_segments where segment_name='MING';

BYTES

----------

436207616

增加一列，只有default值，没有not null：

MING@192.168.61.2/ming(MING)> alter table ming add test1 number default 11111111111111111111111111;

Table altered.

Elapsed: 00:00:01.22

增加一列之后，表的大小：

MING@192.168.61.2/ming(MING)> select bytes from user_segments where segment_name='MING';

BYTES

----------

436207616

12c和11g已经不同了，12c只加default值速度极快，表的大小也没有变化。说明12c加default的列和11g加default+not null一样，只会去修改数据字典，不会动物理行。

现在再看一下default+not null的情况：

MING@192.168.61.2/ming(MING)> alter table ming add test2 number default 22222222222222222222222222 not null;

Table altered.

Elapsed: 00:00:00.18

MING@192.168.61.2/ming(MING)> select bytes from user_segments where segment_name='MING';

BYTES

----------

436207616

可以总结：

1.对于12c，新增default或者default+not null的列，速度都很快，都只会去修改数据字典，不会动物理行。

检查表的信息：

MING@192.168.61.2/ming(MING)> select COLUMN_NAME,COLUMN_ID,HIDDEN_COLUMN,VIRTUAL_COLUMN from dba_tab_cols where table_name='MING' AND owner='MING';

COLUMN_NAME COLUMN_ID HID VIR

-------------------- ---------- --- ---

OWNER 1 NO NO

OBJECT_NAME 2 NO NO

SUBOBJECT_NAME 3 NO NO

OBJECT_ID 4 NO NO

DATA_OBJECT_ID 5 NO NO

OBJECT_TYPE 6 NO NO

CREATED 7 NO NO

LAST_DDL_TIME 8 NO NO

TIMESTAMP 9 NO NO

STATUS 10 NO NO

TEMPORARY 11 NO NO

COLUMN_NAME COLUMN_ID HID VIR

-------------------- ---------- --- ---

GENERATED 12 NO NO

SECONDARY 13 NO NO

NAMESPACE 14 NO NO

EDITION_NAME 15 NO NO

SHARING 16 NO NO

EDITIONABLE 17 NO NO

ORACLE_MAINTAINED 18 NO NO

APPLICATION 19 NO NO

DEFAULT_COLLATION 20 NO NO

DUPLICATED 21 NO NO

SHARDED 22 NO NO

COLUMN_NAME COLUMN_ID HID VIR

-------------------- ---------- --- ---

CREATED_APPID 23 NO NO

CREATED_VSNID 24 NO NO

MODIFIED_APPID 25 NO NO

MODIFIED_VSNID 26 NO NO

TEST1 27 NO NO

SYS_NC00027$ YES NO

TEST2 28 NO NO

29 rows selected.

这里发现对了一个隐藏列；这个隐藏列是在添加default的列的时候添加的，添加default+not null 的列是不会有的。

select count(*) from ming where test1=11111111111111111111111111;

MING@192.168.61.2/ming(MING)> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID gdh9v3mqm26s9, child number 1

-------------------------------------

select count(*) from ming where test1=11111111111111111111111111

Plan hash value: 4100609557

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 114 (100)| |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------------

|* 2 | TABLE ACCESS FULL| MING | 2999K| 37M| 114 (2)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00027$",0)),NULL,NVL("

TEST1",11111111111111111111111111),'0',NVL("TEST1",111111111111111111111

11111),'1',"TEST1")=11111111111111111111111111)

Note

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------------

- statistics feedback used for this statement

select count(*) from ming where test2=22222222222222222222222222;

MING@192.168.61.2/ming(MING)> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------------

SQL_ID fq2bsz7v5mtw2, child number 0

-------------------------------------

select count(*) from ming where test2=22222222222222222222222222

Plan hash value: 4100609557

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 114 (100)| |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------------

|* 2 | TABLE ACCESS FULL| MING | 222 | 2886 | 114 (2)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(NVL("TEST2",22222222222222222222222222)=222222222222222222

22222222)

表压缩之后：

MING@192.168.61.2/ming(MING)> alter table ming compress;

Table altered.

Elapsed: 00:00:00.16

08:51:43 MING@192.168.61.2/ming(MING)> alter table ming add test5 number default 3 not null;

alter table ming add test5 number default 3 not null *

ERROR at line 1:

ORA-39726: unsupported add/drop column operation on compressed tables

08:51:57 MING@192.168.61.2/ming(MING)> alter table ming add tes6 number default 4;

alter table ming add tes6 number default 4

ERROR at line 1:

ORA-39726: unsupported add/drop column operation on compressed tables

12c表compress之后，两种方式都不能再添加列了，这与11g不同，11g中default+not null是可以操作的；但是只有default是不行的。当然12c还是可与用下面的方法：

SQL> alter table ming add test4 number;

SQL> alter table ming modify test4 number default 4;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2153741/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31480688/viewspace-2153741/

conghe6716

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle表添加带有默认值的列（11g&&12c）

有时需要对表增加带有默认值的列，这时候如果表的数据量很大，那么会花费很长时间，在此期间，表上会加6级锁，连select都会被阻塞。这在生产系统是不可接受的。从11g开始，这种情况有了明显的改善。如果增加的列复合defaul...
复制链接

扫一扫