Re: slow PL/SQL function

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,

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');

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 Received on Thu Jan 30 2003 - 16:55:18 CET