Re: MUTIPLE CURSORS IN PL/SQL

From: Damon Bowman <dbowman_2_at_ix.netcom.com>
Date: 1998/04/04
Message-ID: <6g6ve4$1j5_at_dfw-ixnews8.ix.netcom.com>#1/1


hard to help without the cursor code...

You may want to try using nested fetches instead of using multiple table joins.

Example:
suppose you want to fetch rows from the shipments table for certain orders. You have to join the orders table, the order_details table, and the shipments table. The shipments table has many times more rows than either of the other two tables.

If you join the tables, your cursor will look like this:

CURSOR join_cur IS

   SELECT sh.shipment_id
   FROM shipments sh,

          order_detail det,
          orders ord
   WHERE  ord.ord_id = det.ord_id
   AND    det.det_id = sh.det_id

   AND ord.ord_date > '01/01/1998'
   AND det.quantity > 10
   AND sh.shipment_status = 'CLOSED';

instead of joining the three tables, set up three separate cursors, one for each table, referencing the previous cursor values. Nest the fetches like this:

DECLARE
   CURSOR ord_cur IS

      SELECT ord_id
      FROM   orders
      WHERE  ord_date > '01/01/1998';

   ord_rec ord_cur%ROWTYPE;
   CURSOR det_cur IS
      SELECT det_id
      FROM   order_details
      WHERE  ord_id = ord_rec.ord_id
      AND    quantity > 10;

   det_rec det_cur%ROWTYPE;
   CURSOR ship_cur IS
      SELECT shipment_id
      FROM   shipments
      WHERE  det_id = det_rec.det_id
      AND    shipment_status = 'CLOSED';
BEGIN
   OPEN ord_cur;
   LOOP
      FETCH ord_cur INTO ord_rec;
      EXIT WHEN ord_cur%NOTFOUND;
      OPEN det_cur;
      LOOP
         FETCH det_cur INTO det_rec;
         EXIT WHEN det_cur%NOTFOUND;
         FOR ship_rec IN ship_cur LOOP
            do_something;
         END LOOP;
      END LOOP;
      CLOSE det_cur;

   END LOOP;
   CLOSE ord_cur;
END; Be sure to try both methods to see which is faster with your data distribution and your queries. This method is not necessarily faster than the join method. Write a test script for each method and use the DBMS_UTILITY.GET_TIME function to obtain times you can compare to see which is faster. Run each test script several times and take an average. Be aware that the second and subsequent runs will usually be faster because the queries have been parsed and are in the SGA.

Also, if you are joining several tables, you can experiment with combining certain tables in joined queries and other tables in nested cursors.

There are many other ways to do this, but this is a start...

Jaz wrote in message <891604552.901330_at_piano.synapse.net>...
>I have a procedure with a cursor that retrieves data from multiple
>tables. Some of these tables have over 100,000 records and there are
>many restrictions used in the WHERE clause. The final result of this
>procedure is creating a delimited file on the server by using the
>UTL_FILE package.
>
> In all, it was taking close to 2 hours to run this procedure, the
>problem being with the cursor. I need to optimize this procedure and
>I am told it is possible to create a hierarchy of cursors but I don`t
>have a clue how to do this.
>
> I am using For (record) in (cursor) LOOP in creating the flat file.
>
>I would be greatful for any help or suggestions you may have.
>
>Thanks,
>
>Jaz
>
>
Received on Sat Apr 04 1998 - 00:00:00 CEST

Original text of this message