Mitch,

> > 1 - Do you have an index on Deal EditDate? (This could helps get

rid of

> > the NATURAL scan)
> >
> >
> I do. It was this index that I wanted to use.

What PLAN is generated if:

1 - you move the "and C.Id=(Select Max(C2.ID) from discussion C2 where
C2.deal_id=d.id)" criteria from the JOIN to the WHERE.

2 (or) - change the "and C.Id=(Select Max(C2.ID) from discussion C2
where C2.deal_id=d.id)" criteria to "and C.Id=(Select Max(C2.ID) from
discussion C2 where C2.deal_id=d.id+0)" criteria

I found that the v1.x optimizer had a number of issues with choosing the
best index.

These 'tricks' might get the Deal EditDate index to come into play, and
thus reduce the number of tests which the other criteria would invoke.

> > 2 - You should create a DESCENDING compound index in Discussion,
> > combining Deal_ID + ID). The optimizer should choose this index to
> > resolve the MAX() criteria.
>
> I thought of that. However, there should be few rows per "Deal", so,
> not sure that would be a tremendous help, but is certainly worth a

try.

It think you would be very surprised, this particular index (the
DESCENDING index) would reduce the number of fetches per deal to 1 --
since the first indexed read for each Deal would yield the highest ID
value.

> > 3 - You should create compound indexes on Attachments and

Discussions

> > (combining DealID + CreateDate). The optimizer should choose these
> > indexes over the current approach of trying to create a bit map by
> > combining the separate indexes (FK_DISCUSSION_1/IDX_DISCUSSION_2 and
> > FK_ATTACHMENT_2/IDX_ATTACHMENT_1).
>
> another goo suggestion. Thanks, I'll try it.

I would be beneficial if you could make one change at a time and post
the PLAN and performance numbers, rather then doing all the changes in
one step.

It would give a better sense of the impact of each change.


Sean