修改欄位資料型別的方法

在日常維護的過程中可能碰到這樣的問題，需要修改表的欄位型別。

 

 

對於絕大部分正常的情況，都是將表的欄位型別的長度擴大，但是有的時候是需要縮小表的欄位長度的，甚至有的時候是要修改表的資料型別的。

SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已建立。

SQL> DROP TABLE T PURGE;

表已刪除。

SQL> CREATE TABLE T AS
  2  SELECT OBJECT_ID, OWNER, OBJECT_NAME
  3  FROM DBA_OBJECTS;

表已建立。

SQL> DESC T
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- -------------------------
 OBJECT_ID                                          NUMBER
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)

SQL> SELECT MAX(LENGTH(TO_CHAR(OBJECT_ID))) FROM T;

MAX(LENGTH(TO_CHAR(OBJECT_ID)))
-------------------------------
                              5

SQL> ALTER TABLE T MODIFY OBJECT_ID VARCHAR2(10);
ALTER TABLE T MODIFY OBJECT_ID VARCHAR2(10)
                     *
第 1 行出現錯誤:
ORA-01439: 要更改資料型別, 則要修改的列必須為空

對於這種情況，Oracle要求表的欄位為空，才能進行修改。因此對應的方法一般有兩類，一類是在表上新增一列，在表內根據原列的值更新目標列。另一類方法是建立一張新表，根據源表的值更新目標表的值。

其中第一類方法最省事，由於只是表的列發生變化，因此對資料庫的物件的影響相對比較小，但是這種方法鎖表時間可能會比較長，需要儘可能在比較空閒的時間內進行，對操作的執行時間有一定的要求。最後這種方式相對容易產生行遷移，影響後續表訪問的效能。

而第二類方法中對系統影響最小的就是線上重定義方式。這種方式鎖表時間最短，基本不影響業務的線上訪問。可是這種方法也並不是沒有缺點，首先這種方法相對比較複雜，第一類方法可能僅僅兩、三個SQL就搞定了，而這種方法就需要很多的SQL語句，還要呼叫很多Oracle的包，複雜度比第一種情況高很多；第二類方法還需要考慮很多的東西，由於表被替換掉了，索引、約束、觸發器、過程、許可權、統計資訊等等這些都是需要考慮和處理的，否則就很容易造成問題；還有就是這類操作和第一種操作相比，會產生更多的REDO和UNDO資訊；更重要的一點是，這種方式有一定的限制條件，在11g以前，物化檢視的基表是無法進行線上重定義的。而如果採用這類方法的其他方式，則前面提到的那些優點就不存在了，而缺點確仍然無法避免。

因此，對於比較繁忙、資料庫可用性要求比較高的系統，對於資料量很大，直接更新要花費大量時間的表推薦採用線上重定義方式，而對於資料量不大的表，對於系統有充足維護時間的系統，可以考慮上面的第一類方式。

上面已經提到了，由於只是列的修改，而沒有對刪除原有的表，因此對系統的其他物件影響比較小。除非採用增加新列刪除舊列的方式，否則不會影響系統中索引、約束、觸發器、過程、許可權和統計資訊等物件，如果採用刪除舊列，那麼也只需要關注這個列相關的約束、索引和統計資訊。

如果可以確保維護時間，那麼第一類方法最大的問題就是行遷移，由於增加了新列，且給新列賦值，記錄的長度增加，會造成行遷移的產生，從而影響表的訪問效能。

SQL> ALTER TABLE T ADD NEW_OBJECT_ID VARCHAR2(10);

表已更改。

SQL> UPDATE T SET NEW_OBJECT_ID = ID;
UPDATE T SET NEW_OBJECT_ID = ID
                             *
第 1 行出現錯誤:
ORA-00904: "ID": 識別符號無效

SQL> UPDATE T SET NEW_OBJECT_ID = OBJECT_ID;

已更新50599行。

SQL> ALTER TABLE T DROP COLUMN OBJECT_ID;

表已更改。

SQL> ALTER TABLE T RENAME COLUMN NEW_OBJECT_ID TO OBJECT_ID;

表已更改。

雖然這種方式完成了操作，但是留下兩個問題，一個是列的位置發生了變化，這樣可能會對影響一些編碼風格不好的程式：

SQL> DESC T
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 OBJECT_ID                                          VARCHAR2(10)

SQL> INSERT INTO T (OBJECT_ID, OWNER, OBJECT_NAME) VALUES ('60000', 'YANGTK', 'T');

已建立 1 行。

SQL> INSERT INTO T VALUES ('60001', 'YANGTK', 'T');

已建立 1 行。

對於上面的標準寫法，列的順序沒有關係，但是如果採用類似下面的寫法，就會導致錯誤的出現。

另外一個問題，就是前面提到多次的行遷移：

SQL> @?/RDBMS/ADMIN/UTLCHAIN.SQL

表已建立。

SQL> ANALYZE TABLE T LIST CHAINED ROWS;

表已分析。

SQL> SELECT COUNT(*) FROM CHAINED_ROWS;

  COUNT(*)
----------
      2107

其實如果採用下面的方法就可以基本上避免上面的這兩個問題：

SQL> CREATE TABLE T AS
  2  SELECT OBJECT_ID, OWNER, OBJECT_NAME
  3  FROM DBA_OBJECTS;

表已建立。

SQL> ALTER TABLE T ADD COL_TEMP NUMBER;

表已更改。

SQL> UPDATE T SET COL_TEMP = OBJECT_ID, OBJECT_ID = NULL;

已更新50600行。

SQL> ALTER TABLE T MODIFY OBJECT_ID VARCHAR2(10);

表已更改。

SQL> UPDATE T SET OBJECT_ID = COL_TEMP, COL_TEMP = NULL;

已更新50600行。

SQL> ALTER TABLE T DROP COLUMN COL_TEMP;

表已更改。

SQL> DESC T
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ---------------------------
 OBJECT_ID                                          VARCHAR2(10)
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)

SQL> ANALYZE TABLE T LIST CHAINED ROWS;

表已分析。

SQL> SELECT COUNT(*) FROM CHAINED_ROWS;

  COUNT(*)
----------
         0

採用同時更新表中兩個列的方式，可以有效的避免行遷移的產生，因為在更新的完成後，表記錄的長度增加十分有限，只是由於OBJECT_ID的NULL出現在中間的位置，而使得每條記錄的長度增加了1，而再執行第二次操作的時候，這個長度1的代價又被消除掉了，因此這種方式更新基本上不會產生行遷移。由於列的順序沒有發生變化，也不會對應用構成很大的影響。而且原始列沒有被刪除，索引、約束等都不需要改變。

這種方法的缺點在於需要更新兩次，更新資料量比較大，而且每次更新產生的REDO和UNDO都比直接更新一個欄位要多。