Note: chemical structures and reactions require additional techniques due to the nature of domain indexes.
The General Process:
- batch insert into a staging table
- check for loading errors
- call a procedure to process
Staging Table?
Under some conditions, you may not need the staging table.
eg you know all of your data is clean and you don't need to 'reverse lookup' any values to normalize the data.
(the 'reverse lookup' should be done by step #3)
Use Transactions
If you have a Commit Frequency set and it is too low, this is guaranteed to slow things down.
In general, you should do as much as possible within a single transaction.
Also, doing everything is one transaction makes 'starting over' very easy.
The size of the transaction should be based on the archive log file sizes and the size of the data in bytes, not necessarily the number of rows.
If you are doing a lot, the DBA may need to increase the size to accommodate your requirements.
If you are doing a very very large amount, step #3 may need to incorporate DBMS_PARALLEL_EXECUTE to help out with UNDO/REDO/etc.
.. and/or DBMS_JOB. (ie run the process in the background)
Triggers
Some techniques CAN BYPASS TRIGGERS!!!
Other techniques are mutually exclusive and can not be used if the table has a trigger.
(ie 'triggers are evil')
Implementing Step #1
Step #1 is easy. Just use the SQL component with the 'Batch Size' set to a reasonable number.(* - see below)
You will need to run benchmarks for your system to find out the correct size.
I use 10k for one statement and 2k for another. 100 is WAY TOO LOW.
Sequence CACHE Size
Your sequence cache size needs to be on the same scale as your Batch Size.
eg the CACHE size should be 0.1x to 1.0x the size used for 'Batch Size'
This means, the default size of 20 is too low for a batch size of 1k (or 10k)
Keeping PLP Going Even with Bad Data
To prevent Pipeline Pilot from stoping on bad data on INSERT:
Use the "LOG ERRORS INTO" clause.
http://www.oracle-base.com/articles/10g/dml-error-logging-10gr2.php
Step #2 just does a SELECT on the
Compress Tables
Oracle has many white paper proving that a compressed table can give speed improvements.
You'll have to run your own benchmarks to prove/disprove this.
Please note that "COMPRESS FOR ALL TRANSACTIONS"/"COMPRESS FOR OLTP" requires the Advance Compression add-on for EE.
I use it because I get a 6:1 compression ratio on a few of my tables.
Direct Path Inserts
The heart and soul of step #3 should be:
INSERT .... SELECT ...
In other words, one SQL statement. (or, at least, as few as possible)
All of your 'reverse lookups' for data normalization occurs as part of the SELECT .. JOIN statements.
Parallel DML
If you can do it in parallel, do it.
(ALTER TABLE ... PARALLEL)
I've seen a 5min INSERT SELECT drop down to 6s with this.
HOWEVER there are restrictions on its use. eg NO TRIGGERS!!!
http://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm#autoId41
(hint: make FK's "initially deferrable");
Also, you will need to COMMIT the data before you SELECT from that table.
BTW - Let Oracle pick out the parallelize level.
Parallel Execution (11g)
Obviously, if the size of your data requires that you use DBMS_PARALLEL_EXECUTE, you shouldn't run parallel DML statements also.
(ie don't mix this with the above)
You use this when you need to 'chunk' a large table into smaller, more manageable size for processing.
(*) Test and restest your code.
PLP 9.0 has fixed a lot of bugs that exist in the SQL Components for 8.0/8.5.
