Hello Friends;
I am asking this as a database administrator, perhaps more on behalf of
the SQL Programmer.

We have a search on a view that uses a street number and a partial
street name in a LIKE clause.  It is a very slow, inefficient search,
pretty much unusable, and disruptive when we do use it. I believe the
view has about 200,000 records.  I will post the view code and the
select statement below, I hope that is useful, but I believe I may be
asking more "What is a good method/process for doing searches with
partial data available?, is the a proven widely used technique?"
Please tell me what to add to make this a more complete inquiry.
Thanks.  The application is websphere, the database is IBM DB2 UDB V8.2
for Linux Intel 32 bit, at fixpak 8.  Following are the view and the
select code incase it shows something glaring,  Thanks sincerely
Claude

This is the VIEW definition:

create view CCDB.BASIC_CLIENT_V as  select     CCDB.CLIENT.ID as
CLIENT_ID,   CCDB.CLIENT.CLIENT_TYPE_ID as TYPE_ID,
CCDB.CLIENT_NAME.ID as NAME_ID,   CCDB.CLIENT_NAME.NAME_TYPE_ID as
NAME_TYPE_ID,   CCDB.NAME_LIST.FIRST_NAME as FIRST_NAME,
CCDB.NAME_LIST.MID_NAME as MID_NAME,   CCDB.NAME_LIST.LAST_NAME as
LAST_NAME,   CCDB.NAME_LIST.FULL_NAME as FULL_NAME,
CCDB.NAME_LIST.GIVEN_NAMES as GIVEN_NAMES,
CCDB.CLIENT_EVENT.OCCURRED_ON AS OCCURRED_ON,  CCDB.ADDRESS.STREET_NUM
AS STREET_NUM,  CCDB.ADDRESS.STREET_NAME AS STREET_NAME,  '' AS NAME1,
'' AS NAME2,    CCDB.CLIENT_IDENTIFIER.IDENTIFIER AS CLIENT_IDENTIFIER,
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AS IDENTIFIER_TYPE_DESCRIPTION,
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AS IDENTIFIER_TYPE_CODE,
CCDB.CLIENT_NAME.END_DATE AS NAME_END_DATE,
       CCDB.PHONE_NUMBER.PHONE_NUMBER AS PHONE_NUMBER  FROM
CCDB.CLIENT_PHONE RIGHT OUTER JOIN CCDB.CLIENT ON
CCDB.CLIENT_PHONE.CLIENT_ID = CCDB.CLIENT.ID                 LEFT JOIN
CCDB.PHONE_NUMBER ON CCDB.CLIENT_PHONE.PHONE_ID = CCDB.PHONE_NUMBER.ID,
                                      CCDB.CLIENT_EVENT,
CCDB.CLIENT_IDENTIFIER,              CCDB.CLIENT_TYPE,
CCDB.CLIENT_NAME,              CCDB.NAME_LIST,
CCDB.CLIENT_ADDRESS,           CCDB.ADDRESS    WHERE
CCDB.CLIENT_TYPE.ID = CCDB.CLIENT.CLIENT_TYPE_ID   AND ( CCDB.CLIENT.ID
= CCDB.CLIENT_EVENT.CLIENT_ID   )   and ( CCDB.CLIENT.ID =
CCDB.CLIENT_IDENTIFIER.CLIENT_ID   )   and ( CCDB.CLIENT.ID =
CCDB.CLIENT_NAME.CLIENT_ID  )   and ( CCDB.CLIENT.ID =
CCDB.CLIENT_ADDRESS.CLIENT_ID  )   AND 2 = CCDB.CLIENT_TYPE.ID  AND 1 =
CCDB.CLIENT_ADDRESS.ADDRESS_TYPE_ID  AND 1 =
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID    AND 1 =
CCDB.CLIENT_EVENT.EVENT_TYPE_ID   AND ( CCDB.ADDRESS.ID =
CCDB.CLIENT_ADDRESS.ADDRESS_ID  )     AND  (
CCDB.CLIENT_ADDRESS.END_DATE > CURRENT DATE OR
CCDB.CLIENT_ADDRESS.END_DATE IS NULL)    AND  (
CCDB.CLIENT_EVENT.END_DATE > CURRENT DATE OR CCDB.CLIENT_EVENT.END_DATE
IS NULL)   AND  ( CCDB.CLIENT_IDENTIFIER.END_DATE > CURRENT DATE OR
CCDB.CLIENT_IDENTIFIER.END_DATE IS NULL)     and (
CCDB.CLIENT_NAME.NAME_ID = CCDB.NAME_LIST.ID   )   AND (
CCDB.CLIENT_NAME.NAME_TYPE_ID in (1,2))  AND  (
CCDB.CLIENT_NAME.END_DATE > CURRENT DATE OR CCDB.CLIENT_NAME.END_DATE
IS NULL)

Here is the where portion of the select statement that is paining us,
it is a SELECT DISTINCT bunch of fields from the view
WHERE BASIC_CLIENT_V.STREET_NUM='15' AND BASIC_CLIENT_V.STREET_NAME
LIKE 'MACKINNON%' ORDER BY UPPER(BASIC_CLIENT_V.LAST_NAME) ASC,
UPPER(BASIC_CLIENT_V.FIRST_NAME) ASC, UPPER(BASIC_CLIENT_V.MID_NAME) ASC