Re: Oracle 9i and Release 2

John

Associative arrays fill in the missing link as far as "hashes" go inside Oracle (I'm surprised it took them this long). To my mind they are of greatest use as a means of caching data and exposing it by packaged function for lookups, and this also goes for those keyed by PLS_INTEGER or BINARY_INTEGER. Anything that requires lookup data but cannot access lookup tables in "straight SQL" can benefit hugely from these arrays. Things such as busy OLTP systems and SQL*Loader (or any other load mechanism that cannot join to a lookup table in SQL) can dramatically reduce the lag times of repeatedly accessing database tables in functions by replacing those table lookups with hash lookups - I have seen literally thousand-fold improvements using this technique.

If you are coming from a Perl slant, then you would probably want to use them just about everywhere ;)

Apparently the VARCHAR2 associative arrays are also optimized for sparseness as there would be no sequence to string keys, as opposed to the PLS/BINARY_INTEGER versions, which are more likely to have sequential key values. If your numeric keys are fairly random and your data plentiful, then you *might* see some benefit of TO_CHARring your numeric keys and storing those as VARCHARs. There's a benchmark for someone...

Regards

Adrian

PS Steven Feuerstein wrote a good article on them in this month's Oracle Professional -
http://www.pinnaclepublishing.com/op/OPMag.nsf/0/F2E754B65828DEF985256C1A0056DF3F Received on Mon Sep 23 2002 - 18:31:22 CDT