RE: Ant: Memory based tables

Perhaps the original poster was thinking of associative arrays. This is another name for a PL/SQL based table that can be stored as a package variable and can be used to lookup data far quicker than from disk.

e.g.

create table lookup_table
( domain_value number,
  description varchar2(200))

The application needs to transform a descriptive term into its lookup value. An OLTP function to do this would be (forgive any syntax blunders as I'm typing this on the fly)

create or replace function lookup_val (p_desc varchar2) return number is

retval number;

begin
  select domain_value
  into retval
  from lookup_table
  where description = p_desc;
end;

I say an OLTP function because as soon as you need to call this more than once (e.g. for an ETL process) it becomes a pretty inefficient approach. Obviously the most efficient approach would be to use a SQL*Plus table join however lets assume this can't be done for some reson or another. e.g. too much rework because the function is used in several different places

The idea behind an associative array is to create a PL/SQL table in memory that can be accessed via a function. There are two ways the PL/SQL table can be built - either by pre-populating it with the entire lookup table when a transaction starts or by creating it as you go by looking up new values if they are not found in the array. I think either way has merits and the decision probably depends on the number of rows in the lookup table and how many distinct values need transforming. The example below populates the array as and when a new value is hit

create or replace package lookup as
  type numtab is table of number index by varchar2(200);   t_values numtab;

  function domain_value (p_desc varchar2) return number; end;

create or replace package body lookup_value as

retval number;

function domain_value(p_desc varchar2) return number is begin
  if p_desc is null then

     return null;
   else
     return t_values(p_desc);
  end if;
exception when no_data_found then
begin
  select domain_value
  into retval
  from lookup_table
  where description = p_desc;
  t_values(p_desc) := retval;
  return retval;
exception when no_data_found then
  return null;

end;
end;
end;


 

Both functions are called in a similar manner  

e.g. select lookup_val('Ordinary') from dual;  

or select lookup.domain_value('Associative') from dual  

however you will notice that for multiple calls within the same session the second approach does far less work and is therefore more efficient.

One word of warning - it helps to know the size of the lookup table/s as this data is stored in the PGA and might not be a good idea for very large lookup tables :)

Hope this helps,

Cheers,  

Ian

This email is only intended for the person to whom it is addressed and may contain confidential information. If you have received this email in error, please notify the sender and delete this email which must not be copied, distributed or disclosed to any other person. Unless stated otherwise, the contents of this email are personal to the writer and do not represent the official view of Ordnance Survey. Nor can any contract be formed on Ordnance Survey's behalf via email. We reserve the right to monitor emails and attachments without prior notice.

Thank you for your cooperation.

Ordnance Survey
Romsey Road
Southampton SO16 4GU
Tel: 023 8079 2000
http://www.ordnancesurvey.co.uk

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