> > 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.
>
> No, Sean is right.
> An index for MAX() is used when the ORDER of the query is determined.
> for example: PLAN (ORDERSD ORDER ORDERSDBYSEQNUM)
> But because the resultset is depended from od_om_id no index can
> be used for
> the MAX(). Indices for Aggregate functions are added to the sort-clause.

This sounds even stranger, since:

CREATE DESCENDING INDEX ORDERSDBYSEQNUM ON ORDERSD (OD_OM_ID, OD_SEQNUM);

contains both "OD_OM_ID" which is selected for, and "OD_SeqNum" which
is the field the max is applied to. I really don't understand unless
this is just a case of poor coding in the IB optimizer. I mean how
much better could a select fit a index?


You know, just for a test I added "OD_ID" (the primary key for the file)
to the end of the above index, it was then selected in the plan, but when
I executed:

select max(od_seqnum)
from ordersd
where od_om_id = 25433

it read all the rows that had od_om_id = 25433(3422 rows). This is
just seems really weird to me!

Thanks Michael L. Horne

>
> > 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);?
>
> Only if it has an better selectivity.