Oracle PL/SQL for DBAs

Bulking Up

If you fetch records , one by one, via a PL/SQL loop, you will incur the overhead of context switching between SQL and PL/SQL once per record. This will dramatically increase the elapsed time if you are processing a large number of records. You can reduce the number of context switches by using a bulk fetch (BULK COLLECT INTO) to query records in sets or all at once.

First, here is an example of what I mean by fetching one record at a time:

    CREATE OR REPLACE PROCEDURE one_at_a_time AS
      CURSOR curs_get_ord IS
      SELECT order_number,
             order_date
        FROM orders
      ORDER BY order_number;
      v_order_number NUMBER;
      v_order_date   DATE;
    BEGIN
      FOR v_order_rec IN curs_get_ord LOOP
        do_something;
      END LOOP;
    END;

If the ORDERS table contains 100 records, then 100 context switches would occur. Here is the bulk fetch version of the code.

    CREATE OR REPLACE PROCEDURE all_at_once AS
      CURSOR curs_get_ord IS
      SELECT order_number,
             order_date
        FROM orders
      ORDER BY order_number;

      -- local collections to hold bulk fetched values
      TYPE v_number_t IS TABLE OF NUMBER;
      TYPE v_date_t   IS TABLE OF DATE;
      v_order_number v_number_t;
      v_order_date   v_date_t;

    BEGIN
      -- get all orders at once
      OPEN curs_get_ord;
      FETCH curs_get_ord BULK COLLECT INTO v_order_number, v_order_date;
      CLOSE curs_get_ord;
      -- if any orders were found then loop through the local
      -- collections to process them
      IF NVL(v_order_number.COUNT,0) > 0 THEN
        FOR counter IN v_order_number.FIRST..v_order_number.LAST LOOP
          do_something;
        END LOOP;
      END IF;
    END;

For large record ...