I'm currently involved in an ETL-type project that involves loading data into an Oracle-based 3 tier application. The application in question has some unusual characteristics in that a lot (but by no means all) of the business logic is splurged into the database and is executed by means of triggers (utterly horrific I know, but changing this is not possible).
When loading data into the target schema, it's generally accepted that doing bulk operations (BULK COLLECT, FORALL, etc) is vastly quicker than performing a more traditional FOR...NEXT cursor loop type exercise due to the reduction in context switches. Would it be correct to say that there would be 2 context switches per iteration, from PL/SQL to SQL and back again? I.e.:
FOR...LOOP
<PL/SQL code>
<<context switch>>
INSERT INTO...
<<context switch>>
<PL/SQL code>
END LOOP
Anyway, my question is this: if the tables being inserted into contain triggers that run PL/SQL procedures/functions, what sort of performance increase could be expected by implementing bulk operations compared to the cursor loop approach? When the FORALL inserts a bunch of records into the table, would there still be a context switch for every row due to the existence of the trigger? Would there still be two context switches per row (i.e. from SQL to PL/SQL and back again)?
Hope that makes sense...!