oracle - Data filtering performance:sqlldr+triggers or sqlldr+stored procedure? -

i have load millions of records on db, said records need further processing according of columns values. in particular if row satisfies condition save row in table b, otherwise save in table c.

according documentation sqlldr direct path fast, anyway loading rows not trigger triggers. so, came 2 solutions:

solution 1:

using sql loader direct path=true load data in table a. calling stored procedure perform actual filtering. not sure seem in case oracle performs multithreading behind scenes.

solution 2:

using sql loader direct path=false triggers activated after insert on table a.in case, performance sake, need explicitely perform multithreading splitting data file in multiple files , calling sql loader multiple times (by way have no idea on how on bash script...)

which 1 leads better performances?and there performance difference between two?

in situations data loading requires processing/cleansing before adding core data strong preference load first staging table, , use fastest available method, i.e. sqlldr direct path. gets data loading done , dusted quickly. there implications direct paths regards logging , recovery, best keep activity small window possible.

the staging data can processed greater flexibility using stored procedures. here can split records batches , have stored procedure process particular batch via parameter, , have more 1 stored proc running on discrete batches in parallel (assuming each record can processed discretely). add in things process status (ready, checking, failed, completed) control process , have flexible , more manageable. last project did had loaded 100s millions of rows files, , processed them in batches on several nights during quiet batch periods.

coding wise it's more work, messing around files , bad records , reload , avoiding duplicates via sqlldr more cumbersome table data.