RE: Lookup/Code table as hot block

One technique I've used to good affect is to use dynamic associative arrays to perform the lookup rather than use the database table. I'll give an example but you'll have to forgive any typos as I'm pretty much typing this in on the fly.

If you have a lookup table e.g.  

create table lookup (keyval number, textval varchar2(2000));  

You will often see an application using this sort of function to perform a lookup;  

create or replace function lookup_table(p_key number) return varchar2 as  

retval varchar2(2000);  

begin

  select textval into retval from lookup where keyval = p_key;

  return retval;

end;  

This function can be re-written to use an associative array as follows;  

create or replace function lookup_assoc_array(p_key number) return number is  

type vchartab is table of varchar2(200) index by binary_integer;

t_lookup vchartab;  

begin

  if p_key is null then

     return null;

   else

     return t_lookup(p_key);

  end if;

exception when no_data_found then

begin

  select textval

  into retval

  from lookup

  where description = p_key;

  t_lookup(p_key) := retval;

  return retval;

exception when no_data_found then

  return null;

end;

end;  

The example uses a numeric key to return a text value form a lookup table however an associative array can use a varchar just as easily. In this case the type definition changes to;  

type vchartab is table of varchar2 index by varchar2(200);  

Obviously tables of numbers are also possible.  

I don't know if this would be of any use to solve your original problem but it might provide a useful alternative.    

Cheers,  

Ian  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ranko Mosic Sent: 19 April 2006 21:47
To: _oracle_L_list
Subject: Lookup/Code table as hot block  

Hi List,

I have couple of thousand of users all accessing the same, very small lookup table.

This is hot block in database cache.

How can I avoid this ? Multiple table copies ? How to do this - all users connect under single name .

-- 
Regards, 
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional 
Phone: 416-450-2785
email: mosicr_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMo
sicMain.html 
 


.


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