Well, whenever you see an SQL statement in PL/SQL, you know you have a context switch. When you have that SQL in a loop, you know you will have many context switches. So yes, your code will result in many context switches.
- When you do a BULK COLLECT INTO, you are holding the data in session memory. I don't know how many products you have, but if it were millions, you might not be happy about the amount of memory this will take up. When you are dealing with large tables, you should use the limit clause to minimize the amount of session memory the collection is hogging. This concept is explained here.http://www.oracle-base.com/articles/9i/ ... lk_collect
- It looks like you are taking 3 queries to decide how what data to process. If you take some time and think about this you can probably do the same decision in a single query. This will save you having extra queries in loops adding to the context switches. You can easily drop out one without even thinking.
Code: Select all
BULK COLLECT INTO p_prod
FROM (SELECT p1.product_no,
FROM products p2
WHERE p2.product_date_arrival_date IS NOT NULL
AND p2.product_no = p1.product_no) AS counter
FROM products p1)
WHERE counter > 2;
FOR outer_indx IN 1 .. p_prod.COUNT
BULK COLLECT INTO
FROM products pr
WHERE product_no =
FOR prod_indx IN 1 .. p_vendor_id.COUNT
|| p_prod (outer_indx)
|| ' has been delivered';
INSERT INTO product_delivery
duplicate_removals (p_prod (outer_indx),
You only want to process them if they have more than 2, so why not just query those that have more than 2?
Like I said, if you spent some time thinking, you could probably do it all from a single driving query.
- At minimum, your inserts should be switched to put data into a collection, then use FORALL to bulk bind them into the database. Once again, depending on the amount of data expected, this may be build all the data into the collection then insert it on one go, or it may build chunks of rows and insert them when the size gets above a certain size.
I'm not going to rewrite the whole code for you, but I've told you what you need to look at. Suffice to say, the code is currently very inefficient...