在IMP 一个大表数据时，曾经以为 可以这样做：
1. import 空表，带索引
2. 将索引置为unusable
3. 使用SKIP_UNUSABLE_INDEXES = Y 参数导入数据
4. Rebuild nologging 索引
个人以为，比单纯的IMP 数据，第四步由于使用NOLOGGING数据，速度肯定会快点。
于是编写两个的比较脚本：
1. 传统的IMP
首先 truncate table outp_bill_items reuse storage ;
time /t
imp system/manager file=outp_bill_items.dmp full=y ignore=y log = imp.log feedback = 100000
time /t
整个时间为14:47 到14:53 不过7分钟左右
2. NOLOGGING 方式：
首先 truncate table outp_bill_items reuse storage ;
time/t
imp system/manager file=outp_bill_items.dmp full=y ignore=y log = imp.log feedback = 100000 rows=n trigger=n
sqlplus outpbill/outpbill @disableidx.sql
imp system/manager file=outp_bill_items.dmp full=y ignore=y log = imp.log SKIP_UNUSABLE_INDEXES = Y
sqlplus outpbill/outpbill @enableidx.sql
time/t
整个时间为15:59 到16:23 都快半小时了
其中脚本disableidx.sql
BEGIN
FOR cur_index IN (SELECT index_name
FROM user_indexes
WHERE table_name = 'OUTP_BILL_ITEMS'
AND index_name NOT IN (
SELECT constraint_name
FROM user_constraints
WHERE table_name =
'OUTP_BILL_ITEMS'))
LOOP
EXECUTE IMMEDIATE ' alter index ' || cur_index.index_name
|| ' unusable ';
END LOOP;
END;
/
BEGIN
FOR cur_index IN (SELECT trigger_name
FROM user_triggers
WHERE table_name = 'OUTP_BILL_ITEMS')
LOOP
EXECUTE IMMEDIATE ' alter trigger ' || cur_index.trigger_name
|| ' disable ';
END LOOP;
END;
/
exit
脚本enableidx.sql
alter session set sort_area_size = 120000000 ;
BEGIN
FOR cur_index IN (SELECT index_name
FROM user_indexes
WHERE table_name = 'OUTP_BILL_ITEMS'
AND index_name NOT IN (
SELECT constraint_name
FROM user_constraints
WHERE table_name =
'OUTP_BILL_ITEMS'))
LOOP
EXECUTE IMMEDIATE ' alter index '
|| cur_index.index_name
|| ' rebuild nologging ';
END LOOP;
END;
/
BEGIN
FOR cur_index IN (SELECT trigger_name
FROM user_triggers
WHERE table_name = 'OUTP_BILL_ITEMS')
LOOP
EXECUTE IMMEDIATE ' alter trigger ' || cur_index.trigger_name
|| ' enable ';
END LOOP;
END;
/
exit
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9036/viewspace-167417/ ，如需转载，请注明出处，否则将追究法律责任。
