At 04:41 PM 22/11/2004 +1100, you wrote:

>Hi,
>
>Just trying to get a better understanding. I'm an Oracle hack, is there
>any requirement in Firebird for gathering statistics to aid plan
>optimization or are statistics gathered automatically or does Firebird use
>a rule based optimizer?

The answer is "Yes-ish". :-)

Requirement - no. The Firebird optimizer uses cost analysis of sets as
well as indexes in preference to slavish rules that depend on constantly
updating statistics; yet it is worth your while to watch the index stats,
both in pre-deployment testing and in production, to ensure that indexes
are working well. It's a very good way to discover indexes that cause
problems, either inherently or because of unbalanced growth.

Index selectivity is recalculated at database startup (i.e. when the first
user logs in to a DB that previously had no attachments) and maintained in
the system table RDB$INDICES. You can also cause the stats to be
recalculated by running a SET STATISTICS command from isql. These figures
are useful to the optimizer but in fact are not read often - really only at
start-up (Arno will put you exactly straight on that one). Cached index
pages are also used by the optimizer in ways I don't understand clearly
(this is one of Ann's party pieces).

Pavel Cisar did a presentation at the Firebird Conference on the inner
workings of the optimizer. It has morphed into a white paper, entitled
"Understanding the Firebird Optimizer and the Execution Plan", that is
available on the forthcoming IBPhoenix CD.

Sorry I can't give you the total answer, but at least I dropped a few names
there for you to browse in the archives. :-)

./hb