慎用 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