RE: Customer Lookup on DB2

I'd like to thank everyone for their input into my dilemma.

There have been several replies that address the regional nature of a
customer lookup. I.E. the person's location - zip code, state, etc...

As things go, the business rules are defined without much input
from the people who code the stuff. They have the option of putting in a
zip code, which returns the result set immediately. The only required
criteria are that at least the first letter of their first name be
included - so that helps a little.

I have received several replies regarding the use of a clustered index
and frequent reorgs to keep the index data in the correct order to
eliminate the "order by". I have to answer to a somewhat volatile
management structure and if the results are not consistent then I am in
a world of hurt. Still, based upon reading the manual, clustered indexes
do not guarantee a physical ordering, only that rows that would be
ordered are found on the same page.

Special kudos to Mike Hut who suggested using the soundex() function in
the following manner:
Soundex(substr(name_last,1,3)||substr(name_first,1,1))
By adding an additional column onto my main customer table using the
"Generate Always As (generation expression)" column default spec, I can
store the phonetic representation of the basic characteristics of the
name and later search on this single column. This may prove to help
people who can't spell very well. Some experimentation needs to take
place, but it is an elegant solution.

Baiju: You are not wasting my time. I hope I have not wasted yours.

Currently I am on a LUW (AIX) v7.2 database that is about to be migrated
to a new machine and upgraded to v8.2. I have rebuilt my indexes to
type-2 and reorganized the tables involved. After doing so (about 1am
last night) I found that I had about a 10,000 fold improvement in
timerons, cpu utilization, and I/O. The result set returns in about 1/2
the time (1m40s versus 2m54s). When I drop the "order by" the results
are returned in 6 seconds in the worst case scenario.

Douglas Kostelnik
Senior Database Administrator/Architect

-----Original Message-----
From: BAIJU VARGHESE [mailto:BAIJU.[Email address protected]
Sent: Wednesday, October 25, 2006 9:30 AM
To: Doug Kostelnik
Subject: Re: Customer Lookup on DB2

**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text

When you have 100,000 Smiths in your table, there should be some other
criteria that gave you the 'Smith' that you want.
And if 100,000 Smiths satisfy all the criteria, how do you know which
'Smith' is that you want?
If you are looking for just a certain number of rows back, one thing you
can use is the 'FETCH FIRST n ROWS ONLY' option. But that does not
guarantee you that the results will be in order unless you use 'order
by' and it could be costly as well.

Another way..here is an example
SELECT *
FROM (SELECT s.*
,ROW_NUMBER() OVER(ORDER BY inv#) AS row#
FROM invoice s (here goes your sql to select the Smith that you want..)
)xxx
WHERE row# <= 5
ORDER BY inv#;

I don't know wether this is helpful, if it is not..sorry to waste your
time..

Baiju

>>> "Doug Kostelnik " [Email address protected] 10/24/2006 4:00:32 PM
>>>

Hi,

We have a customer database that has about 12,000,000 rows. When we do a
customer lookup function, it joins several tables together and returns a
result set that allows a counselor to choose the correct customer.

The basic rule is that the customer has to supply at least the first
letter of their first name and their entire last name. A query on M%
SMITH (where name_first like 'M%' and name_last='SMITH') takes quite
some time and returns about 100,000 ordered rows. In order to
accommodate an ease of use requirement, the management wants to change
the rule to at least the first letter of their first name and at least
the first three letters of their last name. Thus the query on M% SMITH
turns into M% SMI% (where name_first like 'M%' and name_last like
'SMI%'), which ends up with 200,000+ rows after the order by.

If we do a lookup on a non-common name like mine, the current query
returns the result set instantly. It is only on name like Jones, Smith,
Gonzalez, etc... that we choke.

I am looking for a way that would:

1) Consistently return the result set in name_last,
name_first order

2) Return the results quickly

Given these constraints, does anyone have a thought on how to write the
optimal query? Crazy (but doable) ideas are welcome.

Douglas Kostelnik

Senior Database Administrator/Architect

[Email address protected]

AAA Auto Club South

(813)-289-1342

<html>
 <align=justify> 
_____________________________________________________________ 
The information transmitted is intended only for the person(s) or entity
to which it is addressed and may contain confidential and or privileged
material and should be treated as a confidential AAA Auto Club South
communication. If the reader of this message is not the intended
recipient, you are hereby notified that your access is unauthorized, and
any review, dissemination, distribution or copying of this message
including any attachments is strictly prohibited. If you are not the
intended recipient, please contact the sender and delete the material
from any computer.
</html>

---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page