批量匯入某大張表資料的時候的最佳實踐

lusklusklusk發表於2017-07-19
批量匯入某大張表資料的時候的最佳實踐：
1、把表上所有的索引都設定為unusable: alter index <index name> unusable;
2、做批量匯入
3、rebuild索引：alter index <index name> rebuild parallel nologging;
 




演示如下
SQL> create table emp as select * from employees;
Table created.

SQL> create index idx_emp_job on emp(job_id);
Index created.

SQL> select bytes from user_segments where segment_name='IDX_EMP_JOB';
     BYTES
----------
     65536

SQL> alter index idx_emp_job unusable;
Index altered.

SQL> insert into emp select * from emp;
107 rows created.

SQL> /
214 rows created.

SQL> /
428 rows created.

SQL> /
856 rows created.

SQL> /
1712 rows created.

SQL> /
3424 rows created.

SQL> /
6848 rows created.

SQL> /
13696 rows created.

SQL> /
27392 rows created.

SQL> /
54784 rows created.

SQL>
SQL>
SQL>
SQL> /
109568 rows created.

SQL> commit;
Commit complete.

SQL> select bytes from user_segments where segment_name='IDX_EMP_JOB';
no rows selected

SQL> select status from user_objects where object_name='IDX_EMP_JOB';
STATUS
-------
VALID

SQL> alter index IDX_EMP_JOB rebuild parallel 4 nologging;
Index altered.

SQL> select bytes from user_segments where segment_name='IDX_EMP_JOB';
     BYTES
----------
   5373952

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2142268/，如需轉載，請註明出處，否則將追究法律責任。

相關文章