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

傷城~ 2022-06-10 00:21 101阅读 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 条评论，101人围观）

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

相关阅读

相关 Oracle 11g版本中，表空间大小调整不当的问题

在Oracle 11g版本中，如果表空间的大小调整不当，可能会引发以下几个问题： 1. **性能瓶颈**：如果表空间过大或过小，可能会影响数据库的数据读写速度，导致性能瓶颈。

谁借莪１个温暖的怀抱￠/ 2025年01月01日 07:18/ 0 赞/ 30 阅读

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

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

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

相关 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 赞/ 196 阅读

相关 Oracle11g中Exp空表的问题

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

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

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

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

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

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

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

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

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

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

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

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

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

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

相关 default not null

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

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

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

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

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