oracle数据库添加字段不同方式的实验测试

最新推荐文章于 2023-04-04 17:24:06 发布

#慧#

最新推荐文章于 2023-04-04 17:24:06 发布

阅读量545

点赞数

分类专栏： oracle 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_41561862/article/details/107072837

版权

oracle 专栏收录该内容

74 篇文章 2 订阅

订阅专栏

（1）创建测试表

--测试环境下的一张表alter_test，数据量如下

SQL> select count(*) from alter_test;

  COUNT(*)
----------
  37126467

（2）新增字段测试，增加字段不带默认值

给表alter_test新增一个字段：alter table alter_test add test_column1 char(1);

--执行10046事件
alter session set events '10046 trace name context forever,level 12';

--给表alter_test新增一个字段
alter table alter_test add  test_column1 char(1);

--关闭10046事件
alter session set events '10046 trace name context off';


--获得当前trace文件生成路径
select tracefile from v$process where addr in (select paddr from v$session where sid in (select distinct sid from v$mystat));

--使用oracle server自带的tkprof工具解析trace文件
[oracle@test ~]$ tkprof /U01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_43138.trc /home/oracle/alter_test.log

TKPROF: Release 11.2.0.3.0 - Development on Sat Apr 25 19:01:05 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


[oracle@test ~]$ ll /home/oracle/alter_test.log
-rw-r--r-- 1 oracle oinstall 61531 Apr 25 19:01 /home/oracle/alter_test.log

（3）查看解析的trace文件

从解析后的trace文件可以看到下面的信息：

说明此时对表alter_test以NOWAIT方式，加了ROW EXCLUSIVE模式锁。接下来就是执行的新增字段的SQL语句，

直接看关闭10046事件之前的最后一句；

执行的是col$表的更新语句。从表名看，col$是列的数据字典表，使用了绑定变量，那这些值是什么，就成了问题的关键。

使用v$sql_bind_capture可以查看仍在内存中的SQL绑定变量值，10046事件前的最后一句SQL使用的绑定变量值如下：

select sql_id,name,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_id='6vqvn8ya0xybh' order by last_captured,position;

从trace文件中可以看到 update col$语句中一共有20个绑定变量，上面SQL显示的绑定变量值同样是20个，说明是对应的。

（截图显示的有重复的是因为字段TIESHEN_BIND是测试之前不久新加的字段）。

update col$的where条件是编号为1和2的绑定变量值，这里显示的是14936和TEST_COLUMN1。再来看看这个14936是什么，从上面SQL的条件obj#=:1，猜测是一个对象，

select object_name,object_id from dba_objects where object_id=14936;

于是，可以猜测，之前已经将TEST_COLUMN1字段加入了相应的数据字典表，最后一句就是更新col$中alter_test表TEST_COLUMN1字段的一些信息。trace文件的最后列出了所有递归调用语句的消耗统计：

上面我们了解到了新增字段的SQL语句背后，Oracle大致做了什么操作。接着，我们通过实验来看下不同方式新增字段的效率。

（4）实验一，添加可为空不带默认值的字段

如上刚才的实验我们添加的就是这种形式的字段，时间是比较快速的。

SQL> alter table alter_test add  test_column1 char(1);
 
Table altered
 
Executed in 0.062 seconds

新增一个允许NULL，且无默认值的字段。从10046的trace文件看获得的是一个ROW EXCLUSIVE模式锁。

（5）实验二，添加可为空带默认值的字段

alter session set events '10046 trace name context forever,level 12';
alter table alter_test add  test_column2 char(1) default 'N';
alter session set events '10046 trace name context off';

SQL> set timing on
SQL> alter table alter_test add  test_column2 char(1) default 'N';

Table altered.

Elapsed: 00:12:15.37

可以看到时间是12多分钟。分析产生的trace日志如下：

从10046的trace文件看表会首先用EXCLUSIVE模式锁来锁定表。同时，在最后执行了更新字段test_column2为默认值的操作。因此不难想像，前台反映的现象就是这个操作处于hang状态，并且影响其他session对该表的操作，为什么耗时这样久，原因就是这个操作需要更新表中所有记录该字段为默认值，另外，还会因为数据量的增加，可能需要更多的UNDO空间，进而可能因为一条新增字段的操作，导致整个库的UNDO表空间不够用，不仅影响对这张表的正常增删改操作(因为获取了最高级别EXCLUSIVE锁)，还有可能影响其他业务功能(因为UNDO表空间不够用)。

