向大表添加含有默認值的欄位

        現有表bigtable  ---------10000000筆數據，要求不再不影響應用的情況下向該表添加一個含有默認值的欄位COL_NEW

 

一、在Oracle11.2.0.3數據庫中：

 

方案1、

ALTER TABLE BIGTABLE ADD COL_NEW NUMBER(9) DEFAULT 1;  耗時且鎖表、影響應用的使用

 

方案二，可以將該語句改為：
ALTER TABLE BIGTABLE ADD COL_NEW NUMBER(9);

ALTER TABLE BIGTABLE MODIFY COL_NEW NUMBER(9) DEFAULT 1;

     效能會有所提升，但是之前存在的數據行中該欄位值為空

 

方案三，如果將該新增含默認值的欄位添加 NOT NULL，速度會大幅提升
ALTER TABLE BIGTABLE ADD COL_NEW NUMBER(9) DEFAULT 1 NOT NULL; 

      此方案是利用11g新特性

 

方案四，利用ROWNUM小批量的update欄位值

ALTER TABLE BIGTABLE ADD COL_NEW NUMBER(9);

 

BEGIN

   WHILE 1=1 

LOOP

EXECUTE IMMEDIATE

'UPDATE BIGTABLE SET COL_NEW=1

                 WHERE COL_NEW IS NULL AND ROWNUM<=100';

COMMIT;

END LOOP;

END;

/

        耗時、不影響應用的正常使用

 

方案5、利用cursor以及rowid order by效能會很高，且只做一次Full table scan

DECLARE
  CURSOR UPDATE_FOR IS
      SELECT ROWID ROW_ID FROM WHISPC.BIGTABLE ORDER BY ROW_ID;
  V_COUNTER NUMBER;
 
BEGIN
  V_COUNTER :=0;
  FOR ROW_CUR IN UPDATE_FOR LOOP   ------------------------遍歷ROWID
     UPDATE WHISPC.BIGTABLE  SET COL_NEW00=00 WHERE ROWID=ROW_CUR.ROW_ID;
   V_COUNTER :=V_COUNTER + 1;
   IF (V_COUNTER >= 100) THEN
      COMMIT;
     V_COUNTER := 0;
   END IF;
  END LOOP;
  COMMIT;
  END;

 

二，在MSSQLSERVER中

方案一、利用TOP N的方式小批量修改數據

ALTER TABLE dbo.BIGTABLE ADD COL_NEW VARCHAR(20) NULL
GO

 

declare @Count int
DECLARE @I INT
SET @I=1
select @Count =COUNT(*) FROM dbo.BIGTABLE
WHILE @IBEGIN  
update top (10)  BIGTABLE
set Name='A'
WHERE
ISNULL(NAME,'')<>'A'
 --     ROW_NUMBER() over(order by name)

SET  @I=@I+10
END;

/

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

转载于:http://blog.itpub.net/24867586/viewspace-758040/