Subject Re: [firebird-support] firebird 2,0 and index optimization
Author Helen Borrie
At 02:34 AM 14/06/2007, you wrote:
>Does firebird 2 use indexes more functionally and fast with aggregate

Fb 2 improves the way index statistics are used on composite indexes.

>I use views with aggregate functions.
>If i have a view that uses a group by function by id and i want to read a
>record from this view,
>does optimizer scan all records for calculating the data and build all the
>view or optimizer scan only records that i want from it.

As Ann said, the optimizer doesn't operate on records: it considers
indexes and statistics and prepares a plan for the engine. But I
think possibly the question you want an answer to (forgive me if I'm
wong) is whether the engine applies the WHERE clause to filter a view
before aggregating, as with tables, using any indexes that are
useful; or has to form the unfiltered set first and operate without
the benefit of indexes for searching and aggregating, as with
selectable stored procedures.

The answer is the first: for views, the engine operates on "live"
data streams and the optimizer can provide indexed operations if they
would be useful. The trick is to ensure that the underlying tables
have indexes that remain useful for the filtering, aggregating and
ordering you want to do. Some view specifications might be so
abstruse as to perform no better than filtering and aggregating a
"dead" set from a SSP.

On the other side of the equation, if your aggregating criteria are
not going to vary, it can be better for performance to bypass the
view and use a SSP to extract the exact set you want, using input
parameters to determine the filter values.