Re: Why is optimizer choosing this index?

In general, when you use rule-based optimizer you need to determine the driving set which will drive the performance of the query. Driving set is determined by the position of the table in the "FROM" clause and indexes used are based on several different factors such as the join methods (nested loops, outer, sort-merge, hash and cluster) and the ranking of the access paths. Both join methods and access paths are determined by the predicates and columns (indexed/no indexed). Consult books on query optimization and you will know how the RBO works.

Hope this helps.

Alan Lee
BSI Consulting
Houston, Texas

In article <6n8puk$qo5$1_at_nnrp1.dejanews.com>,   tammy.adler_at_ipaper.com wrote:
>
> We are currently operating under rule-based
> optimizer, due to vendor specific choice. However,
> migrating to 'Choose' is on the horizon. We have a
> SQL statement with the following where clause:
>
> where a= ...., date between .... and .... and not
> (b like ....) and (c = ... or c = ... or c = ...)
>
> The index it is choosing (according to the explain)
> has column 'a' and the date column in
> the 1st and 4th position, with columns 2 and 3 of
> the index not named in the where or the select.
> There are other indexes on the columns of the
> where clause, but there are no more than 2 columns
> matching, and the 2 columns are separated by
> others.
>
> Is this performing a bounded range search on the
> indexed columns, even though only there are indexed
> columns between the leading column and the range?
> It would be expected that it would perform slowly,
> filtering on only one column. But it is very fast.
> Plus, the cost based optimizer chooses a different
> path,(Full Scan) and is much slower.
>
> I would like to know what rule the optimizer is using to choose
> this index
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 02 1998 - 09:28:20 CDT