ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【转】修改表的字段数据类型的方法

【转】修改表的字段数据类型的方法

原创 Linux操作系统 作者:leonarding 时间:2013-08-22 16:17:44 0 删除 编辑
链接:http://yangtingkun.itpub.net/post/468/461123

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


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

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
刘盛,网名leonarding,the first ACEA in china, Oracle OCM10g&11g RHCE, ACOUG Core Member Blogger, DATAGURU Oracle数据库版主, ITPUB HADOOP版版主, DB2中国专家组成员, 专注于&Oracle&Hadoop&Nosql&SAS, 提倡“分享技术~成就梦想”, 新浪微博:@itpub_leonarding QQ:40354446

注册时间:2012-02-10

  • 博文量
    238
  • 访问量
    3245175