Here's a quick update...
This 400MM record table is mostly used in an equality join to pull the
EMAIL address of a user.
So every customer of ours has a DATA table with a USER_ID, and this
lookup to pull the EMAIL is
typically an equality join in a 3 table join... ie
DRIVING_TABLE a, DATA_TABLE b, LARGE_LOOKUP_TABLE c
WHERE a.userid = b.userid and
a.userid = c.userid
Every query in the system (large queries which pull millions of
records) pull the EMAIL
from that large lookup table.. and typically the wait time on those
datafiles avg about 30-150 ms.
I was able to convince my management to move the EMAIL address into
each customers DATA_TABLE so that everyone is no longer contenting for
that LARGE_LOOKUP_TABLE and the indexes used to satisfy queries... I
think this simple change will dramatically decrease contention, plus it
breaks that dependency on a large lookup table to individual smaller
tables. This gives us some flexibility as we grow (either multiple
customer databases or RAC).
- thanks to everyone for the great feedback as always.
Received on Thu Jan 26 2006 - 09:29:46 CST
