Automatic creation of indexes.

    This sounds like magic. It's important to clarify that IB doesn't guess the extra indices you may want to use for performance reasons. It only takes the declaration of a table structure one step further in behalf of you.

Each time you declare a primary key, an alternate key (unique non primary key) or a foreign key, Interbase creates an index in the target table automatically. While this is a time saver, other users think this feature is annoying because the index name in this case is selected by Interbase.

It's important to review the script generated by your CASE tool, because the vast majority of such tools don't know about Interbase and its special features, hence they generate a custom index declaration along with the integrity declaration. Other engines require explicit index creation to enforce declaration of keys. With Interbase, this causes duplicated indices in each table, so the performance decreases. Currently, IB doesn't detect when two indices' definitions are exactly the same so it will happily create as many indexes as you want. In the old IB 4.2, redundant indices that cluttered a model with 70 or more tables had a negative impact on the stability of the database.

You must be aware that in the case of a foreign key, the declaration only creates an index in the dependent table. This is not a problem, because to be linked, the parent table must have a primary or unique key in the field or fields that are referenced and this should have created the needed index in the parent table.

Of course, you're still in charge to define by hand of through your CASE tool the non-unique, auxiliary indexes that you may need for performance reasons.

This page was last updated on 2000-05-26 04:28:46