How to insert large amounts of data in Oracle

1. Use of high-speed storage devices to improve reading and writing capabilities, such as: EMC and NetApp,

2. If there is no data in the TAB1 table

DROP TABLE TAB1;

CREATE TABLE TAB1 as SELECT * from TAB2;

And then when you create the index

3. Reduce operation time with hint tips

INSERT/*+append*/into Tab1

SELECT * from TAB2;

4. Use not to write log and use hint hint to reduce data operation time.

The recommended scenario is to modify the table first to not write the log:

Sql> ALTER TABLE table_name nologging;

Insert data:

INSERT/*+append*/into Tab1

SELECT * from TAB2;

After inserting the data, modify the table write log:

Sql> ALTER TABLE table_name LOGGING;

The difference here is that it takes a long time to insert the data and write the log, especially the large amount of data inserts.

5. Processing large amounts of data with Exp/imp

(1) Renaming the current two tables separately

ALTER TABLE TAB1 Rename to Tab11;

ALTER TABLE TAB2 Rename to Tab1;

(2) Export the tab2 before renaming

Exp user/pwd@ ... file= log= ... tables= (TAB1)

(3) Change the name back

ALTER TABLE TAB1 Rename to tab2;

ALTER TABLE TAB11 Rename to Tab1;

(4) Import data

Imp user/pwd@ ... file= log= ... fromuser=user touser=user tables= (TAB1)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/