（6）实验三、添加带默认值且不为空的字段

alter session set events '10046 trace name context forever,level 12';
set timing on
alter table alter_test add  test_column3 char(1) default 'N' not null;
alter session set events '10046 trace name context off';

select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

可以看到新增一个包含NOT NULL约束，有默认值的字段，用时很短。从10046的trace文件看，会获得一个ROW EXCLUSIVE模式锁来锁定表。

锁的级别比实验二要低，而且该默认值是存储于数据字典表中的，并不是保存在原表记录上，即新增一个NOT NULL和默认值的字段，以后每次需要使用该字段时，默认值都是从数据字典中查询到的，这样就减少了新增字段时的DDL语句时间，也减少了存储空间(不用每条需要使用默认值的记录都存储默认值)。

像上面第一次增加列的操作时，会同时更新sys.ecol$和sys.col$数据字典表，若以后再修改这个默认值，则只是会修改sys.col$的值，且以后每次查询也是从sys.col$的default$列获取默认值，我们可以根据sys.ecol$、sys.col$和dba_objects查询相关表和字段信息。

select * from dba_objects where object_name='ALTER_TEST';

SELECT * FROM sys.ecol$;

查看第一次的默认值:

select name,default$ from sys.col$ where obj#=14936 and col#=60;

尝试修改默认值，从N变为Y;

alter table alter_test modify test_column3 default 'Y';

查看修改后sys.col$记录的默认值:

select name,default$ from sys.col$ where obj#=14936 and col#=61;

11g的官方文档也介绍了，Oracle增加了这种新特性，对新增字段操作做了上面这些优化，如果新增一个含有默认值的字段，那么会立即更新每一行，在更新过程中，会有一个EXCLUSIVE级别的锁在该表上。如果指定NOT NULL和默认值，则会进行优化，降低阻止DML操作的时间。

（7）实验四、新增一个字段为not null

alter table alter_test add  test_column4 char(1) not null;

新增一个仅有NOT NULL约束，没有默认值的字段，则需要表为空。如果增加一个仅有NOT NULL的约束字段，那么需要表不能包含任何记录，否则就需要必须指定一个默认值，这也好理解，如果执行之前有记录，又要求NOT NULL，那么之前的记录字段默认值是什么就需要指定才行。

（8）实验五、删除表字段

alter session set events '10046 trace name context forever,level 12';
set timing on
alter table alter_test drop column test_column3;
alter session set events '10046 trace name context off';
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

从10046的trace文件看，也是获得了一个EXCLUSIVE锁，进而在删除字段的过程中是对整张表的DML操作有影响的。

小结：

1. 11g以上的版本，如果使用NOT NULL和默认值的方式新增字段，那么执行时间会大大降低。且只会有一个ROW EXCLUSIVE级别锁。

2. 11g以上的版本，如果使用默认值，没有NOT NULL约束的方式新增字段，那么执行时间会很久，取决于表中数据量的大小，获得的是EXCLUSIVE级别锁，期间会影响所有记录的DML操作，可能会因UNDO不足对其他操作有影响。

3. 11g以上的版本，如果新增字段没有默认值，也没有NOT NULL约束，则还是会使用ROW EXCLUSIVE模式锁，但由于不需要更新字段值，执行时间也是比较短。

补充：今天在操作一张数据量7亿左右的表增加带默认值且不为空，耗费时间为0.016秒。并测试了修改数据类型所耗费的时间，记录如下：

表结构如下：
create table USER_TEST
(
  UIN           NUMBER(16) not null,
  IP_ADDR       VARCHAR2(16),
  USER_TIME     DATE,
)


SQL> alter table user_test modify (IP_ADDR varchar2(20));
 
Table altered
 
Executed in 0.031 seconds
 
SQL> alter table user_test modify (UIN NUMBER(20));
 
Table altered
 
Executed in 0.015 seconds


现在表user_test中字段USER_TIME的值有为null的，执行如下语句会报错：

SQL> alter table user_test modify USER_TIME default sysdate not null;
 
alter table user_test modify USER_TIME default sysdate not null
 
ORA-02296: 无法启用 (.) - 找到空值

因此：对于表字段的数据已经存在空值，就不能修改为not null约束了，会报错。

如果直接修改update_time字段有默认值，则只对新插入的数据有效。

alter table user_test modify USER_TIME default sysdate;

修改字段的默认值，只对新插入的数据有效。