Aaron Abend wrote:

>
> Neither Oracle nor Sybase/MSSQL support more than one index per table
> reference per query (in general). The biggest difference with Firebird is
> that it will use more than one index. I am still trying to figure out how to
> use this capability effectively.

In theory, you don't have to. The easiest way to take advantage of
Firebird's ability to use multiple indexes is to get rid of artificial
compound indexes. Rather than creating an index on VALUE, ID, CATEGORY,
and another on VALUE, CATEGORY, ID, and another on ID, VALUE, CATEGORY,
and another on ID, CATEGORY, VALUE, and another on CATEGORY, VALUE, ID,
and yet another on CATEGORY, ID, VALUE, put one index on each field and
let Firebird combine them as appropriate.

In Firebird 1.x you may end up creating those indexes as combinations of
the field and the record's primary key to avoid problems with duplicates.

>
> Let's take a simple example: a table with 50,000 records which has a VALUE
> and an ID (like a state table but much bigger). Sometimes, I will query on
> the VALUE and look for a list of matching IDs that will be used to retrieve
> VALUEs from another table by a join. In this case, I would index (VALUE, ID)
> to drive the query from the VALUE to the ID, and possibly allow the query to
> use just the index, since the index would contain all of the data required
> for the query.

That's not going to work well in Firebird, because of the
multi-generational index situation. The fact that you get a VALUE/ID
pair out of the index doesn't guarantee that the pair represent
legitimate data for your transaction. So, for each matching VALUE entry
in the index, Firebird has to read the corresponding record. You're
better off (denser index, potentially more flexible) just having
separate indexes on VALUE and ID.

> In other queries, I would be using this same table as a
> lookup to display appropriate values for a list of IDs, again through a
> join. For this purpose, I put another index on the table, (ID, VALUE). The
> cost of the two indexes is worth the price of inserts based on our
> benchmarks and we get good performance in SQLServer. It seems that in
> Firebird, singleton indexes on ID and VALUE might be best, since as point 4
> below makes clear, database versioning would require the table to be read
> anyway.
>
> Is this analysis correct?

Yup.

> Now it is my understanding
> that Firebird does not support clustered indexes. Is this true?

Right. Absent a lot of deletions, records are stored on data pages with
the oldest on the first pages and the newest on the most recently
allocated pages. Space from deleted records and back versions of
modified records is reused, so that ordering absolute. Furthermore,
when a data page is completely emptied, it goes back into the free page
pool and can be reused as a data page for any table or any other type of
page. Relational databases aren't supposed to work like flat files - you
shouldn't have to worry about record locations...

Regards,


Ann