"laurenz_brein" wrote:

> > Next, there are some good techniques in a MGA system such as
> > Firebird for improving the speed considerably. I wont repeat it,
> > but it is right here
> >
> > (http://groups.yahoo.com/group/firebird-support/message/56457)
>
> Sorry to disagree, and I guess it is also a matter of taste,
> but I am very opposed to introducing redundancy into a database.
> It can lead to inconsistencies. This 'good technique' is what I
> would prefer to call a kludge.

This technique is not built-in into db server, you have to implement it
yourself in your database. Other than that, what problems do you have with it ?
If it was built-in, db server would have to use the same redundancy,
or do you have some better idea how to count rows in MGA ?
Also, what inconsistencies do you have in mind ?

> > Fouthly (and finally), there is one difference in Firebird's index
> > structure that you should be aware of. It is directional, not
> > bi-directional. This allows a greater density, and can still help
> > with most operations, but certainly not all. An index is ascending
> > unless you specifically declare it to be descending. That means
> > that a normal index on a field qty could not help you run the
> > following
> >
> > select max(qty) from tablea
>
> Sorry to be so dense, but I need an explanation for this.
>
> Isn't an index a B*-Tree, so that all you have to do when finding
> the maximum is to descend from each parent node to the leaf node
> that contains the largest values?
> I have always thought that ascending and descending indexes only
> behave differently (that is, efficiently or inefficiently) when it
> comes to sorting the result.
>
> For what I call an index scan, I thought it didn't matter as the
> index can be used to seperate the high values from the low ones,
> no matter whether it is ascending or descending.
> I thought it's just a question of choosing the 'left road' or the
> 'right road' at each index node.

The reason is exactly the same as why Count(*) is expensive in MGA.
I.e. index can contain much more values than your transaction can see.
If you find maximum in index, server has to verify whether your transaction
can actually see the record (because the value can pertain to row
that was inserted but not yet commited, or row that was deleted
but not yet garbage collected, or row whole value was update).
If the found maximum is not visible to your transaction, servers has
to find and try other candidate (i.e. it has to navigate through index
like with sorting).

Ivan