> > No, Sean is right.

Thanks, Arno. It nice to be right sometimes ;-)

> > 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?

> 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