Insert of 10 Million Queries Under 10 Minutes in Oracle

INSERT of 10 million queries under 10 minutes in Oracle?

I know others have mentioned this and you don't want to hear it but use SQL*Loader or external tables. My average load time for tables of approximately the same width is 12.57 seconds for just over 10m rows. These utilities have been explicitly designed to load data into the database quickly and are pretty good at it. This may incur some additional time penalties depending on the format of your input file, but there are quite a few options and I've rarely had to change files prior to loading.

If you're unwilling to do this then you don't have to upgrade your hardware yet; you need to remove every possible impediment to loading this quickly. To enumerate them, remove:

1. The index
2. The trigger
3. The sequence
4. The partition

With all of these you're obliging the database to perform more work and because you're doing this transactionally, you're not using the database to its full potential.

Load the data into a separate table, say ABC_LOAD. After the data has been completely loaded perform a single INSERT statement into ABC.

insert into abc
select abc_seq.nextval, a.*
  from abc_load a

When you do this (and even if you don't) ensure that the sequence cache size is correct; to quote:

When an application accesses a sequence in the sequence cache, the
sequence numbers are read quickly. However, if an application accesses
a sequence that is not in the cache, then the sequence must be read
from disk to the cache before the sequence numbers are used.

If your applications use many sequences concurrently, then your
sequence cache might not be large enough to hold all the sequences. In
this case, access to sequence numbers might often require disk reads.
For fast access to all sequences, be sure your cache has enough
entries to hold all the sequences used concurrently by your
applications.

This means that if you have 10 threads concurrently writing 500 records each using this sequence then you need a cache size of 5,000. The ALTER SEQUENCE document states how to change this:

alter sequence abc_seq cache 5000

If you follow my suggestion I'd up the cache size to something around 10.5m.

Look into using the APPEND hint (see also Oracle Base); this instructs Oracle to use a direct-path insert, which appends data directly to the end of the table rather than looking for space to put it. You won't be able to use this if your table has indexes but you could use it in ABC_LOAD

insert /*+ append */ into ABC (SSM_ID, invocation_id , calc_id, ... )
select 'c','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'a','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'b','b',NULL, 'test', 123 , 'N', 'asdf' from dual
union all select 'c','g',NULL, 'test', 123 , 'N', 'asdf' from dual

If you use the APPEND hint; I'd add TRUNCATE ABC_LOAD after you've inserted into ABC otherwise this table will grow indefinitely. This should be safe as you will have finished using the table by then.

You don't mention what version or edition or Oracle you're using. There are a number of extra little tricks you can use:

• Oracle 12c

  This version supports identity columns; you could get rid of the sequence completely.

  CREATE TABLE ABC(
     seq_no         NUMBER GENERATED AS IDENTITY (increment by 5000)
  

• Oracle 11g r2

  If you keep the trigger; you can assign the sequence value directly.

  :new.seq_no := ABC_seq.nextval;
  

• Oracle Enterprise Edition

  If you're using Oracle Enterprise you can speed up the INSERT from ABC_LOAD by using the PARALLEL hint:

  insert /*+ parallel */ into abc
  select abc_seq.nextval, a.*
    from abc_load a
  

  This can cause it's own problems (too many parallel processes etc), so test. It might help for the smaller batch inserts but it's less likely as you'll lose time computing what thread should process what.

---

tl;dr

Use the utilities that come with the database.

If you can't use them then get rid of everything that might slow the insert down and do it in bulk, 'cause that's what the database is good at.

Best way to do multi-row insert in Oracle?

This works in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

The thing to remember here is to use the from dual statement.

Oracle insert in index table:Time to load 500 thousand rows is more than inserting 16 million rows

Thank everyone for your valuable thoughts.

I found the actual problem later. Since I have doing frequent truncate and load in target table RDW10DM.INV_ITEM_LW_DM so index pages might have fragmented.
So, ran query after rebuilding indexes and got expected results.

---

Related Topics