CODEWITHSUNDEEP
oracle-databaseplsql
tuna
tuna

Reputation: 1

PLSQL collection with bulk collect

In the table MAP_A_B_C below are the records for example, there are 9 million records.

MAP_A_B_C

a_source,b_source,c_source,a_target,b_target,c_target
1001,235,5001,12,1,1
1001,235,5002,11,2,2
1001,236,6012,23,3,1
1002,235,5001,11,2,100
1002,237,5002,32,1,1
1003,239,6012,21,1,5
1003,236,6012,11,3,4

CLIENT

CLIENT_ID, A, B, C
9001,1OO1,235,5001
9002,1003,238,6012
9003,1002,235,5001
9004,1003,236,6013

Now, what is needed?

For a client, if I know the a_source,b_source,c_source from the table CLIENT first I want to make a check if the combination exists in MAP_A_B_C, if exists then return me the value of a_target,b_target,c_target respectively and insert into a table let's say CLIENT_MAPPED with the CLIENT_ID and target values of column A,B,C from table MAP_A_B_C. If the combination doesn't exist, then it will not further process and skip that client and proceed to next iteration.

Note - I used concatenation ('|') to use the combination and then substr() to find the target values from the concatenated string. However, if there is any better way to use a collection/type or nested table it will be helpful. Like for example when we check rec_type(i).exists then it only returns the indexed by column for the source value to a target which is 1 to 1. Here my intention is if we can create 3X3 matrix and check the existence.

Your help is much appreciated.

Upvotes: 0

Views: 80

Answers (1)

hol
hol

Reputation: 8423

To get all clients that exist in the map table:

SELECT * 
FROM   CLIENT
WHERE
(a, b, c) in (SELECT a_source, b_source, c_source
              FROM   MAP_A_B_C )

But you could create the CLIENT_MAPPED table also by just joining them

INSERT INTO CLIENT_MAPPED
  SELECT CLIENT_ID, a_target, b_target, c_target 
  FROM   CLIENT
  JOIN   MAP_A_B_C ON (a_source = a and b_source = b and c_source = c)

Play around with it, I am not sure if got your question right.

If it is a performance issue you might want to put an index on a, b, c and a_source, b_source, c_source respectively:

CREATE INDEX CLIENT_ABC_IDX ON CLIENT (A, B, C);
CREATE INDEC MAP_A_B_C_ABC_IDX ON MAP_A_B_C (a_source, b_source, c_source);

On the new table you could also do a create table right out of the join:

CREATE TABLE CLIENT_MAPPED AS
  SELECT CLIENT_ID, a_target, b_target, c_target 
  FROM   CLIENT
  JOIN   MAP_A_B_C ON (a_source = a and b_source = b and c_source = c)

Hope that helps.

Upvotes: 1

Related Questions