批量导入某大张表数据的时候的最佳实践

发布时间：2020-08-11 18:45:50 作者：lusklusklusk
来源：ITPUB博客阅读：153

批量导入某大张表数据的时候的最佳实践：
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

批量导入某大张表数据的时候的最佳实践

相关阅读