Would increasing memory benefit bulk collect with performance gain
Oracle 11g 11.2.0.1 on Linux x86-64 2.6.18
In order to speed up a lengthy 'Payroll time rules process' that creates a batch for Payroll, spent considerable time rewriting sections of the code to switch from doing individual queries for certain data items to doing one 'bulk collect' query up-front into a PL/SQL "array" and then doing look ups of the array instead. Debugging lines show that the bulk collect statement runs quite fast.
In theory doing this should have eliminated about 30000 individual queries, replacing them with one larger query and 30000 loops through the PL/SQL array. The PL/SQL associative arrays/VARRAYs give the illusion of being like arrays that one would create and use in languages such as C and Java. So thought that they would be like arrays in memory.
In order to speed up a lengthy 'Payroll time rules process' that creates a batch for Payroll, spent considerable time rewriting sections of the code to switch from doing individual queries for certain data items to doing one 'bulk collect' query up-front into a PL/SQL "array" and then doing look ups of the array instead. Debugging lines show that the bulk collect statement runs quite fast.
In theory doing this should have eliminated about 30000 individual queries, replacing them with one larger query and 30000 loops through the PL/SQL array. The PL/SQL associative arrays/VARRAYs give the illusion of being like arrays that one would create and use in languages such as C and Java. So thought that they would be like arrays in memory.
0