Re: Slow Query in PL/SQL Block

Hi Brijesh,

   I'm not sure I understand your question properly, but I'd like to add to your post that SQL is USUALLY faster than the equivalent in PL/SQL (even though I've certainly seen exceptions, with properly-tuned PL/SQL). Your code, as written now, triggers a "context switch" from the PL/SQL engine to the SQL engine for each cursor row fetched. A context switch consists at a high level of storing everything needed for when the program control comes back to the PL/SQL or SQL engine
(SQL record count, PL/SQL user variable values, ...). If you decide to
stick to PL/SQL (as opposed to pure SQL) for whatever reason, use collections in order to minimize this context switching overhead. To make sure to not eat up all your (server) RAM, limit your bulk collects to 500 records, or possibly 200. Look in the PL/SQL programming
(chapter 5) of your Oracle version to find the exact syntax to use for
collections.

HTH Daniel Received on Sun Aug 14 2005 - 08:50:07 CDT