Re: which is more scalable - view or raw table access+offload to application server

Timasmith wrote:
> Suppose I have a database table with 20 fields which are lookups to a
> single table.
>
> configtable(configtable_id, a_field, something_lookup_id,
> another_lookup_id, ...)
> lookup(lookup_id, value, description, ...)
>
>
> what is going to be faster to map the rows to an Object which needs
> the 'value' for every field ending in lookup_id
>
>
> a) View
>
>
> select c.configtable_id, l1.value as something_lookup, l2.value as
> another_lookup
> from configtable c,
> lookup l1,
> lookup l2
> where c.something_lookup_id = l1.lookup_id
> and c.another_lookup_id = l2.lookup_id
>
>
> foreach row
> map values to object
> end
>
>
> b) Cache all lookup values and populate
>
>
> select c.* from configtable
>
>
> foreach row
> map values to object
> if lookup_id
> find value from hashtable and map value to object
> endif
> end
>
>
> It seems that the latter *might* be better to scale outward better,
> as
> you could add application servers to do the caching/mapping and you
> only select from a single table?

My initial impression is that you are about to make a mistake. Which one is hard to tell. It would be really helpful if what you were asking was clearly stated and there was an Oracle version number somewhere within sight.

If you are talking about using objects the answer clearly is not to use them for this purpose.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org