Data loading in Oracle

Question

Hi,

I am facing problem in loading data. I have to copy 800,000 rows from one table to another in Oracle database.

I tried for 10,000 rows first but the time it took is not satisfactory. I tried using the "BULK COLLECT" and "INSERT INTO SELECT" clause but for both the cases response time is around 35 minutes. This is not the desired response I'm looking for.

Can anyone suggest?

Thanks Anirban

Answer 1

Anirban,

Using an "INSERT INTO SELECT" is the fastest way to populate your table. You may want to extend it with one or two of these hints:

• APPEND: to use direct path loading, circumventing the buffer cache
• PARALLEL: to use parallel processing if your system has multiple cpu's and this is a one-time operation or an operation that takes place at a time when it doesn't matter that one "selfish" process consumes more resources.

Just using the append hint on my laptop copies 800,000 very small rows below 5 seconds:

SQL> create table one_table (id,name)
  2  as
  3   select level, 'name' || to_char(level)
  4     from dual
  5  connect by level <= 800000
  6  /

Tabel is aangemaakt.

SQL> create table another_table as select * from one_table where 1=0
  2  /

Tabel is aangemaakt.

SQL> select count(*) from another_table
  2  /

  COUNT(*)
----------
         0

1 rij is geselecteerd.

SQL> set timing on
SQL> insert /*+ append */ into another_table select * from one_table
  2  /

800000 rijen zijn aangemaakt.

Verstreken: 00:00:04.76

You mention that this operation takes 35 minutes in your case. Can you post some more details, so we can see what exactly is taking 35 minutes?

Regards, Rob.

Answer 2

I would agree with Rob. Insert into () select is the fastest way to do this.

What exactly do you need to do? If you're trying to do a table rename by copying to a new table and then deleting the old, you might be better off doing a table rename:

alter table
   table
rename to
   someothertable;

Answer 3

INSERT INTO SELECT is the fastest way to do it.

If possible/necessary, disable all indexes on the target table first.

If you have no existing data in the target table, you can also try CREATE AS SELECT.

Answer 4

As with the above, I would recommend the Insert INTO ... AS select .... or CREATE TABLE ... AS SELECT ... as the fastest way to copy a large volume of data between two tables.

You want to look up the direct-load insert in your oracle documentation. This adds two items to your statements: parallel and nologging. Repeat the tests but do the following:

CREATE TABLE Table2 AS SELECT * FROM Table1 where 1=2;
ALTER TABLE Table2 NOLOGGING;
ALTER TABLE TABLE2 PARALLEL (10);
ALTER TABLE TABLE1 PARALLEL (10);
ALTER SESSION ENABLE PARALLEL DML;
INSERT INTO TABLE2 SELECT * FROM Table 1;
COMMIT;
ALTER TABLE 2 LOGGING:

This turns off the rollback logging for inserts into the table. If the system crashes, there's not recovery and you can't do a rollback on the transaction. The PARALLEL uses N worker thread to copy the data in blocks. You'll have to experiment with the number of parallel worker threads to get best results on your system.

Answer 5

Is the table you are copying to the same structure as the other table? Does it have data or are you creating a new one? Can you use exp/imp? Exp can be give a query to limit what it exports and then imported into the db. What is the total size of the table you are copying from? If you are copying most of the data from one table to a second, can you instead copy the full table using exp/imp and then remove the unwanted rows which would be less than copying.

Answer 6

try to drop all indexes/constraints on your destination table and then re-create them after data load.

use /*+NOLOGGING*/ hint in case you use NOARCHIVELOG mode, or consider to do the backup right after the operation.