Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.