Re: Lookup tables in multiple databases?

On Aug 14, 7:48 pm, grawsha2..._at_yahoo.com wrote:
> Greetings,
> I have a question about "the best" approach to deal with lookup
> tables(employees, sections, etc..) that show up again and again in
> many database (HR, Inventory, CRM ) of an enterprise with Oracle DB
> servers.
> Should a separate database be created to include such lookup tables
> and have that database referenced on the front-end with the main
> database of the application whenever needed; Or have these tables
> created in databases, thus, one ends up with duplicate lookup tables,
> more or less.
> Interms of performance, scalablity and all issues of Oracle GP DB
> design??
> Any urls, books to help.
> MTIA,
> Grawsha

There is a minor performance issue having to do with table sizing. "Small" tables ("the maximum of 2% of the buffer cache and 20, whichever is bigger" - performance tuning guide) are not put on the tail end of the LRU list in the buffer cache. So you can have funny things happen when comparing duplicate lookup tables vs. everything lookup tables within a database (I'm assuming you were using "database" as Oracle people use "schema", since you specified HR, Inventory etc.). For an example similar to Ed's, you might have small tables with different attributes of states and governers, each of which is considered a small table, but when you put them together with a bunch of other unrelated things and access large portions of the table, the result is no longer small. If it is pounded on, then it will stay in memory anyways (but if something in it is updated by many users, you may wind up with latching/cpu issues), but if it is lightly used and lots of other stuff is going on, it may slow things down by aging out of memory and then being brought back in. And then the definition of "small" changes simply by changing the cache size. And many things are "small" these days since memory has gotten cheaper and SGA's larger.

So really, there are many variables, and predicting what will happen requires knowing what they all are and how they interact. Too many to make a general statement about which way to go, so that's why many people recommend just designing the applications correctly and don't try to play games to make it fast until you find something that actually is making it go slow. There are many knobs to play with, including multiple buffer pools, which can mean lumping everything together may reduce your ability to fine tune issues.

jg

--
@home.com is bogus.
""I stay away from fried foods." - Jeremy Orme, vendor of deep-fried
Pepsi.