Re: long queries on name searches using indexes

Why are you surprised that two different columns should use two different access
paths ? Are the two columns guaranteed
to hold exactly the same data all the time ? (That's a rhetorical question, by the way). Personally I am more surprised by the fact that Oracle uses an index at all when accessing 'SMITH%' - watch out for side-effects if you ever create histograms on these columns.

Run out a full execution plan and check the cost of the table access to the client table on each of the queries, and then check the cost when hinting the query to use the
short-name index. If there isn't much
difference in cost, then reducing the
db_file_multiblock_read_count may be
sufficient to switch the path from t/s to index access.

Alternatively, look at Tim Gorman's paper on "The search for intelligence in the cost based optimizer' - and use the information there to set your optimizer_index_cost_adj to scale back
the relative costs of multiblock vs. single block reads - a typically 'reasonable' value is around 30% to 50%. This may also be
all you need to do.

In passing, your hint use_nl(c) is irrelevant. It tells Oracle to use a nested loop when joining C to the prior row source - but your ORDERED hint makes C the first row source.

Your ordered_predicates hint is also redundant in this query - there is only one predicate available to which it could apply, it can't apply to join predicates.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


Maria wrote in message
<351fd9d6.0302191159.38b47280_at_posting.google.com>...


>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