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.
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.