Re: Why is optimizer choosing this index?

So what you're saying is, the only thing being use out of the index is column a. It doesn't matter that date is a couple columns down the road-- it's not pertinent to the query.

The optimizer will pick any ol' index if one looks as good as another. If you disagree, use a hint to force a particular index: SELECT /*+ INDEX (INDEX_NAME TABLE_ALIAS) */   column_list
FROM TABLE TABLE_ALIAS
WHERE ... Just plug in the name of the index and the table alias. --
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.

tammy.adler_at_ipaper.com wrote in article <6n8puk$qo5$1_at_nnrp1.dejanews.com>...
> 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 - 08:28:26 CDT