表中已有数据，将表中某个字段为空的改为非空

最新推荐文章于 2024-03-04 11:43:23 发布

转载最新推荐文章于 2024-03-04 11:43:23 发布 · 2k 阅读

文章标签：

本文介绍了一种在Oracle数据库中高效地将表字段修改为非空的方法，尤其适用于大型表。通过使用enablenovalidate属性，可以仅对新插入的数据应用非空约束，并逐步处理现有数据。

1.需求：
表t中大概有5亿条数据，现需要将表中的is_validate字段改为非空。考虑到表中有大量数据，而且表还是实时插入，如果直接修改为not null,将会使用很长时间。
发现加not null 可以使旧的数据不生效，只对新数据生效。即enable novalidate属性.
2.实施过程:
1)查看表结构
SQL> desc t
名称是否为空? 类型
----------------------------------------- -------- ---------------

BATCH_ID NOT NULL NUMBER(11)
OUTPUT_ID NUMBER(14)
OUTFALL_TYPE NUMBER(4)
FLUX_VALUE NUMBER(20,3)
MEASURE_TIME DATE
IS_MEASURE CHAR(1)
FLUX_SUM NUMBER(20,3)
INSERT_TIME DATE
IS_VALIDATE CHAR(1)
2)修改字段is_validate为非空，只对新数据生效
SQL> alter table t modify is_validate not null enable novalidate;
表已更改。
已用时间: 00: 00: 00.85
3)更新表中为空的值
SQL> update t set is_validate=0 where is_validate is null;
已更新7766852行。
已用时间: 00: 30: 18.67
SQL> commit;
4)查看约束状态
SQL> select table_name,constraint_name,status,deferrable,deferred,validated from user_constraints where table_name='T';

TABLE_NAME CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------ ------------------------------ -------- -------------- --------- -------------
T SYS_C005606 ENABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

已用时间: 00: 00: 00.00

4)启用约束
SQL> alter table t modify constraint sys_c005606 enable validate;
表已更改。

已用时间: 00: 02: 39.90

5)使trace跟踪查看两者调用情况

alter session set events '10046 trace name context forever, level 12';

alter table t modify status not null enable novalidate;
alter session set events '10046 trace name context off';

alter session set events '10046 trace name context forever, level 12';

alter table t modify status not null；
alter session set events '10046 trace name context off';

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

转载于:http://blog.itpub.net/26937943/viewspace-1658903/