Replacing a subselect with Collections

Francesco M. · *nix forums beginner Joined: 01 Apr 2005 Posts: 36

Hello,
thanks for your replies. Well both. I'm interested to give a try to
pl-sql collections and to make the query run a bit faster. I'm
surprised to see that the saving is really negligible if I switch from
the subselect to a pl-sql variable.
As I mentioned, from my Java background I'm accustomed to cache
everything which can be reused....but it seems that with Oracle engine
it works differently.
Thanks
Francesco

mikharakiri_nospaum@yahoo · *nix forums beginner Joined: 05 Feb 2005 Posts: 46

If anag_ptr.desc_ptr is indexed and desc_ptr = snap.ptr_a are very
selective predicates, then your query as fast as it can possibly be (on
server side). Changing those scalar subqueries into pl/sql would
achieve only performance degradation caused by context switch.

DA Morgan · *nix forums Guru Joined: 06 Mar 2005 Posts: 1042

fmarchioni@libero.it wrote:

Francesco M. · *nix forums beginner Joined: 01 Apr 2005 Posts: 36

Hi all oracle users,
I have a cursor which fetches a few thousand records.
Inside it I have a subselect which is used for decoding one field.

CURSOR snap_crs IS
SELECT decode (snap.id, 1,
(select code_ptr from anag_ptr where desc_ptr = snap.ptr_a),

(select code_ptr from anag_ptr where desc_ptr = snap.ptr_b)
),
..... other fields....

My question is: since * anag_ptr * is a static table (doesn't ever
change),
is it worthy to replace the subselect with values taken from a
Collection ?

Having a Java background I would use an Hashtable to insert couples
(code_ptr,desc_ptr)...but what about pl/sql ? can I do the same ?

Thanks a lot
Francesco

Google