> > > 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?
>
> Well, the problem is that the system doesn't think that it's the best.
>
> Is the combination of OD_OM_ID + OD_SEQNUM unique? If so, create a
> unique index, this will help the optimizer to recognize that OD_OM_ID +
> OD_SEQNUM has the same selectivity as OD_OM_ID + OD_ID.
>
> By the way (penny just dropped) why do you have an index on OD_OM_ID +
> OD_ID?

OD_OM_ID contains the primary key of the Master record, that the detail
records go with. OD_ID is the primary key of the Detail records. I assumed
that it would be better to have it set like this because I do a lot of
selections from the detail file to retreve the records for a Master. Am
I wrong in that assumption?
Thanks

>
>
> > it read all the rows that had od_om_id = 25433(3422 rows). This is
> > just seems really weird to me!
>
> The engine always has to read all rows from an index to check if the row
> referenced by the index is 'valid' for the current transaction. Index
> are updates outside transaction control, so the results need to
> "validated" in the current/requesting transaction context.
>
>
> Sean
>