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.