修改字段数据类型的方法

在日常维护的过程中可能碰到这样的问题，需要修改表的字段类型。

 

 

对于绝大部分正常的情况，都是将表的字段类型的长度扩大，但是有的时候是需要缩小表的字段长度的，甚至有的时候是要修改表的数据类型的。

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都比直接更新一个字段要多。

 

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

转载于:http://blog.itpub.net/4227/viewspace-260077/