Table Access Strategy

Optim™ solutions use one of two methods to access the tables in each relationship: table scanning and Optim primary key lookup. By default, Optim solutions choose the table access method automatically at run time. To optimize performance, you can force the use of a specific access method for a specific table relationship and increase the maximum number of simultaneous key lookups.

Choosing the Table Access Method

In general, Optim solutions use an Optim primary key lookup when a relational database index is available and Optim solutions use a scan when no index is available. However, to access a significant portion of the table, a scan might be used, even if an index exists.

Use the Process Report to determine which table access method performs better. If most rows in a well-indexed table are to be processed, a full table scan can be more advantageous than the default key lookup. When database constraints such as primary keys are not defined, the creation and use of Optim primary keys can optimize performance for extract and delete processes.

Optim primary keys are objects that are local to Optim. Primary keys can therefore represent a non-unique column in table, which can affect performance, and can result in multiple rows being processed. For example, in a delete process, a primary key might lead to the comparison of every column value, which would degrade performance and might result in multiple rows being deleted.

Table Access

For an extract or archive process, you can override the default method for accessing the parent or child table for each relationship (that is, scan or key lookup). For a delete process, you can override the default method for deleting rows from each table.

Default
Optim determines whether to use scan or key lookup.
Force Key Lookup
Use a WHERE clause to search for primary or foreign key values.
Note: To verify that a DBMS index exists, click Tools > Relationship Index Analysis in the Access Definition Editor. In the Delete Request Editor, right-click a table name in the grid and click Analyze Primary Key Index for this table to open the Primary Key Index Analysis dialog. For more information, see Index Analysis.
Force Scan
Force Optim to read all rows in a table at one time.

Access Definition Editor

Use the Table Access and Key Lookup Limit grid columns on the Relationships tab of the Access Definition Editor to modify the default access method for an archive or extract process. These grid columns are displayed automatically if either contains a value other than the default. If the columns are not displayed, click Options > Advanced Options to display the Table Access and Key Lookup Limit grid columns on the Relationships tab.

Relationships tab in the access definition editor

Table Access Strategy Dialog

In the Delete Request Editor, click Tools > Edit Strategy to display the Table Access Strategy dialog with the Access Method and Key Lookup Limit grid columns.

Table Access Strategy dialog

Key Lookup Limit

The maximum number of key lookups performed at one time for a table. Valid values are 1 through 100. By default, Optim looks up one key at a time.

Increasing the Key Lookup Limit might significantly improve performance. For example, enter 5 as the Key Lookup Limit for a single column key to search five key values in a single request to the DBMS. Generally, increasing the Key Lookup Limit to the maximum (100) is most effective.

Editing the Key Lookup Limit column for a table in a delete process requires:



Feedback