Database keys (dbkeys) are internal pointers to specific table
rows in a database. Application programs can use the DBKEY or
ROWID keyword in SQL statements to refer to the database key for
a table row. The ROWID keyword is a synonym to the DBKEY keyword.
Database keys are considered value expressions. As such, they can
be specified as part of a select expression.
SQL statements that retrieve rows by specifying their database
keys have the following advantages:
o Fast access: Retrieval through database keys is direct and
bypasses any indexed or sequential searches.
o Reduced locking of data: Because access is direct, the
database system locks only the row retrieved or updated.
o Uniqueness: Within the database key scope specified in the
CREATE DATABASE or DECLARE ALIAS statements, database keys
are guaranteed to be unique. This means singleton SELECT
statements based on database keys will never return more than
a single row, and that they will return the same row, or an
error if the row was deleted.
The scope of a database key refers to how long the database
system guarantees that a particular row's database key will
point only to that row and not be used again even if the row
is deleted. In ATTACH, CREATE DATABASE, DECLARE ALIAS, and IMPORT
statements, you can specify that the database key scope be for
the duration of a transaction (the default) or for the duration
of an attachment to the database.
Applications that plan to use database keys across transaction
boundaries should declare databases with the DBKEY SCOPE IS
ATTACH clause, which allows the program to use a database key for
a particular table row over the course of many transactions. If
another user deletes the row, the database key will not be used
again for a newly inserted row, ensuring that the database key is
still valid. Any further reference to that DBKEY wukk generate an
error.
When you use the DBKEY, some space on the page is not reclaimed
until all users are using DBKEY SCOPE IS TRANSACTION and the page
is updated. Also, see the RMU RECLAIM command which allows online
reclaiming of this space.
NOTE
Oracle Rdb recommends using DBKEY SCOPE IS TRANSACTION to
reclaim space on a database page faster than if you use
DBKEY SCOPE IS ATTACH.