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

The table has 1008 rows and is 60k in size

no index since oracle would do full table scan anyway since it is faster than an index an a small table like that, and our default block read is 16 blocks and this table has only 9 so entire table is read into buffer

>>
>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.
>
Received on Wed Apr 26 2000 - 00:00:00 CDT