Anybody - date in PL/SQL associative arrays

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Sat, 19 Feb 2005 14:21:00 -0800 (PST)
Message-ID: <20050219222100.88295.qmail@web52602.mail.yahoo.com>

Hi,

Did anybody at any stage use DATE column in associative arrays to do complex searches?

I have the situation where many small tables are with date columns in the primary key. PK is something like:

id1 NUMBER
id2 NUMBER
activation_date DATE

Also having deactivation_date out of PK. Search is on id1 and id2 while checking that the date is between activation and deactivation date.

The idea is to do the lookup on the associative array instaed of the table (do no ask me why without good reason :)

I have only one idea in my head with two dimensional associative array like:

type arr_by_date is table of xxxxxx indexed by binary_integer;
type arr_by_ids is table of arr_by_date indexed by varchar2(sizeof(id1+id2))

Then the lookup will be find out by id1||id2 all records and then check date range inside arr_by_date.

Anbody tried something similar. Currios about performances too.

Should try myself. Will try of course.

I know that you can do a few 100k lookups on associative arrays (tested myself). Not sure about multidimensional lookups and because of dates the use of if/then/else or similar construct.

Any comment welcome.

Regards,
Zoran Martic                 

---

Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Feb 19 2005 - 17:23:56 CST