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.
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.
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.
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 to display the Table Access and Key Lookup Limit grid columns on the Relationships tab.

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

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: