【轉】修改表的欄位資料型別的方法

leonarding發表於2013-08-22

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


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

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的包,複雜度比第一種情況高很多;第二類方法還需要考慮很多的東西,由於表被替換掉了,索引、約束、觸發器、過程、許可權、統計資訊等等這些都是需要考慮和處理的,否則就很容易造成問題;還有就是這類操作和第一種操作相比,會產生更多的REDOUNDO資訊;更重要的一點是,這種方式有一定的限制條件,在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_IDNULL出現在中間的位置,而使得每條記錄的長度增加了1,而再執行第二次操作的時候,這個長度1的代價又被消除掉了,因此這種方式更新基本上不會產生行遷移。由於列的順序沒有發生變化,也不會對應用構成很大的影響。而且原始列沒有被刪除,索引、約束等都不需要改變。

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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26686207/viewspace-769060/,如需轉載,請註明出處,否則將追究法律責任。

相關文章