Oracle Updating value in one collection using another collection

Question

I have a solution that works using for loops but this method is inefficient and performing poorly. I'm new to using collections and can't find my answer on the internet yet. Can anyone point me in the right direction? This is a simplified version of what I'm trying to achieve.

DECLARE
TYPE rec_numbers IS RECORD
(
  DIGIT NUMBER,
  ENGLISH VARCHAR2 (10),
  FRENCH VARCHAR2(10),
  ITALIAN VARCHAR2(10)
);
TYPE tab_numbers IS TABLE OF rec_numbers;
blk_num tab_numbers;
blk_num2 tab_numbers;

BEGIN

SELECT 1 DIGIT, 'ONE' ENGLISH, NULL FRENCH, NULL ITALIAN 
BULK COLLECT INTO blk_num
FROM DUAL;

SELECT 1 DIGIT, NULL ENGLISH, 'UN' FRENCH, 'UNO' ITALIAN 
BULK COLLECT INTO blk_num2
FROM DUAL;    

FOR i IN blk_num.FIRST .. blk_num.LAST LOOP
    FOR j IN blk_num2.FIRST .. blk_num2.LAST LOOP
        IF blk_num(i).digit = blk_num2(j).digit THEN
            blk_num(i).french := blk_num2(j).french;
            blk_num(i).italian := blk_num2(j).italian;
        END IF;
    END LOOP;      
END LOOP;

END;    

Answer 1

How about this?

update (select * from table(blk_num )) a
set (FRENCH, ITALIAN) = 
    (select FRENCH, ITALIAN 
     from table(blk_num2) b 
     where a.DIGIT = b.DIGIT);

Answer 2

You have not mentioned why you would need a collection here. If you define a TYPE and collection as schema objects, it is possible to do a simple join query using TABLE functions to do exactly what your for loop does.

CREATE OR REPLACE
         TYPE rec_numbers AS OBJECT 
         ( digit      NUMBER,
         english    VARCHAR2(10),
         french     VARCHAR2(10),
         italian    VARCHAR2(10) );
         /

CREATE OR REPLACE    TYPE tab_numbers AS
              TABLE OF rec_numbers;
              /

Code

SET SERVEROUTPUT ON
DECLARE
     blk_num    tab_numbers;
     blk_num2   tab_numbers;
     blk_num3   tab_numbers;
BEGIN
     SELECT rec_numbers(1,'ONE',NULL,NULL) BULK COLLECT
       INTO blk_num
      FROM dual;

     SELECT rec_numbers(1,NULL,'UN','UNO') BULK COLLECT
       INTO blk_num2
     FROM dual;

SELECT rec_numbers
 ( a.digit,
   a.english, 
   COALESCE(b.french,a.french ),
   COALESCE(b.italian,a.italian)  
  ) BULK COLLECT
  INTO blk_num3
     FROM TABLE   ( blk_num  ) a
  LEFT JOIN TABLE ( blk_num2 ) b 
  ON a.digit = b.digit;
  blk_num := blk_num3;

  for i in 1..blk_num.count
  loop
  dbms_output.put_line(blk_num(i).digit ||','||blk_num(i).english
                ||','||blk_num(i).french||','||blk_num(i).italian
                );
  END LOOP;
END;
/

Output

1,ONE,UN,UNO

PL/SQL procedure successfully completed.

Demo

Answer 3

Nested loops can be slow. If blk_num and blk_num2 each have 1000 records, you are doing 1000x1000 = 1 million iterations. That's O(n^2) performance.

You can get this down to O(n) by taking the time to convert blk_num2 into an associative array after you load it. Then, instead of looping through all the entries in blk_num2, you can find the one you are looking for just by asking for it by its index value.

Here is an example of what I mean:

DECLARE
TYPE rec_numbers IS RECORD
(
  DIGIT NUMBER,
  ENGLISH VARCHAR2 (10),
  FRENCH VARCHAR2(10),
  ITALIAN VARCHAR2(10)
);
TYPE tab_numbers IS TABLE OF rec_numbers;
blk_num tab_numbers;
blk_num2 tab_numbers;
-- Define an associative array to copy blk_num2 into.
-- Note: I did not INDEX BY PLS_INTEGER because I do not know how big DIGIT can be.
-- if DIGIT will always fit into a PLS_INTEGER, you can use that instead.
TYPE assoc_numbers IS TABLE OF rec_numbers INDEX BY VARCHAR2(30);
blk_num2_aa assoc_numbers;


BEGIN

SELECT 1 DIGIT, 'ONE' ENGLISH, NULL FRENCH, NULL ITALIAN 
BULK COLLECT INTO blk_num
FROM DUAL;

SELECT 1 DIGIT, NULL ENGLISH, 'UN' FRENCH, 'UNO' ITALIAN 
BULK COLLECT INTO blk_num2
FROM DUAL;    

-- Copy blk_num2 into blk_num2_aa
FOR i in blk_num2.FIRST..blk_num2.LAST LOOP
  blk_num2_aa(to_char(blk_num2(i).digit)) := blk_num2(i);
END LOOP;

FOR i IN blk_num.FIRST .. blk_num.LAST LOOP
  -- Find the match based on the associative array's index value
  IF blk_num2_aa.exists(to_char(blk_num(i).digit)) THEN
    blk_num(i).french := blk_num2_aa(to_char(blk_num(i).digit)).french;
    blk_num(i).italian := blk_num2_aa(to_char(blk_num(i).digit)).italian;
  END IF;
END LOOP;

END;