oracle 11g对大表中添加DEFAULT值的NOT NULL字段速度有大幅度的提升

傷城~ 2022-06-10 00:21 81阅读 0赞

今天同事问我一个问题他说在一张2000万的表上增加了一个字段并字段一个默认值，执行这条语句（alter table tablename add new\_col default ‘col’）一个小时没有执行完，问我有没有其他解决方法

我查了一下资料发现

Oracle11g中，在添加一个包含DEFAULT值的NOT NULL字段，Oracle不会去更新现有的数据，Oracle需要做的不过是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。这张表利用BLOB字段存储ALTER TABLE添加的DEFAULT值

然后我测试一下情况（在11g中测试alter table tablename add new\_col default ‘col’ 后代not null和不带not null的区别）

1.新建一张表test

SQL> create table test (id number,name varchar(10));

表已创建。

2.向test表中插入500000记录

SQL> begin

2  for i in 1..500000 loop

3  insert into test values(i,'jack');

4  end loop;

5  end;

6  /

PL/SQL  过程已成功完成。

SQL> select count(1) from test;

COUNT(1)

\----------

500000

SQL>

SQL> set timing on

3．向表中添加一个新的字段NEW\_COL1带有默认值如下语句不带NOT NULL

ALTER TABLE t ADD NEW\_COL1 CHAR(10) DEFAULT 'TESTCOLUMN'

SQL> ALTER TABLE test ADD NEW\_COL1 CHAR(10) DEFAULT 'TESTCOLUMN';

表已更改。

已用时间:  00: 00: 43.23

SQL>

4.  向表中添加一个新的字段NEW\_COL2带有默认值语句中带上 NOT NULL

ALTER TABLE test ADD NEW\_COL2 CHAR(10) DEFAULT 'TESTCOLUMN' NOT NULL;

SQL> ALTER TABLE test ADD NEW\_COL2 CHAR(10) DEFAULT 'TESTCOLUMN' NOT NULL;

表已更改。

已用时间:  00: 00: 00.25

惊奇的发现两者差别太大了带not null的用时不到1秒

这是11g对新增一个not null字段带有默认值进行了优化，默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中

如下：可以查询test表中添加的默认值

SQL> SELECT OBJECT\_ID FROM DBA\_OBJECTS WHERE OBJECT\_NAME = 'TEST' and owner='IMUSE01';

OBJECT\_ID

\----------

74063

SQL> select COLNUM,BINARYDEFVAL from ecol$ where TABOBJ\#= 74063;

COLNUM

\----------

BINARYDEFVAL

\--------------------------------------------------------------------------------

4

54455354434F4C554D4E

默认值可以转换一下：

SQL> SET SERVEROUT ON

SQL> DECLARE

2  V\_BLOB VARCHAR2(32767) DEFAULT '54455354434F4C554D4E';

3  BEGIN

4  FOR I IN 1..LENGTH(V\_BLOB)/2 LOOP

5  DBMS\_OUTPUT.PUT(CHR(TO\_NUMBER(SUBSTR(V\_BLOB, (I - 1) \* 2 + 1, 2), 'XXX')));

6  END LOOP;

7  DBMS\_OUTPUT.NEW\_LINE;

8  END;

9  /

TESTCOLUMN

PL/SQL 过程已成功完成。

SQL>

发表评论取消回复

表情：

评论列表（有 0 条评论，81人围观）

还没有评论，来说两句吧...

相关阅读

相关 Oracle 11g 向表中大字段插入数据

建用户并授权 SQL> create user samp identified by samp ; User created. SQL> grant con...

野性酷女/ 2024年04月18日 02:56/ 0 赞/ 57 阅读

相关 MySQL 带默认值的表插入默认值数据values(default,default) 或values(NULL,NULL);

CREATE TABLE test( id SERIAL PRIMARY KEY, \`datetime\` timestamp NOT NULL DEFAULT CU

柔光的暖阳◎/ 2023年10月18日 22:01/ 0 赞/ 157 阅读

相关 Oracle 11g 修改表的所属表空间

在做数据库测试的时候，一直向oracle库写数据，然后就报错，表空间不足分析解决： 1、提示表空间不足后，想起当时创建表的时候没有新建表空间，直接默认的，已经忘记

柔光的暖阳◎/ 2022年11月27日 08:46/ 0 赞/ 179 阅读

相关 Oracle11g中Exp空表的问题

数据是从oracle10g导出的，导入oracle11g一切都正常运行了一段时间，需要从11g中exp导出使用这次备份的再次导入到11g时发现少了很多表，还

一时失言乱红尘/ 2022年09月30日 09:53/ 0 赞/ 220 阅读

相关 oracle 11g 表空间大小问题

oracle 11g版本，创建数据库表空间，默认单个数据文件最大为32G,如果数据文件大于32G,可以增加数据文件。比如，创建表空间,设置自增长 autoextend

以你之姓@/ 2022年08月24日 00:57/ 0 赞/ 257 阅读

相关 mysql中表中字段中值的删除和添加

一、mysql删除表字段值前6位将表zsgoods中的Image0字段前6位字符串面删除，然后将新的字符串重新放到Image0字段中 update zsg

柔光的暖阳◎/ 2022年08月10日 06:09/ 0 赞/ 255 阅读

相关 SQL查询一个表中类别字段中最大值对应的值

SQL查询一个表中类别字段中最大值对应的值一、SQL查询一个表中类别字段中最大值对应的值（对一张表的操作）：问题：根据教师id查询checkOutAut

缺乏、安全感/ 2022年07月11日 09:09/ 0 赞/ 653 阅读

相关 oracle 11g对大表中添加DEFAULT值的NOT NULL字段速度有大幅度的提升

今天同事问我一个问题他说在一张2000万的表上增加了一个字段并字段一个默认值，执行这条语句（alter table tablename add new\_col default

傷城~/ 2022年06月10日 00:21/ 0 赞/ 82 阅读

相关 default not null

default只在insert的时候没有显式赋值的时候生效，若insert时赋值null或update设为null 这时候notnull的作用就显示出来了 add colum

桃扇骨/ 2022年04月03日 03:16/ 0 赞/ 208 阅读

相关 postgresql 修改表的字段由NOT NULL修改为NULL

postgresql 修改表的字段由NOT NULL修改为NULL： alter table mytable alter test drop not null;

古城微笑少年丶/ 2022年01月11日 10:57/ 0 赞/ 1002 阅读