慎用 skip_unused_indexes

 

在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博客 ” ，链接：http://blog.itpub.net/9036/viewspace-167417/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9036/viewspace-167417/