Quickly insert 24 million rows into Oracle

Question

Which is the best way to insert 24 million rows into a table in Oracle (from the same database table)?

Target table has unique and not null indexes and it is partitioned.

Answer 1

The fastest way to insert a large number of rows is to use parallelism and direct-path writes:

alter session enable parallel dml;

insert /*+ append parallel */ into test1
select -a, b, c from test;

commit;

(You might be able to simply use a negative sign to avoid primary key violations. IDs are almost always positive, but there's usually not a rule preventing them from being negative.)

This code isn't merely a FAST=TRUE flag. There are many important caveats you must understand first.

Parallelism requires Enterprise Edition (the most expensive version), not-terrible hardware (more than a single core and a single disk), and a sane configuration (if a DBA set the parameter parallel_max_servers = 1 then there will be no parallel threads available). And parallelism can often "steal" from other processes - you might need 10x the system resources for only 5x performance improvement. And getting the right degree of parallelism can be tricky.

Direct-path writes directly to the data files and and avoid writing REDO and UNDO (extra data used for recovery and consistency). The trade-off here is that the statement will lock the entire table - nobody else will be able to write to it at the same time. And the table will not be recoverable until the next full backup. And there are lots of things that can block direct-path writes, like TRIGGERS, certain foreign key relationships, etc.

Check the execution plan carefully to ensure you're getting all the features. You want to see "LOAD AS SELECT" instead of "LOAD CONVENTIONAL" to ensure you're getting direct-path writes. And you'll want to see "PX" operations to ensure you're getting parallelism. You'll want a "PX" above both the reading and the writing part of the operation. Check the Note section carefully, it may tell you why you're not getting what you ask for.

explain plan for
insert /*+ append parallel */ into test1
select * from test2;

select * from table(dbms_xplan.display);


Plan hash value: 1209398148

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |     1 |    39 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |     1 |    39 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| TEST1    |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |     1 |    39 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |          |     1 |    39 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | TEST1    |     1 |    39 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

This can be a lot of work! You might spend hours optimizing a single INSERT, and you might need to read the Oracle documentation to solve some problems. But if you're patient and have the right configuration, it's possible to see huge improvements in INSERT performance.