当导入索引组织表的数据不能排序时，那么效率是很差的。如果用一个大事务来做，很可能失败，并且导致服务器压力很大。

在oracle中还是推荐使用根据extent分块来实现大数据量的移动 http://hi.baidu.com/dbaeyes/blog/item/dcf741ada8edac004b36d674.html

 测试：

1. 使用堆表 2000w记录，耗时 3mins

2. 使用索引组织表 2000w记录，耗时 20mins

--创建存储extent的临时表
dbaeyes@ orcl>create table xf_rowid(
  2  table_name                     VARCHAR2(50),
  3  ID                             NUMBER(22),
  4  ROWID_MIN                      VARCHAR2(100),
  5  ROWID_MAX                      VARCHAR2(100),
  6  HAS_DEAL                       NUMBER(22),
  7  gmt_modified                   DATE,
  8  primary key(table_name,id));

Table created.

--将表的extent对于的最大最小rowid放到临时表中
dbaeyes@ orcl>insert into xf_rowid(table_name,id,rowid_min,rowid_max,has_deal)
  2   select 'rac_02',
  3                            rownum,
  4          DBMS_ROWID.ROWID_CREATE(1,o.data_object_id,e.RELATIVE_FNO,e.BLOCK_ID,0),
  5          DBMS_ROWID.ROWID_CREATE(1,o.data_object_id,e.RELATIVE_FNO,e.BLOCK_ID+e.BLOCKS-1,10000),
  6          0
  7          from dba_extents e,dba_objects o
  8          where e.segment_name=upper('user_cat_info')
  9          and e.owner='DBAEYES'
 10          AND o.object_name = upper('user_cat_info')
 11          AND o.owner='DBAEYES';

3446 rows created.

--统一每个extent区块的数据量
select /*+ rowid(tt) */
  COUNT(*) 
   from user_cat_info
  where rowid >= chartorowid('AAF7lUAKSAAAhIFAAA')
    and rowid <= chartorowid('AAF7lUAKSAAAhQECcQ');
                 
  COUNT(*)
----------
    173336
            
因为单行记录比较小，所以一个extetn存放而来17w多的记录。                 

--根据extent搬数据的存储过程

CREATE OR REPLACE PROCEDURE SP_XF_EXTENT_MOVE IS
  /* 
  CREATE DATE: 20110613
  USER: XUANFENG
  根据extent搬数据
  */

  V_SQLERRM VARCHAR2(200);
BEGIN
  FOR C IN (SELECT ID, ROWID_MIN, ROWID_MAX
              FROM XF_ROWID
             WHERE TABLE_NAME ='rac_02'
               AND HAS_DEAL = 0) LOOP
               
    INSERT INTO TEST_USER_CAT_IOT(ID, USER_ID, CAT_ID, GMT_MODIFIED)
      SELECT /*+ rowid(tt) */
       ID,
       USER_ID,
       CAT_ID,
       TO_DATE(GMT_MODIFIED, 'yyyy-mm-dd hh24:mi:ss')
        FROM user_cat_info
       WHERE ROWID >= CHARTOROWID(C.ROWID_MIN)
         AND ROWID <= CHARTOROWID(C.ROWID_MAX);
  
    UPDATE XF_ROWID SET HAS_DEAL = 1 WHERE ID = C.ID;
    COMMIT;
  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    V_SQLERRM := SUBSTR(SQLERRM, 1, 200);
    DBMS_OUTPUT.PUT_LINE(V_SQLERRM);
    ROLLBACK;
END SP_XF_EXTENT_MOVE;