Re: Is it possible to copy an oracle table into a pl/sql table for lookups?

You must remember what a PL/SQL table is. It is an array index by binary integer. You might read the entire look up table into a record and put the record into the PL/SQL table. Then loop around the indexes on the PL/SQL table looking at the date (as part of the record). This may or may not be faster then doing a table look-up depending on what oracle keeps in cache. Since you only have a 1000 or records, it may since to keep the data in memory in an array (PL/SQL table).

Marc Parkinson

In article <HxqN4.266$em2.14171_at_newscene.newscene.com>,   ramdan_at_mailexcite.com (ramdan) wrote:
> We have a stored procedure that will process around 10 million rows.
 for each
> record it has to do a lookup against an oracle table T1 of the
 following
> structure
>
> bill_group bill_month 1st read 2nd read 3rd
 read
> 9 1 01/02 01/03
 01/05
>
> select bill month from T1 where bill group =:bg and 1st date =:date1
>
> Rather than do 10 million selects I would like to create a pl/sql
 table that
> would have the same structure and then do lookups against it like an
 array. I
> am assuming this would be much faster than doing selects.
>
> Any ideas?
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 27 2000 - 00:00:00 CDT