Re: long queries on name searches using indexes

evoradba_at_yahoo.ca said...
> 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.
>

(snip)
> Any ideias would be great
> Thanks
> Maria
>

Does everyone have a short name? How many people have the same short name, but a different last name? For example, we store the soundex of a person's last name ... which turns up lots of duplicates for unique last names.

The optimizer might decide that it's more cost-effective to do a fulltable  scan on short name if it has to return "most" of the rows anyhow (only CBO knows what "most" means).

These are ideas that came up without too much thought on my part. There are probably other "causes" if I gave it more thought.

-- 
/Karsten
DBA > retired > DBA