Best practices when importing data from a large table in bulk
Best practices when bulk importing data from a large table:
1. Set all the indexes on the table to unusable: alter index unusable
2. Do batch import
3. Rebuild index: alter index rebuild parallel nologging
The demonstration is as follows
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
