Re: Converting strings to ASCII codes to increase search

On Mon, 19 Feb 2007 03:47:53 -0800, s1m0nc00k wrote:

> Hi,
>
> We have a system that contains 50M customer records. We need to
> repeatedly search this table to see if the new customer is actually a
> new customer or has been a customer of ours in the past.
>

Gut feeling - Fast lookup of one in 50M rows should not be a concern in general with Oracle.

Sounds like the business problem is "increase lookup performance on column X of frequently used customer table"

I assume:
- you have statistics and measurements that verify that the lookup of column X on table Y is truly the problem; and - you are using a supported version of Oracle. (Oracle version and OS are very, very important pieces of information.); and - you are using cost based optimizer (so you can actually use the features available in Oracle);
- the queries are static (using bind variables) and not dynamic (rebuilt with the customer ID coded into the query as a new literal each time; - you have adequate memory allocated to each pool ("database buffer", manually set "keep pool", "library cache", etc.)

So some of the other things I would investigate:

• a 'materialized view' that consists of only the relevant information in the customers table, automatically updated;
• extract the relevant lookup information as an IOT;
• proper indexing (not defeating indexes by using 'upper', 'lower', etc.);
• pinning the table (or reduced table above) into the KEEP pool

Have you, or your developers, looked at Tom Kyte's latest books on http://www.apress.com? My experience: performance is directly proportional to undertstanding the architecture in which you are working. Assumptions about the architecture invariably bite.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.