Re: slow PL/SQL function

It is only efficient if the cache doesn't get invalidated. So you have to build a method to ensure the cache doesn't get out of sync with the backend. If other apps are accessing / changing the data then the cache is out of sync. What RDBMS were you using where the middle ware had to be used to get sufficient performance? (not all RDBMS's are equally capable) The way the caching mechanism works in Oracle is to cache frequently accessed items. If it isn't accessed it won't be cached. If you are concerned about other data crowding out the data then put the look up tables in the keep buffer pool away from other items.

Try it before you say it doesn't work.
Jim

"Tim Smith" <timasmith_at_hotmail.com> wrote in message news:a7234bb1.0301311514.3048814a_at_posting.google.com...
> robertsboyle <member21885_at_dbforums.com> wrote in message
 news:<2463993.1044004861_at_dbforums.com>...
> > I am still not quite sure about the tables. You say "code is a unique
> > number" and then replicate it in the inserts. What is the primary key
> > of the table?
> >
> > Nothing wrong with 9 joins if 9 joins are what is needed, but maybe the
> > table design needs revisited. Also as Jim says the data will be in
> > Oracles memory if they are being hit so much.
> >
> > R.
>
> That was a typo - the code is the primary key.
>
> Its just that I have seen proof of caching the code table in
> middleware and transforming codes into descriptions was way more
> efficient than doing dozens (or actually even one) join to the code
> table. I would like to see a design which has hundereds of these
> codes across dozens of tables without the middleware design, keeping
> it in the database.
>
> The middleware was written in c++, no doubt the substitution was in
> order (1) lookup of the value.
>
> Tim
Received on Sat Feb 01 2003 - 08:31:38 CET