Subject Re: [ib-support] SELECT COUNT(*) ... - slow
Author Ann W. Harrison
At 06:04 PM 4/5/2002 -0300, Lista de Discução wrote:

>When FB count the records when you give a "Select count(*) from aTable"
>The engine needs to scan ALL data pages that belongs to that table right ?


>BUT IF it's only scans the Index Pages (choose the smallest index) so the
>engine will reach a bigger number of record on each page, therefore it will
>scan a small number of pages, I think it could improove the speed.

At the cost of accuracy. There's no way to tell whether an index
entry corresponds to a record that's valid for the current transaction

>And if we send a "Select count(*) from aTable where Name like 'John%'" the
>engine will seek for John's on index pages then go the datapages to count
>the data, or will only count the record from the index references ?

No, it will find all the John% index entries and look-up the
records to find the ones that are valid for the current transaction.

>I have read once that Oracle only read the indexes if the indexes already
>contains all the information the select wants, does FB work i the same way ?

No, because a multi-generational index contains entries for all
record versions, not just those that apply to the current transaction.


We have answers.