Thanks to everybody,

Nico


-----Oorspronkelijk bericht-----
Van: Ann W. Harrison [mailto:aharrison@...]
Verzonden: vrijdag 18 mei 2001 0:44
Aan: ib-support@yahoogroups.com
Onderwerp: Re: [ib-support] PLAN

>Nico wrote:
> >
> > > I've created a query that is making a SUM of some records.

As others have said InterBase will use an index if the record selection
expression (where and join clauses) include indexed columns. Thus

select sum (price) from everything

does not use an index.

select sum (price) from everything

will use an index on location if one is available.

select sum (e.price)
from everything e
join nothing n on e.thing = n.thing
where e.location between 'here' and 'there'

will use an index on everything.thing or nothing.thing if one
exists. It will also use the index on location, again assuming
its existence.

select sum (price) from everything

will NOT use an index on price.

select sum (price) from everything
where price between 1 and 10

will use an index on price to identify candidate rows. Interbase
can not compute the sum without looking at the data.

Ivan Prenosil wrote:

> > it is not possible to read these values from index
> > (because of MGA architecture).

If there were an index on price, other systems could optimize
the aggregate by reading the index rather than the data. InterBase
can not do that because the index is only a loose representation of
the data available to the transaction. An index contains an entry
for every value of that column, including values that are stored in
rows that have not been committed and back versions of rows. That's
where the effect of the multi-generational architecture bites you.

At 12:10 AM 5/18/2001 +0200, Frank Ingermann wrote:

>pls correct me if i'm wrong, but in my understanding
>
>a) sum() needs to visit *all* the records to be summed, order doesn't

matter

That's right. It needs to visit all the records - it can't just read the
data from an index on the column being aggregated.

>b) in this case, traveling the records in natural order should be the

fastest

> (avoiding page jumps you'll get trying to walk an index)

InterBase does not walk indexes unless the query includes an order by clause
that matches the index. Instead, InterBase reads the index, creating a bit
map
of qualifying records which are kept in page order. When it finishes
reading
the index, it then retrieves the records in natural order.

>the choice
>of an index (or no index) should not influence *what* records are fetched,
>but
>only the order (i do hope so :)

Except for a couple of pathological cases, that's true. And generally using
an index reduces the number of pages read without causing retrievals to jump
all over the disk.


Regards,

Ann
www.ibphoenix.com
We have answers.


To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/