关于Oracle数据库中行迁移/行链接的问题

        三、行迁移和行链接的清除

        由于对于行链接来说只能增大db_block_size来清除，而db_block_size在创建了数据库后又是不能改变了的，所以这里对行链接的清除不做过多的叙述了，主要是针对行迁移来谈谈在实际的生产系统中如何去清除。

        对于行迁移的清除，一般来说分为两个步骤：第一步，控制住行迁移的增长，使其不在增多；第二步，清除掉以前存在的行迁移。

        众所周知，行迁移产生的主要原因是因为表上的pctfree参数设置过小导致的，而要实现第一步控制住行迁移的增长，就必须设置好一个正确合适的pctfree参数，否则即使清除了当前的行迁移后马上又会产生很多新的行迁移。当然，这个参数也不是越大越好的，如果pctfree设置的过大，会导致数据块的利用率低，造成空间的大量浪费，因此必须设置一个合理的pctfree参数。如何去确定一个表上合理的pctfree参数呢，一般来说有两种方法。

        第一种是定量的的设定方法，就是利用公式来设定pctfree的大小。先使用ANALYZE TABLE table_name ESTIMATE STATISTICS命令来分析要修改pctfree的表,然后查看user_tables中的AVG_ROW_LEN列值，得到一个平均行长AVG_ROW_LEN1，然后大量的对表操作之后，再次使用上述命令分析表，得到第二个平均行长AVG_ROW_LEN2，然后运用公式100 * （AVG_ROW_LEN2-AVG_ROW_LEN1）/（AVG_ROW_LEN2-AVG_ROW_LEN1 + 原始的AVG_ROW_LEN）得出的结果就是定量计算出来的一个合适的pctfree的值。这种方法因为是定量计算出来的，可能不一定会很准确，而且因为要分析表，所以对于使用RBO执行计划的系统不是很适用。例如：avg_row_len_1 = 60，avg_row_len_2 = 70，则平均修改量为 10，PCTFREE 应调整为 100 * 10 /（10 + 60）= 16.7% 。

        第二种是差分微调的方法，先查询到当前表的pctfree的值，然后监控和调整pctfree参数，每次增加一点pctfree的大小，每次增加的比例不要超过5个百分点，然后使用ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS INTO chained_rows命令分析每次所有的行迁移和行链接的增长情况，对于不同的表采取不同的增长比例，对于行迁移增长的比较快的表pctfree值就增加的多点，对于增长慢的表就增加的少点，直到表的行迁移基本保持不增长了为止。但是注意不要把pctfree调的过大，一般在40%以下就可以了，否则会造成空间的很大浪费和增加数据库访问的IO。

        使用上述的方法控制住了当前表的行迁移的增长之后，就可以开始清除之前表上存在的行迁移了。是否清除掉行迁移，关系到系统的性能是否能够有很大的提高。因此，对于以前存在的行迁移是一定而且必须要清除掉的。清除掉已经存在的行迁移有很多方法，但是并不是所有的方法都能适用所有的情况，例如表中的记录数多少，表上的关联多少、表上行迁移的数量多少等等这些因素都会是成为制约你使用什么方法清除的条件，因此，根据表的特点和具体情况的不同我们应该采用不同的方法去清除行迁移。下面我将逐一介绍各种清除行迁移的方法以及它们各自适用的不同情况。

        方法一：传统的清除行迁移的方法

        具体步骤如下：

        1． 执行$ORACLE_HOME/rdbms/admin目录下的utlchain.sql脚本创建chained_rows表。

        @$ORACLE_HOME/rdbms/admin/utlchain.sql

        2． 将存在有行迁移的表（用table_name代替）中的产生行迁移的行的rowid放入到chained_rows表中。

        ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows;

        3． 将表中的行迁移的row id放入临时表中保存。

        CREATE TABLE table_name_temp AS

        SELECT * FROM table_name

        WHERE rowid IN

        (SELECT head_rowid FROM chained_rows

        WHERE table_name = 'table_name');

        4． 删除原来表中存在的行迁移的记录行。

        DELETE table_name

        WHERE rowid IN

        (SELECT head_rowid

        FROM chained_rows

        WHERE table_name = 'table_name');

        5． 从临时表中取出并重新插入那些被删除了的数据到原来的表中，并删除临时表。

        INSERT INTO table_name SELECT * FROM table_name_temp;

        DROP TABLE table_name_temp;

 

        对于这种传统的清除RM的方法，优点是执行起来过程比较简单，容易实现。但是这种算法的缺陷是没有考虑到表关联的情况，在大多数数据库中很多表都是和别的表之间有表关联的，有外键的限制，这样就造成在步骤3中根本无法delete掉存在有行迁移的记录行，所以这种方法能够适用的表的范围是有限的，只能适用于表上无任何外键关联的表。由于这种方法在插入和删除数据的时候都没有disable掉索引，这样导致主要消耗时间是在删除和插入时维持索引树的均衡上了，这个对于如果记录数不多的情况时间上还比较短，但是如果对于记录数很多的表这个所消耗的时间就不是能够接受的了。显然，这种方法在处理大数据量的表的时候显然是不可取的。

        以下是一个具体在生产数据库上清除行迁移的例子，在这之前已经调整过表的pctfree参数至一个合适的值了：

        SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql

        Table created.

        SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;

        Table analyzed.

        SQL>SELECT count(*) from chained_rows；

        TABLE_NAME COUNT(*)

        ------------------------------ ----------

        CUSTOMER 21306

        1 rows selected.

        查看在CUSTOMER表上存在的限制:

        SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='CUSTOMER';

        CONSTRAINT_NAME C TABLE_NAME

        ------------------------------ - ------------------------------

        PK_CUSTOMER1 P CUSTOMER

        SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_CUSTOMER1';

        no rows selected

        SQL> CREATE TABLE CUSTOMER_temp AS

        SELECT * FROM CUSTOMER WHERE rowid IN

        (SELECT head_rowid FROM chained_rows

        WHERE table_name = 'CUSTOMER');

        Table created.

        SQL>select count(*) from CUSTOMER;

        COUNT(*)

        ----------

        338299

        SQL> DELETE CUSTOMER WHERE rowid IN

        (SELECT head_rowid

        FROM chained_rows

        WHERE table_name = 'CUSTOMER');

        21306 rows deleted.

        SQL> INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_temp;

        21306 rows created.

        SQL> DROP TABLE CUSTOMER_temp;

        Table dropped.

        SQL> commit;

        Commit complete.

        SQL> select count(*) from CUSTOMER;

        COUNT(*)

        ----------

        338299

        SQL> truncate table chained_rows;

        Table truncated.

        SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;

        Table analyzed.

        SQL> select count(*) from chained_rows;

        COUNT(*)

        ----------

        0

        以上整个清除两万多行的行迁移过程在三分钟左右，而且全部都在联机的状态下完成，基本上不会对业务有什么影响，唯一就是在要清除行迁移的表上不能有对外键的限制，否则就不能采用这个方法去清除了。

        方法二：改进了的传统清除行迁移的方法

        1． 执行$ORACLE_HOME/rdbms/admin目录下的utlchain.sql脚本创建chained_rows表。

        2． 禁用所有其它表上关联到此表上的所有限制。

        3． 将表中的行迁移的row id放入临时表中保存。

        4． 删除原来表中存在的行迁移的记录行。

        5． 从临时表中取出并重新插入那些被删除了的数据到原来的表中，并删除临时表。

        6． 启用所有其它表上关联到此表上的所有限制。

        这种算法是对传统算法的一种改进，对于使用这种算法来清除行迁移，考虑到了表之间的关联，还可以灵活的利用的TOAD工具生成的表关联信息，是一种比较适合于清除行迁移的一种方法。但是因为使用这种方法后来需要重建索引，如果记录数很大，比如说上千万条以上的记录的表，就不是很合适了，因为这个重建索引的时间会很长，是线性时间复杂度的，而重建索引是会导致索引所在的表被锁定的，从而导致插入不了新的记录，重建索引的时间太长导致记录长时间插入不了是会严重影响应用的，甚至导致数据的丢失，因此这个是使用这个方法前必须要考虑到的一个重要因素；对于8i以上的版本可以使用online的方法来重建索引，这样不会导致锁表，但是会有额外更多的开销，时间会很长。再者，因为这种方法在插入记录和删除记录都是带着索引的，如果表上的行迁移比较多，这样耗时间会比较长，而且占用资源也会比较大，因此只适用于表上行迁移存在的比较少的表。总的来说，这种方法对于表记录太多或者是表上的行迁移太多的情况都不是很适用，比较适合表记录少和表上行迁移都不太多的情况。

        以下是一个具体在生产数据库上清除行迁移的例子，在这之前已经调整过表的pctfree参数至一个合适的值了：

        SQL>select index_name,index_type,table_name from user_indexes where table_name='TERMINAL';

        INDEX_NAME INDEX_TYPE TABLE_NAME

        -----------------------------------------------------------------

        INDEX_TERMINAL_TERMINALCODE NORMAL TERMINAL

        I_TERMINAL_ID_TYPE NORMAL TERMINAL

        I_TERMINAL_OT_OID NORMAL TERMINAL

        PK_TERMINAL_ID NORMAL TERMINAL

        UI_TERMINAL_GOODIS_SSN NORMAL TERMINAL

        SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_TERMINAL_ID';

        CONSTRAINT_NAME C TABLE_NAME

        ------------------------------ - ------------------------------

        SYS_C003200 R CONN

        SQL>alter table CONN disable constraint SYS_C003200;

        Table altered.

        SQL>CREATE TABLE TERMINAL_temp AS

        SELECT * FROM TERMINAL

        WHERE rowid IN

        (SELECT head_rowid FROM chained_rows

        WHERE table_name = 'TERMINAL');

        Table created.

        SQL>select count(*) from TERMINAL_temp;

        COUNT(*)

        ----------

        8302

        SQL>DELETE TERMINAL

        WHERE rowid IN

        (SELECT head_rowid

        FROM chained_rows

        WHERE table_name = 'TERMINAL');

        8302 rows deleted.

        SQL>INSERT INTO TERMINAL SELECT * FROM TERMINAL_temp;

        8302 rows created.

        SQL>alter table CONN disable constraint SYS_C003200;

        Table altered.

        SQL>select count(*) from terminal;

        COUNT(*)

        ----------

        647799

        SQL>truncate table chained_rows;

        Table truncated.

        SQL>ANALYZE TABLE TERMINAL LIST CHAINED ROWS INTO chained_rows;

        Table analyzed.

        SQL>select count(*) from chained_rows;

        COUNT(*)

        ----------

        0

        从上面过程中可以看出，对TERMINAL这张表的行迁移清除耗时总共不到五分钟的时间，总体来说还是比较快的。从我在生产数据库中清除行迁移的经验来说，这种方法基本适用于大部分存在有行迁移的表。

        方法三：使用TOAD工具清除行迁移的方法

        1． 备份要清除RM的表。

        RENAME table_name TO table_name_temp;

        2． Drop 所有其它表上关联到table_name的外键限制。

        SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME in (SELECT CONSTRAINT_NAME from USER_CONSTRAINTS where TABLE_NAME='table_name' AND CONSTRAINT_TYPE=’P’);

        ALTER TABLE table_name DROP CONSTRAINT XXXX;（XXXX为上述的查询结果）

        3． 重建1中被rename的表。

        CREATE TABLE table_name AS SELECT * FROM table_name_temp WHERE 0 = 1;

        4． 重建表中原来的数据。

        INSERT /*+ APPEND */ INTO table_name SELECT * FROM table_name_temp;

        5． 删除在table_name_temp上的索引和关联其他表的外键。

        6． 在table_name上建立和原来一样的索引、主键和所有的外键限制。

        7． 重新编译相关的存储过程、函数和包。

        8． 删除表table_name_temp。

        对于使用这种方法来清除行迁移，全部的代码都是可以由TOAD工具来生成的。由于此方法把表上的关联考虑进去了，也是一种比较的全面的考虑的一种清除方法，而且在清除过程中重建了表和索引，对于数据库的存储和性能上都有提高。因为这种方法一开始是rename表为临时表，然后重建一个新表出来的，因此需要两倍的表的空间，因此在操作之前一定要检查要清除的表所在的表空间的free空间是否足够；但是也有一定的缺陷，因为在新表中重新插入原来的数据后需要重建索引和限制，因此在时间和磁盘的空间上都有比较大的开销，而且对于前台的应用可能会有一段时间的中断，当然，这个中断时间就主要是消耗在重建索引和重建限制上了，而时间的长短跟需要重建索引和限制的多少以及表的记录多少等等因素都有关系。使用这种方法对于7*24小时要求的系统上清除行迁移不是很合适，因为使用这种方法会导致系统可能有一段时间的停机，如果系统的实时性比较高，这种方法就不是很适用了。

查看本文来源