Delete by RowID Tab

The Delete by RowID feature enables you to use the Oracle hidden row identifier to locate and delete rows. This option can yield a significant performance improvement when you are deleting from a table with no database indexes, or if indexes for the table do not match the delete criteria. In such cases, deleting by Oracle RowID avoids the need for Optim to scan a table to locate the desired rows.

Oracle RowID is an internal row locator and is subject to change over the lifecycle of a row. Running database utilities or updating a row changes that row’s RowID. For this reason, if you use Delete by RowID, minimize the time between extracting the RowIDs and then using the RowIDs in a delete operation. This practice reduces the risk of invalid RowIDs. Use this feature if:
  • You are experiencing poor performance during delete
  • The table you need to delete from has no indexes
  • The table you need to delete from has an index that does not match the delete Where criteria

You can run a delete request immediately after the extract operation and ensure that no database activity that would invalidate the RowIDs is taking place.

Do not use this feature if:
  • Database reorganization or database utilities are running, as these invalidate previously extracted Oracle RowIDs
  • Row Data extracted previously is likely to change between the time it was extracted and the time it is deleted
  • You have existing indexes that can be used during the Delete operation.
Note: If you are deleting from tables with indexes, you gain no performance benefit by using Delete by RowID.
Delete by RowID tab of Delete Request Editor, explained in this section
The Delete by RowID tab displays only if this feature is enabled in your site product options. This tab has the following fields:
Enable Delete by RowID
Select this check box to enable Delete by RowID. It is cleared by default.
Threshold Percentage
Specify the percentage of invalid rows that, if this value is reached, causes one of these actions.
When Threshold Reached
Action to be taken if the number of invalid RowIDs equals the value specified for Threshold Percentage. The drop-down list has these choices:
Stop
Terminate the delete process. The Delete Process Report Error section includes a message that the process terminated.
This situation can occur when one or more rows cannot be deleted because RowIDs in the source file do not match the RowIDs in the database. To process the delete request, you can:
  • Increase the Percentage Threshold on the delete request
  • Disable the delete by RowID feature (clear the Enable delete by RowID setting for the Delete Request)
  • Select Continue rather than Stop for On RowID Validation Failure
  • In the Product Options on the Database tab, set Allow DML Operations by RowID to Never
After changing the appropriate settings, you can restart the delete process.
Note: Other delete strategies may be slower than delete by RowID depending on the availability of unique indexes.
Continue
Delete process continues, but Optim uses the default delete strategy: Array or Multi Key.


Feedback