Sean,
how can this possibly be the best index? Michael is selecting MAX and has
defined a descending index. Even if there is a couple of duplicates, I
would believe "ORDERSDBYSEQNUM" to be the best choice.

Are you saying that Fb/IB would choose "ORDERSDBYSEQNUM" if he changed it to
CREATE DESCENDING INDEX ORDERSDBYSEQNUM ON ORDERSD (OD_OM_ID, OD_SEQNUM,
OD_ID);?

I'm confused,
Set

At 12:50 16.12.2002 -0500, you wrote:

> >CREATE INDEX ORDERSDBYINVOICEM ON ORDERSD (OD_OM_ID, OD_ID);
> >CREATE DESCENDING INDEX ORDERSDBYSEQNUM ON ORDERSD (OD_OM_ID, OD_SEQNUM);
> > When I run the following SQL it uses the plan below:
> >
> > select max(od_seqnum)
> > from ordersd
> > where od_om_id = 25433
> >
> > -------------------------------------------------------------------
> > The Plan:
> >
> > PLAN (ORDERSD INDEX (ORDERSDBYINVOICEM))
> > -------------------------------------------------------------------
> > Shouldn't it be using the "OrdersdBySeqnum" index, that one
> > is decending and on the fields being accessed? Why doesn't it?
>
>No, the index ORDERSDBYINVOICEM is the best fit, given the selectivity
>of the OD_ID column (unique!).