Re: slow PL/SQL function

Databases are good at joins. Oracle will automatically cache the information in memory since you are accessing it so much. You could mark the table as cached alter table codes cache; and also put it in the keep buffer pool.(see docs for info about that). 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.0301300755.1d2a9574_at_posting.google.com...


> robertsboyle <member21885_at_dbforums.com> wrote in message

 news:<2458439.1043920943_at_dbforums.com>...


> > I don't understand why you need either a procedure or a view for this?

> > Surely all you need to do is

> >

> > select  description

> > from    codes

> > where  code = &param_code;

> >

> > If you are running from the web, say jsp, you would have $param_code to

> > be your ? variable and have it as a bind variable (very important) have

> > a look at the asktom.oracle.com website for examples of bind variables.

> >

> > If you posted your table structures + an example output + oracle

> > version, platform and what exactly you are hoping to do then i may be

> > able to help you further.

> >

> > R.

>

> Ok here is a sample schema

> create table customer (

>   mytable_id           number,

>   last_name            varchar2,

>   first_name           varchar2,

>   cust_type_code       number,

>   cust_status_code     number,

>   sex_code             number,

>   race_code            number,

>   religion_code        number,

>   species_code         number,

>   vip_code             number,

>   application_code     number,

>   update_code          number,

> ... etc

> )

>

> create table codes (

>    code               number,

>    code_type          varchar2(20),

>    description        varchar2(40),

>

> ...

> )

>

> So code is a unique number that is used not only in the customer table

> but in tens or even hundereds of other tables.  It is a logical table

> of tables delineated by the code_type e.g.

>  insert into codes values (10,'SEX','MALE');

>  insert into codes values (11,'SEX','FEMALE');

>  insert into codes values (12,'VIP','1');

>  insert into codes values (12,'VIP','2');

>  insert into codes values (12,'VIP','3');

> etc

>

> It is the major lookup of static data, drop down lists etc.

> It is used everywhere and for this OLTP application is critical to

> access the descriptions efficiently to allow for a massively

> multi-user system.

>

> The code table must be cached somewhere and should never be flushed by

> any large queries or any another activity as it is vital for

> individual transaction performance.

>

> I have seen it cached in middleware but is there a database equivalent

> to provide fast access to the data.

>

> I want to be able to 'select * from customer' and ideally return both

> the field codes and the description.  It would be ridiculous to join

> to the codes table 9 times and gets even more silly if I join the

> customer and order table (since it has over a dozen codes).

>

> Using codes in this fashion is of course to provide the flexibility to

> change them without doing massive updates on tables.

>

> Any ideas or is this not possible in Oracle?

>

> Tim