Predicate Indexes and Joined Searches

Many special restrictions and considerations exist when using predicate indexes on a joined table. A parent table can have at the most one primary index. As it is generally not advisable to have more than one primary index on a table, this is not a significant restriction. Apart from this restriction, predicate indexes on a parent table can be used as on any other table. A child table, however, has a number of significant restrictions on the use of predicate indexes.

Internally the join relationship between a child table and its records, and the parent table and its records, is represented as a primary index on the child table. This is similar to a primary predicate index on the child table. Essentially, a child table starts with one primary index. So, all the caveats associated with a second primary index apply to the first primary predicate index on the child table:

•

The first primary predicate index on a child table incurs a large memory penalty, almost doubling the memory requirements for the table.

•

As only one primary index at a time can be used, and as the join search requires the join index be used, a primary predicate index on a child table cannot be used in a joined search. It is applied as a secondary index.

A primary predicate index should be placed on a child table only if:

•

The extra memory costs can be handled.

•

There are many standard (non-joined) queries against the child table that can gain a major performance boost by using a primary predicate index.

Otherwise, a primary predicate index should not be put on a child table.

To obtain the performance boost available from a primary predicate index in a joined search, the partitioned field must be in the parent table. This should be considered while designing the tables. If you want to partition by state code, the state code should be in the parent record for the joined set of tables if possible.

A partitioned index works by allowing the search to skip over the partitions that do not apply. In a joined search, the partitioning applies to the parent table and to all its child tables. Using a partitioned index on a parent table you can skip over blocks of the parent table, as well as all of the associated blocks in the child records. Therefore, in a joined search, the performance boost from using a primary index on the parent table can be even larger than one would expect. As joined queries can be more expensive than a standard query, it is even more important to look for the possibility of using a filtering predicate and a primary index when performance is critical.