Lista de Discução Interbase 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.

I don't think it is possible to see which records can be seen by your
transaction just by looking at the index pages.
For the most part, 'select count(*) from ATABLE" with no where clause is a
(bad) habit that can be dropped (IMO). Anyway, the answer you get is not
guaranteed to be valid for more than an instant and each transaction may
see a different number of records.

> 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 ?

Again, I think looking at the records (record version) is necessary.

> 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 ?
> So if you have a Select Name from aTable where Name Like 'John%' only
> trough a indexes search Oracle returns all names, I think it's a very good
> way to optimize page reads.
> This aproach will be great in situations like that
> Select Max(Item) from Order_Items where OrderID = X

"Select Max ..." is helped by a descending index.
"Select Min ..." is helped by an ascending index.



Regards,
Aage J.