当导入索引组织表的数据不能排序时，那么效率是很差的。如果用一个大事务来做，很可能失败，并且导致服务器压力很大。
在oracle中还是推荐使用根据extent分块来实现大数据量的移动 http://hi.baidu.com/dbaeyes/blog/item/dcf741ada8edac004b36d674.html
测试：
1. 使用堆表 2000w记录，耗时 3mins
2. 使用索引组织表 2000w记录，耗时 20mins
--创建存储extent的临时表
[cc lang='sql']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.[/cc]
--将表的extent对于的最大最小rowid放到临时表中
[cc lang='sql']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.[/cc]
--统一每个extent区块的数据量
[cc lang='sql']select /*+ rowid(tt) */
COUNT(*)
from user_cat_info
where rowid >= chartorowid('AAF7lUAKSAAAhIFAAA')
and rowid <= chartorowid('AAF7lUAKSAAAhQECcQ');
COUNT(*)
----------
173336[/cc]
因为单行记录比较小，所以一个extetn存放而来17w多的记录。
--根据extent搬数据的存储过程
[cc lang='sql']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;[/cc]
This work is licensed under a CC A-S 4.0 International License.