How to load a TABLE fuilly in memory and keep it

[Jaime Stuardo]

Hi all...

I have a table that will never change. Specifically it's a mortality chart
that I need to use to make some calculations. One column of the table is the
age of the person, and other column is the factor to apply to the
calculation.

Suppose the calculation need to be made for 5000 people. It's inadmissible
to make 5000 SELECT's to get the factor for each person. That calculation
isn't done for only one group of persons, but for a lot of groups. So the
solution is to place all that data in memory, in ascending order, so that
the age will be the index to that memory table and the factor will be the
data stored in that memory location.

I want to be able to do something like this

VALUE := 0;
FOR I IN 1..NUM_PERSON LOOP
VALUE := VALUE + TABLE_IN_MEMORY( AGE( I ) ) * SOME_OTHER_NUMBER;
END LOOP;
Is it clear what the problem is? Any help will be greatly appreciated

Thanks a lot in advance
Jaime

[mcstock]

don't think in terms of 3GL programming....

if you need to make the calculation for 1 or 5,000 different rows, simply
join in the correct row from your mortality table, ie:

select p.name, p.age, p.age * m.factor
from
person p
, mortality_chart m
where
p.something = <some criteria>
and
p.age = m.age

likely you'll index the AGE column in your mortality_chart table

let Oracle handle the caching of data from the lookup table -- that's its
job
on the other hand, if you really, need an in-memory structure for your
mortality table, you can populate a PL/SQL collection with the values --
either from a table or just coded in your PL/SQL package -- and use the age
as an index

give some more details on how you're processing your 5,000 mortals

-- mcs

"Jaime Stuardo" <js******@softhome.net> wrote in message
news:3f********@nova.entelchile.net...
| Hi all...
|
| I have a table that will never change. Specifically it's a mortality chart
| that I need to use to make some calculations. One column of the table is
the
| age of the person, and other column is the factor to apply to the
| calculation.
|
| Suppose the calculation need to be made for 5000 people. It's inadmissible
| to make 5000 SELECT's to get the factor for each person. That calculation
| isn't done for only one group of persons, but for a lot of groups. So the
| solution is to place all that data in memory, in ascending order, so that
| the age will be the index to that memory table and the factor will be the
| data stored in that memory location.
|
| I want to be able to do something like this
|
| VALUE := 0;
| FOR I IN 1..NUM_PERSON LOOP
| VALUE := VALUE + TABLE_IN_MEMORY( AGE( I ) ) * SOME_OTHER_NUMBER;
| END LOOP;
|
|
| Is it clear what the problem is? Any help will be greatly appreciated
|
| Thanks a lot in advance
| Jaime
|
|
|
|