Optimizing with keys

This topic includes the following sections:

 

Optimization starts with the design of your database and repository (see Setting up a repository). For xfODBC to quickly process SQL statements, the data files and the repository should have well‑chosen keys that reflect the way users access data.

What are keys?

A key is a portion of a record structure that individually identifies records and enables records to be quickly accessed and sorted. For ISAM files, a key can be a portion (segment) or a group of separate portions of the record structure. For repositories, a key can be a single field or a group of fields. The fields that make up a repository key are also called segments.

Keys are created at two different points in your database’s development: when you create the data files and when you define the repository for those files. When you create an ISAM file, for instance, you define the primary key and any secondary keys for that file. When you create a relative file, the record number is automatically defined as the primary key (this is the only key that can be defined for a relative file). ASCII sequential files, on the other hand, don’t have keys.

In addition to defining keys when you create data files, you also define keys when you define the repository that describes your data files. At this point, there are two types of keys you can create: access and foreign. Access keys correspond to the keys you created when you created the data file, are used to locate and sort records, and can be used to define relationships between tables. Foreign keys, however, are not keys in the data file, but can be used to create relationships between tables. (Note that foreign keys are useful only with ODBC‑enabled applications that support the ODBC API function SQLForeignKeys.)

For information on defining keys for ISAM files and relative files, see Synergy DBMS.

How xfODBC uses keys

As xfODBC processes an SQL statement, it looks to the database’s system catalog for indexes (keys) it can use to speed the processing of the statement. To determine which key to use, xfODBC evaluates two types of SQL clause: restriction clauses and sort clauses. Restriction clauses include WHERE, HAVING, and JOIN clauses. Sort clauses include ORDER BY and GROUP BY clauses.

To evaluate a restriction clause, xfODBC attempts to match the columns (field names) in the restriction clause with the key’s segments. To evaluate a sort clause, xfODBC matches the sort clause’s order (ASC or DESC) with the key’s segment order and matches the columns in the sort clause with the key’s segments. If no key can be used with the sort clause, xfODBC will then create a temporary table sort, which results in many more I/O operations and poorer performance.

Note the following:

If you use xfODBC with inner joins that result in temporary indexes, note that your data files must use static RFAs if update, store, or delete operations will occur while the xfODBC driver is using a temporary index to access a file.

Note that if a “Key of ref” value is specified for a key in a structure, it should be explicitly set for all keys in that structure. Otherwise dbcreate will report an error indicating there is a duplicate key of reference.

Indexes for overlay segments

If a structure has a key with an overlay segment, dbcreate creates an index that includes the non‑overlay fields (i.e., the fields that the segment overlays) if the overlay field

This index is named as follows in GENESIS_INDEXES: KeyName_GENIX_n (where n is an ascending numeric value). For instance, if a key has an a22 segment that overlays four fields (as in the following example), dbcreate creates an index consisting of the four fields (custid, date, code, and text) in addition to an index consisting of seg1.

record
    custid   ,d4
    date     ,d6
    code     ,d2
    text     ,a10
record  ,x
    seg1     ,a22    ;key

This would look like the following in GENESIS_INDEXES:

KEY1

KEY1_GENIX_0

If a structure has a tag index and a key named KEY_0 that overlays two fields, dbcreate will create the following indexes:

$_VTX_TAG_VIX_0001

KEY_0

KEY_0_GENIX_0

Note, however, that if a field is only partially included in an overlay, that field will be omitted from any GENIX index created for that overlay.

On the other hand, if multiple fields overlay a key segment defined as a primary field (the overlaid field)—the inverse of the first example—dbcreate will not create an index for the overlaying fields. Instead, it will create a single index for the key segment. For example, if four overlay fields—custid (a d4), date (a d6), code (a d2), and text (an a10)—overlay an a22 segment (as in the following structure), dbcreate creates only one index, an index for the a22 key field.

record
    key1     ,a22     ;key
record  ,x
    custid   ,d4
    date     ,d6
    code     ,d2
    text     ,a10

In this case, xfODBC won’t use an index for a statement with a restriction or sort clause for the custid, date, code, or text fields.

Finally, if multiple overlay fields are defined as a key (as in the following example), xfODBC won’t be able to use the primary field (the overlaid field) for optimization. For the following example, xfODBC won’t be able to use the key1 field for optimization.

record
    key1     ,a22
record  ,x
    custid   ,d4     ;key segment
    date     ,d6     ;key segment
    code     ,d2     ;key segment
    text     ,a10    ;key segment

Defining keys

Keep the following in mind when defining keys:

Note the following:

Keys with literals

A key can have a literal segment in any position in the key. This means that for foreign keys, a literal can be used to correspond to a literal tag in a related table. For example, in the sample database included with the Connectivity Series distribution (the repository is in connect\synodbc\dict), the literal at the beginning of TAG_KEY_VEND in the ORDERS table enables a relation to be created between TAG_KEY_VEND and TAG_KEY, as illustrated in figure 1. Without this literal, we couldn’t use the tag for the VENDORS table, and the keys wouldn’t correspond. We need an equivalent segment as the first segment in TAG_KEY_VEND.

1. Keys with literals in the sample database.

Keys with literals in the sample database

If the ORDERS and VENDORS tables had the following values there would be matches for all the rows in the ORDERS table except the row with the OR_VENDOR value of 42:

2. VENDORS table matches for the ORDERS table.

VENDORS table matches for the ORDERS table

Likewise, all but one of the rows in the VENDORS table would have a match. The row with the VEND_KEY value of 40 wouldn’t have a match because there is no row in the ORDERS table with an OR_VENDOR value of 40 and because this row has a VEND_R_TYPE value of 2, while the corresponding literal segment in TAG_KEY_VEND is always 1.

Tags and optimization

Keep the following in mind when defining tags or creating keys for a file with a tag:

If your repository has a set of keys that either do not include the tag or do not include it as the first segment, create a new key with the tag as the first segment, and then use the Repository option “Excluded by ODBC” to exclude similar keys that either have the tag in other positions or that do not have the tag at all. (For information on this Repository option, see Defining keys.)

Similarly, if your repository has two or more keys that are identical except for the inclusion or placement of a tag field, and one of the keys has the tag as the first segment (as we recommend), do one of the following to ensure that the key with the tag as the first segment is used for optimization: