> At 11:48 AM 19-04-02 +1200, you wrote:
> >I have a table with two indices
> >
> >index1 on fieldA, fieldB
> >index2 on fieldA, fieldC
> >
> >fieldB is varchar (20) and fieldC is numeric (18, 0)
> >
> >when I do a select statement like
> >
> >select * from tableA
> >where fieldA = :val1 and
> > fieldC = :val2
> >
> >I expect it to use index2, but for some reason it uses index1. I need the
> >statement to use index2 as I'm using them in stor proc, and this
> >behaviour makes stor proc too slow.
>
> You are encountering that ol' problem of overlapping indexes. The solution
> is supposed to be "force the optimizer to use an explicit plan" but, in
> practice, it's not always perfect, ofttimes not even worthwhile if any of
> the indexes involved are system-generated - your plan will become invalid
> in the event you have to restore from backup.
>
> What's commonly done is to disable the "usefulness" the optimizer perceives
> in using index1, e.g. by including a fatuous WHERE criterion involving the
> outlying column of the unwanted index....so, in this case, adding
> ...
> WHERE
> ...
> and fieldB = fieldB
>
> should do it. It's quite fun to play about with stuff like this in IB_SQL
> and see the different plans that the optimizer produces. When you see one
> that looks good to you, go with that statement for your app.
>
> Another alternative is not to define overlapping indexes if there is a way
> to avoid it. For example, with individual indexes on each of fieldA.,
> fieldB and fieldC, the individual indexes on A and C would get used for
> your example and the one on fieldB would be ignored; or one on
> fieldA-fieldC and one on fieldB would cover the possibilities.
>
> And of course there is that ol' warhorse - don't define *any* indexes that
> duplicate the auto indexes the database creates for constraints.
>
> Helen

Creating an index on fieldB works.

Thanxs

Regards

Sandeep

Software Developer
CFL
sandeep@...
http://www.cfl.co.nz