Re: Worst of the worst

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Fri, 30 May 2003 08:18:42 -0700
Message-ID: <3ED77652.265D9B46@exxesolutions.com>

Adrian Billington wrote:

> Here's another from the "Collections and Records" guide in the 817
> docs:-
>
> CREATE TABLE coords (x NUMBER, y NUMBER);
> CREATE TYPE Pair AS OBJECT (m NUMBER, n NUMBER);
> /
>
> DECLARE
> TYPE PairTab IS TABLE OF Pair;
> pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6));
> TYPE NumTab IS TABLE OF NUMBER;
> nums NumTab := NumTab(1, 2, 3);
> BEGIN
> /* The following statement succeeds. */
> FORALL i in 1..3
> UPDATE coords SET (x, y) = (pairs(i).m, pairs(i).n)
> WHERE x = nums(i);
> END;
> /
>
> I love the comment "The following statement succeeds" when it is
> clearly syntactically incorrect and fails miserably. Using collections
> of records in FORALL is still not supported in 9.2 and I submitted an
> enhancement request for this a long time ago... Quite what the author
> of the above was smoking I don't know.
>
> Adrian

Good catch. At least there is a good work-around for the collections not be supported. I put together the following demo for my student's that does a pretty good demo. Here it is for the group:

• demo tables

CREATE TABLE parent (
part_num NUMBER(10),
part_name VARCHAR2(15));

CREATE TABLE child AS
SELECT *
FROM parent
WHERE 1=2;

• anonymous block to generate 50K records DECLARE

j PLS_INTEGER := 1;
k parent.part_name%TYPE := 'Transducer';

BEGIN
   FOR i IN 1 .. 50000
   LOOP

      SELECT DECODE(k, 'Transducer', 'Rectifier',
      'Rectifier', 'Capacitor',
      'Capacitor', 'Knob',
      'Knob', 'Chassis',
      'Chassis', 'Transducer')
      INTO k
      FROM dual;

      INSERT INTO parent VALUES (j+i, k);

   END LOOP;
   COMMIT;
END;
/

• tranditional method CREATE OR REPLACE PROCEDURE old_way IS

CURSOR p_cur IS
SELECT *
FROM parent;

p_rec p_cur%ROWTYPE;

BEGIN
   OPEN p_cur;
   LOOP

      FETCH p_cur INTO p_rec;
      EXIT WHEN p_cur%NOTFOUND;

      p_rec.part_num := p_rec.part_num * 10;

      INSERT INTO child
      (part_num, part_name)
      VALUES
      (p_rec.part_num, p_rec.part_name);

   END LOOP;
   COMMIT;
   CLOSE p_cur;
END old_way;
/

• bulk collect with FORALL method CREATE OR REPLACE TYPE uw_sel_row AS OBJECT ( part_num NUMBER, part_name VARCHAR2(15)); /

CREATE OR REPLACE PROCEDURE new_way IS

TYPE uw_sel_tab IS TABLE OF uw_sel_row;
uw_selection uw_sel_tab;

TYPE PartNum IS TABLE OF parent.part_num%TYPE INDEX BY BINARY_INTEGER; x PartNum;
TYPE PartName IS TABLE OF parent.part_name%TYPE INDEX BY BINARY_INTEGER; y PartName;

BEGIN
   SELECT part_num, part_name
   BULK COLLECT INTO x, y
   FROM parent;

   FOR i IN x.FIRST .. x.LAST
   LOOP
      x(i) := x(i) * 10;
   END LOOP;    FORALL i IN x.FIRST .. x.LAST
   INSERT INTO child
   (part_num, part_name)
   VALUES
   (x(i), y(i));

END new_way;
/

You don't need SET TIMING ON to see the dramatic difference in performance.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Fri May 30 2003 - 10:18:42 CDT