|Subject||RE: [ib-support] Problem with plan selection - doesn't seem right (Resubmit)|
|Author||Michael L. Horne|
> > how can this possibly be the best index? Michael is selectingThis sounds even stranger, since:
> 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.
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
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
> > CREATE DESCENDING INDEX ORDERSDBYSEQNUM ON ORDERSD (OD_OM_ID, OD_SEQNUM,
> > OD_ID);?
> Only if it has an better selectivity.