Why is optimizer choosing this index?

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 Received on Mon Jun 29 1998 - 14:29:25 CDT