more efficient loop in PLSQL

Question

i'm looking for more efficient ways to loop through PLSQL.

Requirements: imagine that i have BudgetTable & RuleSet table. Ruleset looks something like this:

acc  | loc  | proj || rule1  | tag1  | prio
A1%  | L1%  | P2%  || direct | all   | 90
A12% | L12% | P23% || spread | alloc | 50

the first 3 columns are the where clause for the BudgetTable keys below, and store the rest of the columns..

A123  | L123  | P234
A199  | L199  | P299

and the records being picked up for each loop iteration

loop1: (A1% | L1% | P2% || direct | all | 90)

A123  | L123  | P234  || direct | all   | 90
A199  | L199  | P299  || direct | all   | 90

loop2: (A12% | L12% | P22% || spread | alloc | 50)

A123  | L123  | P234  || spread | alloc | 50

I've done the most straightforward way, by iterating through the RuleSet table.

(pseudocodes)
FOR r in (select acc,loc,proj,rule1,tag1,prio from RuleSet) 
LOOP
    INSERT INTO ResultTable 
    select [columns, rule, tag1,prio ] 
    from BudgetTable 
    where acc like r.acc 
      and loc like r.loc
      and proj like r.proj
      ;
END LOOP;

I am looking for better ways to do this. the problem is the RuleSet can contain several thousands rules, so iterating through & matching the records one-by-one can be lengthy. I was wondering if it's possible to break the loop in several parallel stream & simultaneously run them .. Thanks for the input..

thanx all who replied.. so today, i was able to test both approach. (1) with the for loop (2) with cross join. curiously, with the budgetTable abt 10Mil rows, ruleset about 100k, my original approach with the loop is faster. my theory is, that when dealing with large number of rows, oracle had to swap/spill over into its redo logs (or was it archive logs?) .. — handitri
– handitri, Commented Jul 7, 2016 at 12:36

krokodilko · Accepted Answer · 2016-07-05 14:27:05Z

4

Use simple INSERT+SELECT+JOIN, it should be 30~50 timest faster than your loop:

INSERT INTO ResultTable 
SELECT b.columns, b.rule, b.tag1,prio  
FROM BudgetTable b
JOIN RuleSet r
ON  b.acc like r.acc 
   and b.loc like r.loc
   and b.proj like r.proj

answered Jul 5, 2016 at 14:27

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Andrews · Accepted Answer · 2016-07-05 14:27:12Z

2

Maybe you don't need a loop at all:

INSERT INTO ResultTable 
select [columns, rule, tag1,prio ] 
from BudgetTable bt
 cross join RuleSet rs
where bt.acc like rs.acc 
  and bt.loc like rs.loc
  and bt.proj like rs.proj
  ;

answered Jul 5, 2016 at 14:27

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

2 Comments

handitri Over a year ago

If the ruleset is in the order of 10k rows and budget table is in the order of 20mil rows, would cross join use up too much db resources? Whereas loop would allow for frequent commit points

Tony Andrews Over a year ago

I guess the important number would be how many rows would typically end up being inserted. If this is in the millions then it might be too much to do in one go. On the other hand, if you commit periodically you could end up failing with some data inserted and some not.

Collectives™ on Stack Overflow

more efficient loop in PLSQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related