Redefined rowids are columns of any data type, up to 64-bytes in length
(version 4.0 and later), that are to be used as a native rowid. Redefined
rowids are only needed when no native rowid exists in SQL Server and DB2.
Redefined rowids can be used on any table to ensure a well-behaved id,
sequentially ascending from 1, but are only necessary when the table does
not have a native rowid.
If the table already has a unique column that is less than 64-bytes and
is supported by a native unique index, this column is the first choice
for a redefined rowid. Primary keys often fit this category.
If no such column exists, then add an identity column.
In all cases, the redefined rowid must be supported by a unique index
in the native database.
Environment Catalog Syntax for Redefined Rowids
USAGE [ASCENDING] [AUTO] ROWID
- If the value of the redefined rowid is sent in the INSERT statement,
use USAGE ROWID
- If the value of the redefined rowid is automatically assigned by the
native database, either through a trigger or through an identity column,
use USAGE AUTO ROWID
- If the value of the redefined rowid is 100%
guaranteed to be sequentially ascending from 1, then
add the ASCENDING keyword. USAGE ASCENDING
ROWID or USAGE ASCENDING AUTO
ROWID
Omnidex Versions 3.7 and 3.8
Redefined rowids are advisable on nearly all tables in relational databases.
They provide well-behaved ID's for Omnidex bitmap operations as well as
a unique identifier for performing database retrievals needed with the
retrieval engine.
For all databases, if there is already a sequentially ascending 4-byte
integer field supported by a native unique index, then that is the first
choice for a redefined rowid.
If no such column exists, then add an identity column. This approach
differs slightly by database platform:
- Oracle and Informix - add a column that is populated by a trigger using
a sequence
- SQL Server and DB2 - add an identity column
- In all cases, the redefined rowid must be supported by a unique index
in the native database.
Top
|
