Hello,

I am writing a procedure, and i am using same query 4 times to update/ insert in different tables. Is it better to dumb data in temp table and then query temp table or querying again and again to get same result set
e,g

1. merge into table1 using ( select x,y,z from tab2 left join tab3 ....)
2. merge into table2 using ( select x,y,z from tab2 left join tab3 ....)
3. Insert into table3 (x,y, p) ( select x,y,z*3 from tab2 left join tab3 ....)

Or
1 Insert into temptab (x,y, p) ( select x,y,z*3 from tab2 left join tab3 ....)
2 merge into table1 using ( select x,y,z from temptab)
3 merge into table2 using ( select x,y,z from temptab)
4 Insert into table3 (x,y, p) ( select x,y,z*3 from temptab)

>>> tab2 & tab3 are big tables 12000000 rows and growing
>>> temptab may be 1000 at most

Is there a better way

Thanks in advance

You can use 'global temp table' for the purpose (i.e the 2nd method)

As long as you have the proper indexes to optimize the queries in the first scenario, then I would go with that. There will be one less DB access (3 steps instead of 4).

If you do not have the indexes, then scenario 2 would be better because the major cost would be the big query on tab2, tab3, ..., and you would only do it once in the second scenario whereas you would do it twice in the first.

Andy

3nd option,

1) use 'cross load' utility to unload the query result set
2) use 'load' utility to load the result set back to table 1
3) use 'load' utility to load the result set back to table 2
4) use 'load' utility to load the result set back to table 3
...