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

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?
>

I think how good a solution this is depends on how large the pl/sql lookup table is and weather or not the select [for each row] has an index available. I do not remember reading exactly how Oracle implements pl/sql tables but their maximum size is OS dependent and my guess is that they are a form of link list so searches for specific values are sequential lookups.

If all you need is the bill_month from t1 then you might be better off to build an index on t1 such that it has the group, first date, and bill_month in it. You could then avoid reading the t1 table and pull the information straight from the index. You would also probably be better off joining the first table, t0, to t1 instead of using a coordinated sub-query. If t1's select is limited to retrieving one or two columns then the join will be to the index.

Anyway, this is just an idea for you to consider. If you plan to use the pl/sql table approach you may want to verify that your table t1 will fit into a pl/sql table. It seems to me we hit the limination somewhere between 10M and 30M, but that was back in (probably) 7.2 days and a different platform.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.