Nuno Souto <nsouto_at_optushome.com.au.nospam> wrote in message news:<3cecc7e8$0$15147$afc38c87_at_news.optusnet.com.au>...
> In article <f369a0eb.0205221207.64d0538_at_posting.google.com>, you said
> (and I quote):
> >
> > Could be very insignificant unless in a CPU-starved mode. With index
> > compression tipping the I/O balance this is even less of an issue now.
>
> Exactly. I'd rather have a concatenated key that helps for aggregate
> queries such as the one below than one that might help me on 1 or 2 I/Os
> here and there and nothing for aggregates.
>
> > > select status,sum(cur_amount)
> > > from agltransact
> > > group by status;
> > >
> >
> > A good example, allows Oracle to use FFS when you have a concatenated index.
> > This type of quries should be anticipated. Seen too many of them.
> >
>
> With or without the least->most order of concatenation, the FFS may be
> picked up. With the difference that least->most will be considerably
> faster (sometimes by various orders of magnitude, particularly with
> Peoplesoft) for ANY aggregate query such as this. I'd rather pay a small
> price for some single row queries and have the benefit of the much faster
> weekly or EOD queries/updates.
FFS was not available when I worked with PeopleSoft. But what I see from other
applications is that performance for aggregations on columns pulled by FFS is
about the same however you order the columns in the index. My thought is that
it's because result sets from FFS are not sorted.
Received on Thu May 23 2002 - 10:25:37 CDT
