Hi Ann & Alex,

Thanks for the URL.
Basically Jim said the optimizer is broken, and now we're mostly using
rule-based. CMIIW. So does it actually help if we calculate the index
statistics for cost-based optimizer?

> >But if it's cost-based, where's
> >the statistics?
>
> The selectivity is kept in the index header on the index root
> page. By default, it is updated only when the index is rebuilt -
> activate/deactivate, drop/create, or backup/restore. you should
> set the statistics regularly - say just after a regular backup -
> and also after any operation that might change a significant percent
> ( > 10) of the indexed values.

Ann, please help me to understand it a bit more. Does backup (gbak) will
automatically update the statistic? Or should I run "SET STATISTICS INDEX
..." or "ACTIVATE/DEACTIVATE" after my daily backup?

> The cardinality is estimated by the record size and the number
> of pointer pages for the table. A pointer page is (surprise) a
> page of pointers to data tables. It may go as far as to check
> the actual number of data pages by reading the pointer pages,
> I'm not sure.

Ann, thanks for the info. I have a better idea now how the cardinality is
calculated.

regards,
Sindu