This forum is currently locked. You can't register or post questions at this time. (read more)

BULK COLLECT and FORALL usage

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

BULK COLLECT and FORALL usage

Postby ponic » Mon Sep 09, 2013 7:11 pm

Hi Tim,

I would like to know whether the following code does a sql to plsql engine switch as I am not using FORALL when BULK COLLECT is used. Is it a must that FORALL must be used in order to avoid a plsql to sql engine switch?

And are there any better ways of writing the following?

Code: Select all

SELECT    product_no
     BULK   COLLECT INTO p_prod
     FROM   products;

   FOR outer_indx IN 1 .. p_prod.COUNT
   LOOP
      SELECT   COUNT (*)
        INTO   p_count
        FROM   products pr
       WHERE       product_date_arrival_date IS NOT NULL
               AND product_no = p_prod (outer_indx);

      IF p_count > 2
      THEN
         SELECT   vendor_id,
                  product_id
           BULK   COLLECT INTO
                  p_vendor_id,
                  p_product_id
           FROM   products pr
          WHERE       product_no =
                         p_prod (outer_indx);

         FOR prod_indx IN 1 .. p_vendor_id.COUNT
         LOOP
               email_msg :=
                     'Product '
                  || p_prod (outer_indx)
                  || ' has been delivered';
         
               INSERT INTO   product_delivery
                    VALUES   (
                                p_prod (outer_indx),
                                p_vendor_id (prod_indx),
                                p_product_id (prod_indx),
                                email_msg,
                             );

               duplicate_removals (p_prod (outer_indx),
                                      p_vendor_id (prod_indx),
                                      p_product_id (prod_indx)
                                     );
         END LOOP;
      END IF;


Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: BULK COLLECT and FORALL usage

Postby Tim... » Mon Sep 09, 2013 7:43 pm

Hi.

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.

Thoughts:

- 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

 SELECT product_no
  BULK   COLLECT INTO p_prod
  FROM   (SELECT p1.product_no,
                 (SELECT COUNT(*)
                  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
  LOOP
         SELECT   vendor_id,
                  product_id
           BULK   COLLECT INTO
                  p_vendor_id,
                  p_product_id
           FROM   products pr
          WHERE       product_no =
                         p_prod (outer_indx);

         FOR prod_indx IN 1 .. p_vendor_id.COUNT
         LOOP
               email_msg :=
                     'Product '
                  || p_prod (outer_indx)
                  || ' has been delivered';
         
               INSERT INTO   product_delivery
                    VALUES   (
                                p_prod (outer_indx),
                                p_vendor_id (prod_indx),
                                p_product_id (prod_indx),
                                email_msg,
                             );

               duplicate_removals (p_prod (outer_indx),
                                      p_vendor_id (prod_indx),
                                      p_product_id (prod_indx)
                                     );
         END LOOP;
  END LOOP;


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...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests