|Subject||RE: [ib-support] Problem with plan selection - doesn't seem right (Resubmit)|
|Author||Michael L. Horne|
> > > An index for MAX() is used when the ORDER of the query isOD_OM_ID contains the primary key of the Master record, that the detail
> > 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 +
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?
> > 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.