Delete Performance Enhancements

Resolving The Problem

Delete Enhancements

Optim Release 7.2.1 includes the following features to improve delete performance for certain use cases. If you are deleting from a table whose Optim primary key has a non-unique index or no index, you can delete without comparing rows. Optim can then take advantage of DBMS features such as array or multiple key delete, which can further improve performance.

Delete without Row Comparison – For each table in a Delete Request, you can specify whether the row contents are compared before deleting the row. You also have the option to exclude LOB columns from comparison. Turning off row comparison can yield significant performance gains. However, you may risk deleting updates that occurred after the row was written to the source file. Additionally, for a table with an Optim primary key with non-unique index, the wrong row(s) may be deleted due to the possibility of duplicate primary keys. This may not be a concern for all data models if the application managing the DBMS data either has its own safeguards or the design of the application prohibits duplicated rows.

Array or Multiple Key delete – The DB2 and Oracle databases have features to support array delete. For a database other than Oracle or DB2, Optim can construct a multiple-key Where clause for a single delete call to the database.

Refer to Table 1 for examples of processing options and considerations.

Delete without Row Comparison

Optim now supports row comparison processing options for each table in a Delete Request. You can process the delete without comparing the contents of each row and you can exclude LOB columns from the comparison. These actions may significantly improve performance, though risk of data loss is possible.

In releases of Optim prior to 7.2.1, when processing a Delete Request for a table with a non-unique index Optim primary key, Optim forced row comparison. This was the default, even if you specified not to compare rows. You can now override the default, using the Table Access Strategy dialog. Values you choose on the Table Access Strategy dialog override your specifications in the Delete Request and Optim default behavior. For each table in a Delete Request you can choose whether rows are compared by selecting one of these values:

Default

• For a table with a unique-index Optim primary key, the default is the setting for Compare Row Contents in the Delete Request. If you select the Compare Row Contents check box in the Delete Request editor, Optim compares the content of rows in the source file with rows in the database prior to deletion. Rows from the database that exactly match rows in the source file are deleted, and rows that do not exactly match are discarded and noted in the Control File. If you clear this check box, the row comparison is not performed, and rows that do not exactly match rows in the source file can be deleted from the database . (This check box is selected by default.)
• For a table with a non-unique index Optim primary key, the default is to compare row contents, regardless of the setting for Compare Row Contents in the Delete Request. To override the default and turn off row comparison, use Compare Never.

Compare Always
Compare rows, regardless of the setting for Compare Row Contents in the Delete Request.

Compare Never
Do not compare rows. This option overrides the setting for Compare Row Contents in the Delete Request. This option might improve performance significantly. However, you risk losing any updates to the data in the database since the source file was created. Optim displays a warning message which you must acknowledge before proceeding with the deletion.

Include LOBs in Compare
This option is available only if Compare Row Contents is set to Default or Compare Always. Select this check box to include LOB columns in the row comparison. Clear this check box to exclude LOB columns from the row comparison. Excluding LOB columns might improve performance significantly. However, you risk losing any updates to the data in the database after the source file was created. Optim displays a warning message that you must acknowledge before proceeding with the deletion.

Array and Multiple Key Deletes
The DB2 and Oracle databases have features to support array deletes. For other supported databases, Optim can use multiple primary key values in a single delete call to the database to process the deletion more efficiently. You can initiate a multiple key delete by choosing these options on the Table Access Strategy panel:

Access Method
Select either Default or Force Key Lookup.

Default
Optim determines whether to use a scan or key lookup.

Note: In general, a key lookup is used when a DBMS index is available, and a scan is used when an index is not available.

Force Key Lookup
Force Optim to use a key lookup. Before selecting, you should verify that a DBMS index exists. Right-click a table name in the grid and select Analyze Primary Key Index for this table to open the Primary Key Index Analysis dialog. For details, refer to the Archive User Manual.

Key Lookup Limit
Maximum number of key lookups performed at one time for the table. Valid values are 1 through 100. By default, Optim looks at one key at a time. Note that increasing the Key Lookup Limit can significantly improve performance. For example, if you specify 5 for Key Lookup Limit and the key spans a single column, five key values are searched in a single request to the DBMS.

Note: The following conditions must be true to edit the Key Lookup Limit column for a table:

• An index on the Primary Key is defined for the table.
• Compare Row Contents is cleared for the table.
• Row-level Archive Actions are not defined for the Delete Process (for example, Before Delete of Row).
• The DBMS for the table is Sybase ASE, Informix®, or SQL Server (if SQL Server is not using Array Deletes).
• The table does not have any child tables.

Refer to the table below for information on the delete options and their potential results.

Database	Primary Key Index (non-unique or no index)	Compare Never option set for table?	Archive Actions or Delete File attached?	Key Lookup Limit setting	Delete processing when Access Method is default	Expected result	Risks/Warnings/ Comments
Oracle or DB2	non-unique index	Yes	No	N/A	Array delete used	All rows deleted	Additional rows with same Primary Key may be deleted.
Oracle or DB2	non-unique index	Yes	Yes	N/A	Cursor controlled individual Primary Key delete	All rows deleted	Wrong rows with same Primary Key may be deleted.
Database is NOT Oracle or DB2	non-unique index	Yes	No	>1	Multiple key delete	All rows deleted	Additional rows with same Primary Key may be deleted.
Database is NOT Oracle or DB2	non-unique index	Yes	No	=1	Individual Primary Key delete – NOT cursor controlled	Only first row in the duplicate Primary Key table is marked as deleted; other rows with the same Primary Key will be marked as "Not Found".	Additional rows with same Primary Key may be deleted.
Database is NOT Oracle or DB2	non-unique index	Yes	Yes	N/A	Cursor controlled individual Primary Key delete	All rows deleted	Wrong rows with the same Primary Key may be deleted.
All databases	no index	Yes	Yes	N/A	Cursor controlled delete using table scans	All rows deleted	Wrong rows with the same Primary Key may be deleted.
All databases	no index	Yes	No	N/A	Individual Primary Key delete–NOT cursor controlled	Only first row in the Duplicate Primary Key set is marked as deleted–other rows with the same Primary Key will be marked as "Not Found"	Additional rows with the same Primary Key may be deleted.