Hi, to everybody,

let's consider this scenario: you have 1 data-table and 10
dictionary-tables; the data-table has 5 million records and 30 columns, 10
of these columns have a foreign-key to the dictionary-tables: the
dictionary-tables have (almsot all) only two columns (code and description)
and a low number of records (less than 100 in most cases, but one table has
1000 records and another one has 8000 records). When you perform a query on
the data-table you must show the descriptions taken from the
dictionary-tables; you have two options to do this:

1) A multiple join (11 tables) to get both main data and descriptions.
2) Load permanently the dictionary-tables in memory (using hashmaps) and
query only the data-table, then the application looks-up the descriptions
from the hashmaps. Consider that you are writing a web application, so the
hashmaps can be hold in application scope to be used by all the users (more
than 1000 users).

Which solution performs better? I think this scenario is rather common.
I'm a little afraid of doing a multiple join on a table that has 5 millions
of records.

Tell your opinion.


Pino

what version of Rdb are you using? seems like the best
solution may be to create an index for each dictionary
table. include both columns and you'll be able to do
"index only" lookups (ie, SELECT DESCRIPTION FROM
DICTIONARY WHERE KEY = :VALUE) where the table itself
is not read - only the index entries are.

You may be able to use a large enough index node size to
keep the entire index in a couple nodes. If you can use
row caches to keep these indexes pinned in memory, you
should get pretty good performance.

Pino wrote:
--
- - - - -
opinions expressed here are mine and mine alone
and certainly are not intended in any way to
express or represent any opinions or commitment
of oracle corporation.

norman lastovica / oracle rdb engineering