"fabiano_bonin" <fabiano@...> wrote:

>
> When indices statistics are updated?

1) when an index is created
2) when an inactive index is activated
3) when SET STATISTICS is issued

> Plan
> PLAN (A ORDER IDX_TEST)
>
> Why the optimizer uses the index with lower selectivity in the case
> above, if in the where clause i'm searching the primary key?

IDX_TEST is used for sorting. In this case (when ORDER plan is used) all
IB/FB versions prior to FB 2.0 don't report you indices (if any) used to
filter the result set, although they are in fact used. With FB 2.0, you'll
see:

PLAN (A ORDER IDX_TEST INDEX (PK_TEST))

which explains the internal activity much better.

> I'm having performance problems with tables with milion records, when
> i search in a column which has an index with high selectivity but the
> sort is made in a column which has an index with low selectivity.

An index PK_TEST is scanned to evaluate your WHERE clause. The result of
this scan is stored in the intermediate sparse bitmap. Then the engine
performs a navigational scan on IDX_TEST, trying every record number against
the bitmap. So you get a full index scan for IDX_TEST, even if the bitmap
contains a single value. This is a reason of your performance issues.

As a workaround, I'd suggest to order by dummy expression (<col> + 0), this
will disable any possible index navigation and force a SORT plan instead. It
should be much better for you, as the filtering is performed before the
external sorting.


Dmitry