Path: g2news2.google.com!postnews.google.com!p10g2000cwp.googlegroups.com!not-for-mail
From: "Timasmith" <timasm...@hotmail.com>
Newsgroups: comp.databases.oracle.server
Subject: which is more scalable - view or raw table access+offload to application server
Date: 6 Mar 2007 17:10:47 -0800
Organization: http://groups.google.com
Lines: 48
Message-ID: <1173229847.698157.47350@p10g2000cwp.googlegroups.com>
NNTP-Posting-Host: 66.30.187.121
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1173229864 30897 127.0.0.1 (7 Mar 2007 01:11:04 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 7 Mar 2007 01:11:04 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322; Media Center PC 4.0),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: p10g2000cwp.googlegroups.com; posting-host=66.30.187.121;
   posting-account=G8SwugwAAABFbVawXPDZLjgjOmGuJ0_5

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?