BLK1: Introduction to Bulk Processing (PL/SQL Channel)

[@paulwhite923]

excellent, your explanations are very informative!

[@WorldEye88]

Thank you for the great details. Could you please advise on the use of compound trigger on a BULK COLLECT statement.

[@rajit1710]

Hi Steven, can you please provide the zip containing demo codes?

[@Info-Galaxy]

Hi Steven, thank you for uploading the video. I have a doubt when we process the huge no.of records using the "FORALL", the undo log grows, in such cases will it leads to performance issues when a commit is issued as it has to move lots of data from undo log to permanent storage. Another doubt is: is it suggestable to use bulk collect when I have less no.of records, let's say 1000 or 5000 or can I go directly with the FOR loop!! Please suggest

[@PracticallyPerfectPLSQL]

Sorry about the delay in answering, Prasad! There is no difference in undo log usage for FORALL vs individual row by row processing. Unless you are changing the commit processing, and that is an application / transaction issue for you to decide. You will definitely see a boost in performance with bulk collect of 1000 or 5000 rows. But a cursor for loop will give you pretty much the same boost. So, no, if your loop does not contain dml, leave it as is.

[@praveenkumar-fx5wx]

Hi Sir, Is there a way we could implement parallel processing in PL/SQL ? Like we use bulk collect and the execution is serial row by row say 100k rows. Is there a way to divide these 100k rows in batches of 10k rows and run all 10 instances of the procedure at once (parallel run) if yes can you please put a video with some example ?

[@PracticallyPerfectPLSQL]

One possibility: www.oracle.com/technetwork/testcontent/o30plsql-086044.html and asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542 But this is not for bulk collect, not for querying. Generally, you can use dbms_scheduler to kick off N jobs to run at once, or even look into dbms_aq. But it is up to you to handle any synchronization issues. Check out: www.williamrobertson.net/documents/parallel-plsql-launcher.html

[@praveenkumar-fx5wx]

Practically Perfect PL/SQL with Steven Feuerstein thank you sir !

[@praveenkumar-fx5wx]

Sir, i am sorry if I am wrong but it seems we cannot use parallel method (dbms_parallel_execute) in case we have an OUT parameter. So is there any way we can use parallelism for procedure with OUT parameters ?

[@PracticallyPerfectPLSQL]

dbms_parallel_execute is only for executing non-query DML - not PL/SQL procedures. Check out the link to William Robertson's examples.

[@abbeydudeuk]

my daughter calls you Mr Clean lol

[@PracticallyPerfectPLSQL]

I will take that as a compliment!