Re: Lookup/Code table as hot block

Ranko,

I can only echo Jay's question. In particular, one thing that matters is whether this table is accessed through complex joins or through a lookup function.
One way to have multiple copies (assuming that you don't have zillion of concurrent users and that memory isn't a major issue) is to have a PL/SQL array as a package variable, and to load this table (it depends of course on what you mean by 'small', but for a few hundred lines it is likely to be OK) into the initialization section of your package. You will have one copy per session.
PL/SQL arrays are not necessarily indexed by binary integers, you can use them as associative memory (e.g. my_array('old_code') := 'new_code';) it can be quite helpful for small lookup tables.

HTH Stéphane Faroult

JayMiller_at_TDWaterhouse.com wrote:

> First question is how are they accessing it? If via PK then making it
> into an index organized table can cut your lio in half since you won’t
> be doing an index block read.
>
> Thanks,
>
> Jay Miller
>
> Sr. Oracle DBA
>
> x68355
>
> -----Original Message-----
> *From:* oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]
> *Sent:* Wednesday, April 19, 2006 4:47 PM
> *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 <mailto:mosicr_at_rogers.com>
> http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
> <http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html>
>

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