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

Other related posts: