Adam wrote:

>>
>> We are trying to fetch records from a 24 Million record table, and in
>> order to avoid the fetch from both index and the actual table, we
>> included the required non-key field in the index so that the Firebird
>> database does not read the table but fetches data from the index.
>
>
> Indices in firebird are treated differently than many other databases.
> Data is stored in an arbitrary position, not ordered by PK.

Let me clarify that. Some databases cluster records based on primary
key, effectively storing the data as the lowest level of the the
primary key index. Firebird does not. Data and indexes are stored
on separate page types. But that's not what the original author was
asking. Some databases resolve queries from the index without
reference to the data.

As an example, consider a database of courses, students, and
registrations. One index on the registration table - not
necessarily the primary key - contains the student id and the
course id. If that index also included the grade, you could
get grades without reading the registration data.

Firebird does not support that optimization because its indexes
are noisy - they can contain data that is not appropriate for the
current transaction. This is a tradeoff in the implementation of
MVCC. By adding two transaction ids and increasing the number of
index entries per record, we could handle these queries, but every
other query would be slower because the minimum index entry would
be nearly twice as long as it is now.


Regards,


Ann