long queries on name searches using indexes

Hi all

we are having the following issues in relates to oracle way of viewing things, below is a description of the problem

if you perform a client search on LAST_NAME of SMITH, the query returns in less than 2 seconds
- if you perform the exact same search, but use SHORT_NAME of SMITH, the query returns in 40 seconds

Both LAST_NAME and SHORT_NAME are indexed.

I took a look at the query plans, and sure enough, if you search on last name, the query uses the LAST_NAME index. If you search on short name, the query does a table scan of client.

SELECT /*+ USE_NL(c) ORDERED ORDERED_PREDICATES */

        COUNT(distinct c.client_id)
FROM client c,client_account ca,account a WHERE ca.client_id = c.client_id AND a.account_group = ca.account_group AND client_name_last LIKE 'SMITH%'

THEN, try this:
SELECT /*+ USE_NL(c) ORDERED ORDERED_PREDICATES */

        COUNT(distinct c.client_id)
FROM client c,client_account ca,account a WHERE ca.client_id = c.client_id AND a.account_group = ca.account_group AND client_name_short LIKE 'SMITH%'

The only way I can get the query to use the index for short name is to use an index hint. The index hint is not needed for last name (go figure!?!). If we want to improve the performance of this, we may have to change the code to insert the hint if short_name is specified. Any ideias would be great
Thanks
Maria Received on Wed Feb 19 2003 - 13:59:49 CST