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


    在一张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>

  • 相关阅读:
    初中生数学题
    防御准备
    约数个数和「SDOI2015」
    暑期集训题目
    【模板】可持久化线段树 1(主席树)
    【模板】可持久化数组(可持久化线段树/平衡树)
    权值线段树&&线段树合并
    回家的路「SHOI 2012」
    Function「ZJOI2009」
    主席树-可持久化线段树学习笔记
  • 原文地址:https://www.cnblogs.com/hllnj2008/p/5173151.html
Copyright © 2020-2023  润新知