Oracle Insert of 30 Mio records. How long does it take? How to tune it?

2013-03-22  Source: Original Site  Category:Oracle  Views:1 

Advertisement

We got the following problem.
Nearly 30 Mio values have to be calculated for later use. These values will be addressed by a key, that contains 5 number-values ( unique constraint), and a foreign key. Also we have a technical pk in the record ( not my idea ).

The record looks like this :
PK NUMBER not null,
Key1 NUMBER not null,
key2 NUMBER not null,
key3 NUMBER not null,
key4 NUMBER not null,
key5 NUMBER not null,
calculated value NUMBER(*,10) not null,
ForeigKey NUMBER not null

The records are submitted via a bulk insert from the calculating program by an OCI- implementation, 18000 records per insert .

The insert- transaction must not be logged! We can always recalculate the values!

We tried to insert with nologging, with append, etc. but we even did not find out a real good ( fast) solution.

So, here is the question.

1) How long does it take to insert these 30 Mio records? Who has experience with such a lot of records in one table and can help?
2) Which would be the fastest way to insert them ? ( how must the table be created, the index, etc. )

Thanks a lot for your help. We already tried a lot, at the time we need about 2 and a half hour to insert 20 Mio records.

Related articles
  • Oracle Insert of 30 Mio records. How long does it take? How to tune it? 2013-03-22

    We got the following problem. Nearly 30 Mio values have to be calculated for later use. These values will be addressed by a key, that contains 5 number-values ( unique constraint), and a foreign key. Also we have a technical pk in the record ( not my

  • Oracle How long does it take for the certificate arrive? I am OCA? I need it urgen 2012-02-25

    I have recently finished OCA. How long does it take for the certificate to arrive? I need it urgently. Can I pay and expedite the process of Mailing thru fedex.

  • Oracle How long does it take to run execute dbms_stats.gather_dictionary_stats 2012-12-06

    How long does it take to execute dbms_stats.gather_dictionary_stats? I need to run this prior to upgrading 10.2.0.2 aix to 11.2.0.2. I like to know how long it takes to execute because this is a very busy Production system and I do not want to take t

  • Oracle Partitioning a large network - how long does it take? How to speed it up? 2014-03-12

    Can you tell me how long does it take you to partition a very large network (about 1000000 nodes / 1000000 links)? Is there any way to speed it up? It makes me wonder.

  • Oracle How long does it take the server to recognize a newly assigned email addres 2014-09-22

    How long does it take the server to recognize a newly assigned email addres in my control panel?

  • Oracle How long does it take to get the result of hands-on-course test? 2014-10-07

    I passed 1Z0-043 and submitted hands-on-course. How long does it take to get the result? And how long does it take to get the certificate? Another question: Is there any place you can search for OCPs?, just to check those people who have fake certifi

  • how long does it take 2012-02-04

    i have just start to learn how to program i was just wondering how long does it take to learn and has anyone got any advice

  • Ubuntu How long does it take to compile... 2013-05-05

    I'm starting to compile things myself and due to initial the results with Firefox 3.5, I think will be doing this with other applications as well. Firefox takes 1 hour to compile in my machine and +2:20 (still compiling now) if I enable PGO. So I'm j

  • Ubuntu How long does it take? 2013-06-21

    I've finally got around to giving Linux a go. My first machine is running a dual boot Vista/Ubuntu 7.04. The second machine is a Compaq Proliant ML370. Its an old server and up till recently ran NT. Ive now installed Ubuntu Server 6.06 on this machin

  • How long does it take to download jquery? 2013-07-09

    Simple question, and I know the answer is not simple. How long does it take to download jquery? Does anyone have any numbers that would shed some light on this? I know that there are many variables, but does anyone have any numbers reflecting how lon

  • How long does it take to... 2014-06-11

    I am a real newbie and have not programmed in about 5 years. Unfortunatley, the chief guy doesn't like to believe how long it takes to develop a form in php while using MySQL. The other thing is I haven't programmed in any of these languages ever, so

  • OOP - How long does it take to understand it... 2014-09-17

    there, forum members... I have been studying C#.Net and OOP for about 3 months now, and while I understand the syntax of C# and what classes, objects etc. are, I still find that I am struggling to understand the Object Oriented Programming paradigm (

  • Ubuntu How long does it take to connect your bluetooth mouse? 2014-10-20

    I'm running Jaunty with bluez 4.32 on an Asus A8JS notebook. Every time I connect my Targus AMB02US bluetooth mouse, it takes about 5 seconds between pressing a mouse button and the moment the pointer starts responding to mouse movements. It doesn't

  • How long does it take for cname to propagate? 2015-01-09

    Question is in the title, how long does it take, or have I setup my cname wrong? I have created a cname (a subdomain named "files") with no trailing . to point to "mybucket.s3.amazonaws.com". Is that all I need or do I just have to wait. It's been ab

  • How long does it take to know what you're doing? 2015-01-31

    I'm just starting a website and I know nothing but NOTHING about HTML. Question 1. Where is the best source to go to for learning basic HTML? Question 2. How long does it take? I realize I can do a search and I will, but in the meantime; all suggesti

  • Ubuntu how long does it take the installer to set up encrypted lvm on a 500 gb hard drive? 2015-04-11

    About how long does it take for the 7.10 server installer to choose a guided LVM encrypted partitioning system? It sets up the swap in no time, but the / partition seems to hang at 33%. I have a 500 gb drive on an older IDE controller (even though it

  • Oracle Approx. How Long Does Install Take? 2012-08-19

    I am installing Oracle 8.1.7 Enterprise Edition using universal install. How long does this install take? It has been stucked on 85% initializing database for about 2hrs. I received an error that the listener failed earlier, but received a comment th

  • How long does it take to uninstall Oracle Client & Dev Suite? 2012-11-28

    I've been uninstalling OraClient10g_home1 , OraClient10g_home2 and DevSuiteHome and it has taken more than 3hrs. and showing status as 99%. Any idea, how long will it take?

  • Ubuntu how long does it take to zero a 1TB drive? 2012-03-29

    just wondering how long it should take to zero a 1TB sata hard disk, using dd, full command ive used is sudo dd if=/dev/zero of=/dev/sdb been going for nearly 14 hours now, and its not a particuarly verbose command, anyone shed some light on how long

  • how long does it take to learn c++? 2012-11-14

    just a general question. i know its relative but about how long would it take to learn c++ to the level where you can get an entry type job. maybe you can tell me how long it took you or just anything. thanks. edit: i also want to minor i have 3 choi