RE: Lookup/Code table as hot block
- From: "Ian Cary \(C\)" <Ian.Cary@xxxxxxxxxxxxxxxxxxxx>
- To: <ranko.mosic@xxxxxxxxx>, "_oracle_L_list" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Apr 2006 12:35:27 +0100
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxx
http://ca.geocities.com/mosicr@xxxxxxxxxx/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
Other related posts:
