The query is

select [some columns]
from MYTABLE
where FIELDONE= 2 and FIELDTWO< 2
order by [some columns]

Most of the time most records have FIELDONE = 2 and FIELDTWO = 2, so
this query is looking for what are normally a small number of
exceptions, and one of my objectives is to have it run as fast as
possible in the normal case which is that it finds no matching records.

INDEXONE is on FIELDONE, FIELDTWO ASCending.

In the presence of INDEXONE this query uses this index and is fast,
doing, as one might have hoped, very little work in the normal case of
finding no records.

If I add (for other purposes, to support some other query)

INDEXTWO is on FIELDTWO DESCending

then the plan for the above query switches to using INDEXTWO instead of
INDEXONE and does, of the order as measured by the "Fetches" count, a
similar amount of work to a table scan, burning up a significant number
of CPU cycles and amount of wall clock time in the process.

Why does it switch from using a perfectly good index which takes it
straight to the answer to using one that is, in effect, almost as
useless as not using an index at all?

In this particular case the query is hand written (rather than generated
from any sort of ORM layer or anything) so I do, unusually, actually
have the option of sticking in an explicit PLAN clause as a desperate
last resort if I really have to. But if possible I'd like to understand
what's going on first?

--
Tim Ward