Performance of Collections
So, how do collections measure up in terms of performance? We’ve already seen that they can be used to greatly simplify coding within PL/SQL (and into other environments as well), but are they quick?Well, they can be, but they may also be a serious performance problem, if certain considerations are not understood. The following sections highlight a typical problem and various solutions.
Using ROWNUM
It’s a common trick, but referencing rownum within a sub-query causes Oracle to materialize the results into temp (otherwise known as “preventing query merging taking place"). How does this help with collection performance? Well, consider the following example :
