S
Sahil Malik
Okay I might have caught another incorrect MSDN documentation .. but I'm not
sure, so I'll post it here.
So check this out ---
http://msdn.microsoft.com/library/d...en-us/odbc/htm/odbcoptimistic_concurrency.asp
That link says that "To determine whether a row has been changed, its new
version is checked against a cached version of the row. This checking can be
based on the row version, such as the timestamp column in SQL Server or the
ROWID column in Oracle, or the values of each column in the row. "
Now how exactly does ROWID enforce concurrency checks in Oracle?
RowID is not guaranteed to be Unique within a database
RowID can change without the user hitting a "save" as a result of certain
dba like database operations (Reindex/repartition etc.)
RowIDs are guaranteed to stay consistent and not changing over the life of
the row - unless a repartition reindex of a table is done.
Inserts/UpdatesCAN change RowIDs, but they might not necessarily do so.
(The above as per OTN documentation --- pasted at the end of my message).
So how in the world does RowID work the same was as TimeStamp .. as per MSDN
... and as per Sceppa's book? .. please clarify.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
---- LONG COPY PASTE BEGIN -----
Rowids of Row Pieces
The rowid identifies each row piece by its location or address. After they
are
assigned, a given row piece retains its rowid until the corresponding row is
deleted
or exported and imported using the Export and Import utilities. For
clustered
tables, if the cluster key values of a row change, then the row keeps the
same rowid
but also gets an additional pointer rowid for the new values.
Because rowids are constant for the lifetime of a row piece, it is useful to
reference
rowids in SQL statements such as SELECT, UPDATE, and DELETE.
ROWID and UROWID Datatypes
Oracle uses a ROWID datatype to store the address (rowid) of every row in
the
database.
Physical rowids store the addresses of rows in ordinary tables (excluding
index-organized tables), clustered tables, table partitions and
subpartitions,
indexes, and index partitions and subpartitions.
Logical rowids store the addresses of rows in index-organized tables.
A single datatype called the universal rowid, or UROWID, supports both
logical and
physical rowids, as well as rowids of foreign tables such as non-Oracle
tables
accessed through a gateway.
A column of the UROWID datatype can store all kinds of rowids. The value of
the
COMPATIBLE initialization parameter must be set to 8.1 or higher to use
UROWID
columns.
See Also: Oracle9i Application Developer's Guide - Fundamentals for
information about other restrictions on the LONG RAW datatype
See Also: "Rowids in Non-Oracle Databases" on page 12-23
ROWID and UROWID Datatypes
Native Datatypes 12-17
The ROWID Pseudocolumn
Each table in an Oracle database internally has a pseudocolumn named ROWID.
This pseudocolumn is not evident when listing the structure of a table by
executing
a SELECT * FROM ... statement, or a DESCRIBE ... statement using SQL*Plus,
nor
does the pseudocolumn take up space in the table. However, each row's
address can
be retrieved with a SQL query using the reserved word ROWID as a column
name,
for example:
SELECT ROWID, last_name FROM employees;
You cannot set the value of the pseudocolumn ROWID in INSERT or UPDATE
statements, and you cannot delete a ROWID value. Oracle uses the ROWID
values in
the pseudocolumn ROWID internally for the construction of indexes.
You can reference rowids in the pseudocolumn ROWID like other table columns
(used in SELECT lists and WHERE clauses), but rowids are not stored in the
database,
nor are they database data. However, you can create tables that contain
columns
having the ROWID datatype, although Oracle does not guarantee that the
values of
such columns are valid rowids. The user must ensure that the data stored in
the
ROWID column truly is a valid ROWID.
Physical Rowids
Physical rowids provide the fastest possible access to a row of a given
table. They
contain the physical address of a row (down to the specific block) and allow
you to
retrieve the row in a single block access. Oracle guarantees that as long as
the row
exists, its rowid does not change. These performance and stability qualities
make
rowids useful for applications that select a set of rows, perform some
operations on
them, and then access some of the selected rows again, perhaps with the
purpose of
updating them.
Every row in a nonclustered table is assigned a unique rowid that
corresponds to
the physical address of a row's row piece (or the initial row piece if the
row is
chained among multiple row pieces). In the case of clustered tables, rows in
different tables that are in the same data block can have the same rowid.
A row's assigned rowid remains unchanged unless the row is exported and
imported using the Import and Export utilities. When you delete a row from a
table
and then commit the encompassing transaction, the deleted row's associated
rowid
can be assigned to a row inserted in a subsequent transaction.
A physical rowid datatype has one of two formats:
See Also: "How Rowids Are Used" on page 12-21
ROWID and UROWID Datatypes
12-18 Oracle9 iDatabase Concepts
The extended rowid format supports tablespace-relative data block addresses
and efficiently identifies rows in partitioned tables and indexes as well as
nonpartitioned tables and indexes. Tables and indexes created by an Oracle8i
(or higher) server always have extended rowids.
A restricted rowid format is also available for backward compatibility with
applications developed with Oracle7 or earlier releases.
Extended Rowids
Extended rowids use a base 64 encoding of the physical address for each row
selected. The encoding characters are A-Z, a-z, 0-9, +, and /. For
example, the following query:
SELECT ROWID, last_name FROM employees WHERE department_id = 20;
can return the following row information:
ROWID LAST_NAME
------------------ ----------
AAAAaoAATAAABrXAAA BORTINS
AAAAaoAATAAABrXAAE RUGGLES
AAAAaoAATAAABrXAAG CHEN
AAAAaoAATAAABrXAAN BLUMBERG
An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:
OOOOOO: The data object number that identifies the database segment
(AAAAao in the example). Schema objects in the same segment, such as a
cluster
of tables, have the same data object number.
FFF: The tablespace-relative datafile number of the datafile that contains
the
row (file AAT in the example).
BBBBBB: The data block that contains the row (block AAABrX in the
example). Block numbers are relative to their datafile, not tablespace.
Therefore,
two rows with identical block numbers could reside in two different
datafiles of
the same tablespace.
RRR: The row in the block.
You can retrieve the data object number from data dictionary views USER_
OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. For example, the following query
returns the data object number for the employees table in the SCOTT schema:
SELECT DATA_OBJECT_ID FROM DBA_OBJECTS
WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'EMPLOYEES';
ROWID and UROWID Datatypes
Native Datatypes 12-19
You can also use the DBMS_ROWID package to extract information from an
extended
rowid or to convert a rowid from extended format to restricted format (or
vice
versa).
Restricted Rowids
Restricted rowids use a binary representation of the physical address for
each row
selected.When queried using SQL*Plus, the binary representation is converted
to a
VARCHAR2/hexadecimal representation. The following query:
SELECT ROWID, last_name FROM employees
WHERE department_id = 30;
can return the following row information:
ROWID ENAME
------------------ ----------
00000DD5.0000.0001 KRISHNAN
00000DD5.0001.0001 ARBUCKLE
00000DD5.0002.0001 NGUYEN
As shown, a restricted rowid's VARCHAR2/hexadecimal representation is in a
three-piece format, block.row.file:
The data block that contains the row (block DD5 in the example). Block
numbers are relative to their datafile, not tablespace. Therefore, two
rowswith
identical block numbers could reside in two different datafiles of the same
tablespace.
The row in the block that contains the row(rows 0, 1, 2 in the example).
Row
numbers of a given block always start with 0.
The datafile that contains the row (file 1 in the example). The first
datafile of
every database is always 1, and file numbers are unique within a database.
Examples of Rowid Use
You can use the function SUBSTR to break the data in a rowid into its
components.
For example, you can use SUBSTR to break an extended rowid into its four
components (database object, file, block, and row):
SELECT ROWID,
See Also: Oracle9i Application Developer's Guide - Fundamentals for
information about the DBMS_ROWID package
ROWID and UROWID Datatypes
12-20 Oracle9 iDatabase Concepts
SUBSTR(ROWID,1,6) "OBJECT",
SUBSTR(ROWID,7,3) "FIL",
SUBSTR(ROWID,10,6) "BLOCK",
SUBSTR(ROWID,16,3) "ROW"
FROM products;
ROWID OBJECT FIL BLOCK ROW
------------------ ------ --- ------ ----
AAAA8mAALAAAAQkAAA AAAA8m AAL AAAAQk AAA
AAAA8mAALAAAAQkAAF AAAA8m AAL AAAAQk AAF
AAAA8mAALAAAAQkAAI AAAA8m AAL AAAAQk AAI
Or you can use SUBSTR to break a restricted rowid into its three components
(block,
row, and file):
SELECT ROWID, SUBSTR(ROWID,15,4) "FILE",
SUBSTR(ROWID,1,8) "BLOCK",
SUBSTR(ROWID,10,4) "ROW"
FROM products;
ROWID FILE BLOCK ROW
------------------ ---- -------- ----
00000DD5.0000.0001 0001 00000DD5 0000
00000DD5.0001.0001 0001 00000DD5 0001
00000DD5.0002.0001 0001 00000DD5 0002
Rowids can be useful for revealing information about the physical storage of
a
table's data. For example, if you are interested in the physical location of
a table's
rows (such as for table striping), the following query of an extended rowid
tells how
many datafiles contain rows of a given table:
SELECT COUNT(DISTINCT(SUBSTR(ROWID,7,3))) "FILES" FROM tablename;
FILES
--------
2
See Also:
Oracle9i SQL Reference
PL/SQL User's Guide and Reference
Oracle9i Database Performance Tuning Guide and Reference
for more examples using rowids
ROWID and UROWID Datatypes
Native Datatypes 12-21
How Rowids Are Used
Oracle uses rowids internally for the construction of indexes. Each key in
an index
is associated with a rowid that points to the associated row's address for
fast access.
End users and application developers can also use rowids for several
important
functions:
Rowids are the fastest means of accessing particular rows.
Rowids can be used to see howa table is organized.
Rowids are unique identifiers for rows in a given table.
Before you use rowids in DML statements, they should be verified and
guaranteed
not to change. The intended rows should be locked so they cannot be deleted.
Under some circumstances, requesting data with an invalid rowid could cause
a
statement to fail.
You can also create tables with columns defined using the ROWID datatype.
For
example, you can define an exception table with a column of datatype ROWID
to
store the rowids of rows in the database that violate integrity constraints.
Columns
defined using the ROWID datatype behave like other table columns: values can
be
updated, and so on. Each value in a column defined as datatype ROWID
requires six
bytes to store pertinent column data.
Logical Rowids
Rows in index-organized tables do not have permanent physical addresses?they
are stored in the index leaves and can movewithin the block or to a
different block
as a result of insertions. Therefore their row identifiers cannot be based
on physical
addresses. Instead, Oracle provides index-organized tables with logical row
identifiers, called logical rowids, that are based on the table's primary
key. Oracle
uses these logical rowids for the construction of secondary indexes on
index-organized tables.
Each logical rowid used in a secondary index can include a physical guess,
which
identifies the block location of the row in the index-organized table at the
time the
guess was made; that is, when the secondary index was created or rebuilt.
Oracle can use guesses to probe into the leaf block directly, bypassing the
full key
search. This ensures that rowid access of nonvolatile index-organized tables
gives
comparable performance to the physical rowid access of ordinary tables. In a
volatile table, however, if the guess becomes stale the probe can fail, in
which case a
primary key search must be performed.
ROWID and UROWID Datatypes
12-22 Oracle9 iDatabase Concepts
The values of two logical rowids are considered equal if they have the same
primary key values but different guesses.
Comparison of Logical Rowids with Physical Rowids
Logical rowids are similar to the physical rowids in the following ways:
Logical rowids are accessible through the ROWID pseudocolumn.
You can use the ROWID pseudocolumn to select logical rowids from an
index-organized table. The SELECT ROWID statement returns an opaque
structure, which internally consists of the table's primary key and the
physical
guess (if any) for the row, along with some control information.
You can access a row using predicates of the form WHERE ROWID = value,
where value is the opaque structure returned by SELECT ROWID.
Access through the logical rowid is the fastest way to get to a specific
row,
although it can require more than one block access.
A row's logical rowid does not change as long as the primary key value does
not change. This is less stable than the physical rowid, which stays
immutable
through all updates to the row.
Logical rowids can be stored in a column of the UROWID datatype
One difference between physical and logical rowids is that logical rowids
cannot be
used to see how a table is organized.
Guesses in Logical Rowids
When a row's physical location changes, the logical rowid remains valid even
if it
contains a guess, although the guess could become stale and slow down access
to
the row. Guess information cannot be updated dynamically. For secondary
indexes
on index-organized tables, however, you can rebuild the index to obtain
fresh
guesses. Note that rebuilding a secondary index on an index-organized table
involves reading the base table, unlike rebuilding an index on an ordinary
table.
Note: An opaque type is one whose internal structure is not
known to the database. The database provides storage for the type.
The type designer can provide access to the contents of the type by
implementing functions, typically 3GL routines.
See Also: "ROWID and UROWID Datatypes" on page 12-16
ANSI, DB2, and SQL/DS Datatypes
Native Datatypes 12-23
Collect index statistics with the DBMS_STATS package or ANALYZE statement to
keep track of the staleness of guesses, so Oracle does not use them
unnecessarily.
This is particularly important for applications that store rowids with
guesses
persistently in a UROWID column, then retrieve the rowids later and use
themto
fetch rows.
When you collect index statistics with the DBMS_STATS package or ANALYZE
statement, Oracle checks whether the existing guesses are still valid and
records the
percentage of stale/valid guesses in the data dictionary. After you rebuild
a
secondary index (recomputing the guesses), collect index statistics again.
In general, logical rowids without guesses provide the fastest possible
access for a
highly volatile table. If a table is static or if the time between getting a
rowid and
using it is sufficiently short to make row movement unlikely, logical rowids
with
guesses provide the fastest access.
Rowids in Non-Oracle Databases
Oracle database applications can be run against non-Oracle database servers
using
SQL*Connect or the Oracle Transparent Gateway. In such cases, the format of
rowids varies according to the characteristics of the non-Oracle system.
Furthermore, no standard translation to VARCHAR2/hexadecimal format is
available. Programs can still use the ROWID datatype. However, they must use
a
nonstandard translation to hexadecimal format of length up to 256 bytes.
Rowids of a non-Oracle database can be stored in a column of the UROWID
datatype.
---------------------------------------------------------------------
sure, so I'll post it here.
So check this out ---
http://msdn.microsoft.com/library/d...en-us/odbc/htm/odbcoptimistic_concurrency.asp
That link says that "To determine whether a row has been changed, its new
version is checked against a cached version of the row. This checking can be
based on the row version, such as the timestamp column in SQL Server or the
ROWID column in Oracle, or the values of each column in the row. "
Now how exactly does ROWID enforce concurrency checks in Oracle?
RowID is not guaranteed to be Unique within a database
RowID can change without the user hitting a "save" as a result of certain
dba like database operations (Reindex/repartition etc.)
RowIDs are guaranteed to stay consistent and not changing over the life of
the row - unless a repartition reindex of a table is done.
Inserts/UpdatesCAN change RowIDs, but they might not necessarily do so.
(The above as per OTN documentation --- pasted at the end of my message).
So how in the world does RowID work the same was as TimeStamp .. as per MSDN
... and as per Sceppa's book? .. please clarify.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
---- LONG COPY PASTE BEGIN -----
Rowids of Row Pieces
The rowid identifies each row piece by its location or address. After they
are
assigned, a given row piece retains its rowid until the corresponding row is
deleted
or exported and imported using the Export and Import utilities. For
clustered
tables, if the cluster key values of a row change, then the row keeps the
same rowid
but also gets an additional pointer rowid for the new values.
Because rowids are constant for the lifetime of a row piece, it is useful to
reference
rowids in SQL statements such as SELECT, UPDATE, and DELETE.
ROWID and UROWID Datatypes
Oracle uses a ROWID datatype to store the address (rowid) of every row in
the
database.
Physical rowids store the addresses of rows in ordinary tables (excluding
index-organized tables), clustered tables, table partitions and
subpartitions,
indexes, and index partitions and subpartitions.
Logical rowids store the addresses of rows in index-organized tables.
A single datatype called the universal rowid, or UROWID, supports both
logical and
physical rowids, as well as rowids of foreign tables such as non-Oracle
tables
accessed through a gateway.
A column of the UROWID datatype can store all kinds of rowids. The value of
the
COMPATIBLE initialization parameter must be set to 8.1 or higher to use
UROWID
columns.
See Also: Oracle9i Application Developer's Guide - Fundamentals for
information about other restrictions on the LONG RAW datatype
See Also: "Rowids in Non-Oracle Databases" on page 12-23
ROWID and UROWID Datatypes
Native Datatypes 12-17
The ROWID Pseudocolumn
Each table in an Oracle database internally has a pseudocolumn named ROWID.
This pseudocolumn is not evident when listing the structure of a table by
executing
a SELECT * FROM ... statement, or a DESCRIBE ... statement using SQL*Plus,
nor
does the pseudocolumn take up space in the table. However, each row's
address can
be retrieved with a SQL query using the reserved word ROWID as a column
name,
for example:
SELECT ROWID, last_name FROM employees;
You cannot set the value of the pseudocolumn ROWID in INSERT or UPDATE
statements, and you cannot delete a ROWID value. Oracle uses the ROWID
values in
the pseudocolumn ROWID internally for the construction of indexes.
You can reference rowids in the pseudocolumn ROWID like other table columns
(used in SELECT lists and WHERE clauses), but rowids are not stored in the
database,
nor are they database data. However, you can create tables that contain
columns
having the ROWID datatype, although Oracle does not guarantee that the
values of
such columns are valid rowids. The user must ensure that the data stored in
the
ROWID column truly is a valid ROWID.
Physical Rowids
Physical rowids provide the fastest possible access to a row of a given
table. They
contain the physical address of a row (down to the specific block) and allow
you to
retrieve the row in a single block access. Oracle guarantees that as long as
the row
exists, its rowid does not change. These performance and stability qualities
make
rowids useful for applications that select a set of rows, perform some
operations on
them, and then access some of the selected rows again, perhaps with the
purpose of
updating them.
Every row in a nonclustered table is assigned a unique rowid that
corresponds to
the physical address of a row's row piece (or the initial row piece if the
row is
chained among multiple row pieces). In the case of clustered tables, rows in
different tables that are in the same data block can have the same rowid.
A row's assigned rowid remains unchanged unless the row is exported and
imported using the Import and Export utilities. When you delete a row from a
table
and then commit the encompassing transaction, the deleted row's associated
rowid
can be assigned to a row inserted in a subsequent transaction.
A physical rowid datatype has one of two formats:
See Also: "How Rowids Are Used" on page 12-21
ROWID and UROWID Datatypes
12-18 Oracle9 iDatabase Concepts
The extended rowid format supports tablespace-relative data block addresses
and efficiently identifies rows in partitioned tables and indexes as well as
nonpartitioned tables and indexes. Tables and indexes created by an Oracle8i
(or higher) server always have extended rowids.
A restricted rowid format is also available for backward compatibility with
applications developed with Oracle7 or earlier releases.
Extended Rowids
Extended rowids use a base 64 encoding of the physical address for each row
selected. The encoding characters are A-Z, a-z, 0-9, +, and /. For
example, the following query:
SELECT ROWID, last_name FROM employees WHERE department_id = 20;
can return the following row information:
ROWID LAST_NAME
------------------ ----------
AAAAaoAATAAABrXAAA BORTINS
AAAAaoAATAAABrXAAE RUGGLES
AAAAaoAATAAABrXAAG CHEN
AAAAaoAATAAABrXAAN BLUMBERG
An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:
OOOOOO: The data object number that identifies the database segment
(AAAAao in the example). Schema objects in the same segment, such as a
cluster
of tables, have the same data object number.
FFF: The tablespace-relative datafile number of the datafile that contains
the
row (file AAT in the example).
BBBBBB: The data block that contains the row (block AAABrX in the
example). Block numbers are relative to their datafile, not tablespace.
Therefore,
two rows with identical block numbers could reside in two different
datafiles of
the same tablespace.
RRR: The row in the block.
You can retrieve the data object number from data dictionary views USER_
OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. For example, the following query
returns the data object number for the employees table in the SCOTT schema:
SELECT DATA_OBJECT_ID FROM DBA_OBJECTS
WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'EMPLOYEES';
ROWID and UROWID Datatypes
Native Datatypes 12-19
You can also use the DBMS_ROWID package to extract information from an
extended
rowid or to convert a rowid from extended format to restricted format (or
vice
versa).
Restricted Rowids
Restricted rowids use a binary representation of the physical address for
each row
selected.When queried using SQL*Plus, the binary representation is converted
to a
VARCHAR2/hexadecimal representation. The following query:
SELECT ROWID, last_name FROM employees
WHERE department_id = 30;
can return the following row information:
ROWID ENAME
------------------ ----------
00000DD5.0000.0001 KRISHNAN
00000DD5.0001.0001 ARBUCKLE
00000DD5.0002.0001 NGUYEN
As shown, a restricted rowid's VARCHAR2/hexadecimal representation is in a
three-piece format, block.row.file:
The data block that contains the row (block DD5 in the example). Block
numbers are relative to their datafile, not tablespace. Therefore, two
rowswith
identical block numbers could reside in two different datafiles of the same
tablespace.
The row in the block that contains the row(rows 0, 1, 2 in the example).
Row
numbers of a given block always start with 0.
The datafile that contains the row (file 1 in the example). The first
datafile of
every database is always 1, and file numbers are unique within a database.
Examples of Rowid Use
You can use the function SUBSTR to break the data in a rowid into its
components.
For example, you can use SUBSTR to break an extended rowid into its four
components (database object, file, block, and row):
SELECT ROWID,
See Also: Oracle9i Application Developer's Guide - Fundamentals for
information about the DBMS_ROWID package
ROWID and UROWID Datatypes
12-20 Oracle9 iDatabase Concepts
SUBSTR(ROWID,1,6) "OBJECT",
SUBSTR(ROWID,7,3) "FIL",
SUBSTR(ROWID,10,6) "BLOCK",
SUBSTR(ROWID,16,3) "ROW"
FROM products;
ROWID OBJECT FIL BLOCK ROW
------------------ ------ --- ------ ----
AAAA8mAALAAAAQkAAA AAAA8m AAL AAAAQk AAA
AAAA8mAALAAAAQkAAF AAAA8m AAL AAAAQk AAF
AAAA8mAALAAAAQkAAI AAAA8m AAL AAAAQk AAI
Or you can use SUBSTR to break a restricted rowid into its three components
(block,
row, and file):
SELECT ROWID, SUBSTR(ROWID,15,4) "FILE",
SUBSTR(ROWID,1,8) "BLOCK",
SUBSTR(ROWID,10,4) "ROW"
FROM products;
ROWID FILE BLOCK ROW
------------------ ---- -------- ----
00000DD5.0000.0001 0001 00000DD5 0000
00000DD5.0001.0001 0001 00000DD5 0001
00000DD5.0002.0001 0001 00000DD5 0002
Rowids can be useful for revealing information about the physical storage of
a
table's data. For example, if you are interested in the physical location of
a table's
rows (such as for table striping), the following query of an extended rowid
tells how
many datafiles contain rows of a given table:
SELECT COUNT(DISTINCT(SUBSTR(ROWID,7,3))) "FILES" FROM tablename;
FILES
--------
2
See Also:
Oracle9i SQL Reference
PL/SQL User's Guide and Reference
Oracle9i Database Performance Tuning Guide and Reference
for more examples using rowids
ROWID and UROWID Datatypes
Native Datatypes 12-21
How Rowids Are Used
Oracle uses rowids internally for the construction of indexes. Each key in
an index
is associated with a rowid that points to the associated row's address for
fast access.
End users and application developers can also use rowids for several
important
functions:
Rowids are the fastest means of accessing particular rows.
Rowids can be used to see howa table is organized.
Rowids are unique identifiers for rows in a given table.
Before you use rowids in DML statements, they should be verified and
guaranteed
not to change. The intended rows should be locked so they cannot be deleted.
Under some circumstances, requesting data with an invalid rowid could cause
a
statement to fail.
You can also create tables with columns defined using the ROWID datatype.
For
example, you can define an exception table with a column of datatype ROWID
to
store the rowids of rows in the database that violate integrity constraints.
Columns
defined using the ROWID datatype behave like other table columns: values can
be
updated, and so on. Each value in a column defined as datatype ROWID
requires six
bytes to store pertinent column data.
Logical Rowids
Rows in index-organized tables do not have permanent physical addresses?they
are stored in the index leaves and can movewithin the block or to a
different block
as a result of insertions. Therefore their row identifiers cannot be based
on physical
addresses. Instead, Oracle provides index-organized tables with logical row
identifiers, called logical rowids, that are based on the table's primary
key. Oracle
uses these logical rowids for the construction of secondary indexes on
index-organized tables.
Each logical rowid used in a secondary index can include a physical guess,
which
identifies the block location of the row in the index-organized table at the
time the
guess was made; that is, when the secondary index was created or rebuilt.
Oracle can use guesses to probe into the leaf block directly, bypassing the
full key
search. This ensures that rowid access of nonvolatile index-organized tables
gives
comparable performance to the physical rowid access of ordinary tables. In a
volatile table, however, if the guess becomes stale the probe can fail, in
which case a
primary key search must be performed.
ROWID and UROWID Datatypes
12-22 Oracle9 iDatabase Concepts
The values of two logical rowids are considered equal if they have the same
primary key values but different guesses.
Comparison of Logical Rowids with Physical Rowids
Logical rowids are similar to the physical rowids in the following ways:
Logical rowids are accessible through the ROWID pseudocolumn.
You can use the ROWID pseudocolumn to select logical rowids from an
index-organized table. The SELECT ROWID statement returns an opaque
structure, which internally consists of the table's primary key and the
physical
guess (if any) for the row, along with some control information.
You can access a row using predicates of the form WHERE ROWID = value,
where value is the opaque structure returned by SELECT ROWID.
Access through the logical rowid is the fastest way to get to a specific
row,
although it can require more than one block access.
A row's logical rowid does not change as long as the primary key value does
not change. This is less stable than the physical rowid, which stays
immutable
through all updates to the row.
Logical rowids can be stored in a column of the UROWID datatype
One difference between physical and logical rowids is that logical rowids
cannot be
used to see how a table is organized.
Guesses in Logical Rowids
When a row's physical location changes, the logical rowid remains valid even
if it
contains a guess, although the guess could become stale and slow down access
to
the row. Guess information cannot be updated dynamically. For secondary
indexes
on index-organized tables, however, you can rebuild the index to obtain
fresh
guesses. Note that rebuilding a secondary index on an index-organized table
involves reading the base table, unlike rebuilding an index on an ordinary
table.
Note: An opaque type is one whose internal structure is not
known to the database. The database provides storage for the type.
The type designer can provide access to the contents of the type by
implementing functions, typically 3GL routines.
See Also: "ROWID and UROWID Datatypes" on page 12-16
ANSI, DB2, and SQL/DS Datatypes
Native Datatypes 12-23
Collect index statistics with the DBMS_STATS package or ANALYZE statement to
keep track of the staleness of guesses, so Oracle does not use them
unnecessarily.
This is particularly important for applications that store rowids with
guesses
persistently in a UROWID column, then retrieve the rowids later and use
themto
fetch rows.
When you collect index statistics with the DBMS_STATS package or ANALYZE
statement, Oracle checks whether the existing guesses are still valid and
records the
percentage of stale/valid guesses in the data dictionary. After you rebuild
a
secondary index (recomputing the guesses), collect index statistics again.
In general, logical rowids without guesses provide the fastest possible
access for a
highly volatile table. If a table is static or if the time between getting a
rowid and
using it is sufficiently short to make row movement unlikely, logical rowids
with
guesses provide the fastest access.
Rowids in Non-Oracle Databases
Oracle database applications can be run against non-Oracle database servers
using
SQL*Connect or the Oracle Transparent Gateway. In such cases, the format of
rowids varies according to the characteristics of the non-Oracle system.
Furthermore, no standard translation to VARCHAR2/hexadecimal format is
available. Programs can still use the ROWID datatype. However, they must use
a
nonstandard translation to hexadecimal format of length up to 256 bytes.
Rowids of a non-Oracle database can be stored in a column of the UROWID
datatype.
---------------------------------------------------------------------