3亿条数据的一个表插入新字段，并赋予默认值，如何做

幻雪青枫

robinxia 发表于 2012-2-26 12:59
1.create  table test_bak as select a.*,default_value as col1,default_val as col2 from test a;
2.ren ...

恩，这个方法确实是大家推荐的一种方法，只是我测试了一下需要creaTE INDEX 和constraint，不知道其它依赖对象如存储过程等时候会受到影响等？

幻雪青枫

robinxia 发表于 2012-2-26 12:59
1.create  table test_bak as select a.*,default_value as col1,default_val as col2 from test a;
2.ren ...

恩，这种方法，我做了一个300W数据的测试，还不错，只是需要create index 和constraints，不知道会不会对依赖此表的其它对象产生影响

yobyin

两天前刚做了测试，alter 加默认值太慢了。 create table t as select * from t2 快

lucyne

altert table test add a int；  先加字段
alter table test modify a default xx;再加默认值，现在加的默认值只对以后的记录有效
update test set a=xx ,b= xxx  where xxx; commit; 分批操作，将表里的记录更新为默认值

luoyoumou

-- 个人建议：
---------------------------------------------------------------------------------------------------------------------------------------
-- *1. 如果原表有主键，可以新建一张表test2(注意表的其他字段应该和test1完全一致,最好用select dbms_metadata.get_ddl('TABLE','TEST) from dual 得到的代码去修改好后，执行创建test2表,
       另外，最好用分区表:如果主键是日期类型，可考虑按月分区；如果主键是int类型，可以考虑用范围分区)；
--     其中test2表只比test1表多 colu1、colu2 两个字段，其他字段(包括数据类型、精确度、默认值)应该完全一致，还应该考虑表空间的存放问题;
--     如果原表(test表)有比较频繁的DML操作，则在test2上创建一个触发器用以与test同步其增、删、改操作(在触发器中注意多考虑colu1、colu2 两个字段的更新问题)
--     test2表创建好，触发器创建好后，就可以考虑往test2表中插入数据(可采用主键循环插入，例如：如果主键是日期类型，我可以按月来循环操作；如果主键是int类型，我可以按范围(例如，每次插入10万，循环结束后一次提交)循环插入)

-- *2. 如果原表没有主键，可以新建一张表test2(注意表的其他字段应该和test1完全一致,最好用select dbms_metadata.get_ddl('TABLE','TEST) from dual 得到的代码去修改好后，执行创建test2表,
       另外，最好用分区表:如果主键是日期类型，可考虑按月分区；如果主键是int类型，可以考虑用范围分区)；
--     其中test2表比test1表多 colu1、colu2、rowid三个字段，其中rowid字段用来存放test表中的rowid，其他字段(包括数据类型、精确度、默认值)应该完全一致，还应该考虑表空间的存放问题;
--     如果原表(test表)有比较频繁的DML操作，则在test2上创建一个触发器用以与test同步(用rowid同步)其增、删、改操作(在触发器中注意多考虑colu1、colu2 两个字段的更新问题)
--     test2表创建好，触发器创建好后，就可以考虑往test2表中插入数据(可采用主键循环插入，例如：如果主键是日期类型，我可以按月来循环操作；如果主键是int类型，我可以按范围(例如，每次插入10万，循环结束后一次提交)循环插入)

luoyoumou

-- 个人建议：
---------------------------------------------------------------------------------------------------------------------------------------
-- *1. 如果原表有主键，可以新建一张表test2(注意表的其他字段应该和test1完全一致,最好用select dbms_metadata.get_ddl('TABLE','TEST) from dual 得到的代码去修改好后，执行创建test2表,
       另外，最好用分区表:如果主键是日期类型，可考虑按月分区；如果主键是int类型，可以考虑用范围分区)；
--     其中test2表只比test1表多 colu1、colu2 两个字段，其他字段(包括数据类型、精确度、默认值)应该完全一致，还应该考虑表空间的存放问题;
--     如果原表(test表)有比较频繁的DML操作，则在test2上创建一个触发器用以与test同步其增、删、改操作(在触发器中注意多考虑colu1、colu2 两个字段的更新问题)
--     test2表创建好，触发器创建好后，就可以考虑往test2表中插入数据(可采用主键循环插入，例如：如果主键是日期类型，我可以按月来循环操作；如果主键是int类型，我可以按范围(例如，每次插入10万，循环结束后一次提交)循环插入)
--     数据插入好后，就可以删除test，然后重命名test2为test (删除及重命名表操作最好封装到一个事务里，这样才不会影响你 的业务)

-- *2. 如果原表没有主键，可以新建一张表test2(注意表的其他字段应该和test1完全一致,最好用select dbms_metadata.get_ddl('TABLE','TEST) from dual 得到的代码去修改好后，执行创建test2表,
       另外，最好用分区表:如果主键是日期类型，可考虑按月分区；如果主键是int类型，可以考虑用范围分区)；
--     其中test2表比test1表多 colu1、colu2、rowid三个字段，其中rowid字段用来存放test表中的rowid，其他字段(包括数据类型、精确度、默认值)应该完全一致，还应该考虑表空间的存放问题;
--     如果原表(test表)有比较频繁的DML操作，则在test2上创建一个触发器用以与test同步(用rowid同步)其增、删、改操作(在触发器中注意多考虑colu1、colu2 两个字段的更新问题)
--     test2表创建好，触发器创建好后，就可以考虑往test2表中插入数据(可采用主键循环插入，例如：如果主键是日期类型，我可以按月来循环操作；如果主键是int类型，我可以按范围(例如，每次插入10万，循环结束后一次提交)循环插入)
--     数据插入好后，就可以删除test，然后重命名test2为test (删除及重命名表操作最好封装到一个事务里，这样才不会影响你 的业务)

-- 例如：如果原表(没有主键)
-- 原表：
create table test(
  id number(18,0),
  uname varchar2(20)
);

create table test2(
  rowids rowid,
  id number(18,0),
  uname varchar2(20),
  colu1 varchar2(20) default 'chn' not null,
  colu2 number(18,0) default 2222 not null
);

CREATE OR REPLACE TRIGGER test_trg
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW
BEGIN
  IF INSERTING THEN
  BEGIN
    INSERT INTO test2(rowids,id,uname,colu1,colu2)
    VALUES(:NEW.rowid,:NEW.id,:NEW.uname,'chn',2222);
  END;
  ELSIF UPDATING THEN
  BEGIN
    UPDATE test2 SET rowids=:NEW.rowid,id=:NEW.id,uname=:NEW.uname WHERE rowids=:OLD.rowid;
  END;
  ELSE
  BEGIN
    DELETE FROM test2 WHERE rowids=:OLD.rowid;
  END;
  END IF;
END;
/

-- 测试：
-- 单行插入操作测试：
INSERT INTO test(id,uname) values(1,'lym1');
INSERT INTO test(id,uname) values(2,'lym2');
INSERT INTO test(id,uname) values(3,'lym3');
INSERT INTO test(id,uname) values(4,'lym4');
INSERT INTO test(id,uname) values(5,'lym5');
COMMIT;

SELECT * FROM test;
SELECT * FROM test2;

-- 多行插入操作测试：
INSERT INTO test(id,uname)
SELECT 6, 'zct2' FROM dual
UNION ALL
SELECT 7, 'hqg3' FROM dual;
COMMIT;

-- 单行更新操作测试：
UPDATE test set uname='tyg888' where id=1;
COMMIT;

SELECT * FROM test;
SELECT * FROM test2;

-- 单行删除操作测试：
delete from test where id=2;
COMMIT;

-- 以上测试均没问题

-- 然后同步两表的数据(最好用 where 条件循环控制，如我上面所说的)：用存储过程 merge into ......
-- 例如(今天已经太晚，暂略)：

lxpbs8851

create table t as select * from t2
主要生成数据和创建索引耗的时间长一点。
还有就是要指定 nologing的方式

幻雪青枫

lucyne 发表于 2012-2-27 18:02
altert table test add a int；  先加字段
alter table test modify a default xx;再加默认值，现在加的默 ...

我在自己的本子上测试update貌似时间也不短

幻雪青枫

lxpbs8851 发表于 2012-2-27 19:20
create table t as select * from t2
主要生成数据和创建索引耗的时间长一点。
还有就是要指定 nologing ...

都提到需要重建索引，为什么没人提重新加约束呢？

幻雪青枫

luoyoumou 发表于 2012-2-27 19:02
-- 个人建议：
------------------------------------------------------------------------------------- ...

哈哈，写了这么多，我先仔细看看