10g - Need advise on large lookup table and optimizing io

Folks,
I inherited an application that has 1 very large lookup table. This
lookup table has
a 90/10 read/write ratio. Most of the time it's read heavily, but
during uploads
records that don't currently exist in that table get inserted.
This table looks like this.

USER_ID| EMAIL | CLIENT_ID | DUPLICATE_ID

------------------------------------------------------------------------
1000000 'email1' 12312 NULL
1000001 'email2' 13434 1

Anyway this table has 400 million records an is not partitioned. He
way 2 main types of
queries against this table.

SELECT email from THISTABLE where DUPLICATE_ID IS NULL and user_id =
?

The other type of query executed is a SELECT where this table is
simply joined on
USER_PROF_ID to pull the email address.

SELECT A.EMAIL, B.COL1,B.COL2
FROM DEMOGRAPHICS B, THISTABLE A
WHERE B.USER_PROF_ID = A.USER_PROF_ID

We now have indexes on the "THISTABLE" so that the actual data is never
read. Only the indexes in place are used to satisfy all queries. The
above SELECT query like many typical queries on our system pulls lots
of data (500K records to 1 million records).

We have one specific index on the columns
USER_ID|EMAIL|DUPLICATE_ID|CLIENT_ID
that we use to satisfy all the large select queries...these queries do
an INDEX RANGE SCAN on the index columns I just described ..and so
never need to access the table rows.

Because the table/indexes have nearly 400 million records in them, and
this index is read my all our "larger" select queries, this index is
contented for and the IO waits are high.

I know that this data model is not great, but i inherited it and can't
really change too mch now. But I'm wondering what I can do to speed up
the lookups during the SELECT queries against that very large index (
Index now has 3 levels).

I've been doing some testing with a single table hash cluster, but I'm
not sure if the benefits of a hash cluster outperform the benefits (in
terms of IO) of looking up the rows in the large index and not having
to read the table data.

If anyone out there has any input, I'd greatly appreciate it.

--peter

.

Relevant Pages

  • Re: Combo Box
    ... His instruction Select Distinct Tablename.From TableName Order ... does the table Main Lookup need to be a combo box as well? ... And DON'T be afraid of queries. ... without having a stored query (Access will ...
    (microsoft.public.access.forms)
  • Re: adding data to data base
    ... duplicates an "Indexed - no duplicates" index in the table, ... The table's error message should give you some idea ... you use the Lookup to show a person's name when ... data - when in actuality their queries are looking for the wrong data. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: 10g - Need advise on large lookup table and optimizing io
    ... > I inherited an application that has 1 very large lookup table. ... Only the indexes in place are used to satisfy all queries. ... > above SELECT query like many typical queries on our system pulls lots ... > I've been doing some testing with a single table hash cluster, ...
    (comp.databases.oracle.server)
  • Re: 10g - Need advise on large lookup table and optimizing io
    ... I inherited an application that has 1 very large lookup table. ... The other type of query executed is a SELECT where this table is ... Only the indexes in place are used to satisfy all queries. ... I've been doing some testing with a single table hash cluster, ...
    (comp.databases.oracle.server)