Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page