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

The first step is to see if you can eliminate record by record processing by using set processing. If you must use record by record processing, it may be beneficial to have the records sorted in a particular manner.

Since your lookup table is relatively small, it is likely to be cached in the database block buffers. Using a pl/sql table in this situation may give a small percentage gain in performance, but not a magnitude gain. The situation where pl/sql tables beat sql is in record by record processing of complex queries and queries of large tables.

Since pl/sql tables can be sparse, you want a value to directly index the entry desired. This is easy if the lookup table PK is a single column, simply index on that column. In your example, the query is based on an id and a date. There are two ways to deal with this situation. One is to generate a hash key using dbms_utility.get_hash_value. The other is to append a few 0s to the id, then scan the pl/sql table from id00..id99 (for example) to find the correct date and return the other values.

In article <3BGN4.19954$e61.717956_at_zombie.newscene.com>,   sdfdsf_at_swed.com (julius) wrote:
>
>
> 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.
> >
>

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