Re: Relational database architecture

You don't have to try and pin the table in memory. Oracle will do that for you based upon how often it is accessed. You can use a function. or inline sql.
(put it in a package)
create or replace function get_description(person_type) varchar2 as

    per_desc varchar2(xx); -- or column type here begin

    select foo into per_desc from my_lookup where x=y and type=person_type; return per_desc;
end;
/
(put in error handling in case you get 0 rows.) Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Tim Smith" <timasmith_at_hotmail.com> wrote in message
news:a7234bb1.0301281309.4aa18dc0_at_posting.google.com...


> Also if I have a table with 20 codes is it viable to join to the

> static lookup table 20 times?

>

> Is it possible to have some kind of Oracle function such as

>  select get_description(person_type) from table_data

>

>

> Tim

>

> "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message


 news:<VdmZ9.68695$AV4.3381_at_sccrnsc01>...


> > Views.  Definitely views.  All apps can use a view.  That will


 automatically


> > cache that static look up table for you.


> > Jim

> >

> > --

> > Replace part of the email address: kennedy-down_with_spammers_at_attbi.com

> > with family.  Remove the negative part, keep the minus sign.  You can

 figure


> > it out.


> > "Tim Smith" <timasmith_at_hotmail.com> wrote in message

> > news:a7234bb1.0301271504.1d6c7930_at_posting.google.com...

> > > Hi,

> > >    Assume a large enterprise database with the following common theme:

> > >

> > > table_data

> > > pkey  person_type         sex etc

> > >    1          50          175

> > >    2         100          175

> > >    3          50          200

> > >

> > > static_lookup

> > > pkey_code  short_description set

> > >        50  customer          person type

> > >       100  employee          person type

> > >       175  male              sex

> > >       200  female            sex

> > > etc

> > >

> > > If I select from table_data I see the above.

> > > If I want to see the values for the codes I need to do:

> > > select sl.short_description as person_type,

> > >        sl2.short_description as sex

> > > from table_data td, static_lookup s1, static_lookup s2

> > > where td.person_type = sl.pkey_code

> > >   and td.sex = s2.pkey_code

> > >

> > > This type of access is repeated throughout all applications.

> > >

> > > My question is how to optimize this efficiently.  Ideally I want the

> > > application designers to be able to do

> > > select * from view_data

> > > and get both the code and the description.  The former for filling out

> > > data structures and the latter for user display.

> > >

> > > In the past I have seen this done in the middleware which has a cache

> > > of the codes and fill out data structures as requested.  However this

> > > only works for the apps and is less usefull when you want to do direct

> > > queries against the database (typically by users).  It is however

> > > extrememly efficient - faster than ever joining to each table in a

> > > query.

> > >

> > > Is there some way of pinning the table in memory and using stored

> > > procedures and views - will it work as well?

> > >

> > > thanks!

> > >

> > > Tim