Subject Re: Max() in view does not use index
Author csswa
Hi Bob.

Yes, I recreated your table, view, and indexes and I now see your

It seems to have something to do with the GROUP BY in the view. If I
remove it from the view DDL and put it into the sql select, the
indexes are used properly. So long as the GROUP BY is in the view,
it fails to use the indexes as expected.

I've used GROUP BY in views before and indexes/plans behaved properly
so I have no idea what's going on here. Hopefully someone will throw
some light on it.

I tried a couple of test variations, but none helped me pinpoint the

* I discarded the max field;
* I added a primary key column;
* I reduced the group by clause to contain only indexed fields;


Please keep hammering at this because I too would like to understand
what is happening there.

Andrew Ferguson
-- Half art, half science, half empty.

--- In ib-support@y..., Bob Murdoch <ram5@e...> wrote:
> Andrew,
> At 5/23/2002 04:42 PM, you wrote:
> >MAX requires a DESCending index (default is ASCending), otherwise
> >walks the whole table. Indexes can only go one way with IB. This
> >was discussed just this past week. If you want the whole story
> >type 'index' into the group page search box and have a look at the
> >recent posts.
> Thanks for the advice. However, adding a descending index on
> doesn't make a difference. And as I said before, I run the same
query that
> is used in the view, adding the same where clause that I used when
> selecting from the view, I get indexed reads. That is without the
desc index.
> thanks again for your time,
> Bob M..