Identifying Rows by Address in Oracle 10g

A Database Table in Oracle10g consists of rows and each row in the table is associated with an address called a ROWID. The ROWID has values that are strings which represent the address of each row. The Datatype of these strings are said to be ROWID or UROWID type. The ROWID datatype can also be the datatype of an actual column in a table or cluster, but Oracle Database provides no guarantee that the values present in such columns be valid ROWIDs.

The significance of ROWID values to an application can be determined by the three main reasons, they signify the fastest manner to access a single row, they can reveal the way in which the rows in a table are stored and they are distinctive identifiers for rows in a table.

Although ROWID’s are usually unique, the same rowed can be associated to two different rows provided they are in the same data block and in different clustered tables. They are referred to as a pseudocolumn of the table. They can be used in the SELECT and WHERE clauses in a query but the values are not in reality stored in the database and cannot be inserted, updated or deleted. The ROWID is basically a structure that holds the relevant information needed by the database server to access a row.

Other than tables that are index organized and foreign, the values pertaining to the ROWID pseudocolumn have the datatype ROWID with the format being either extended or restricted. The restricted internal ROWID will be 6 bytes on most platforms and contains data pertaining to Datafile identifier, Block identifier and Row identifier. This pseudocolumn returns the value to client applications in the type of an 18 character string by way of a hexadecimal encoding of the details.

The extended ROWID datatype returns the value in the form of that includes a data object number in addition to the data in the restricted ROWID.The Data object number is actually an identification number that is assigned to each database segment. This extended ROWID is 10 bytes on the majority of platforms. Here data is transmitted back to the client application by means of an 18 character string with a base 64 encoding of the details in a four-piece format. In general the DBMS_ROWID package is used to extract and provide information specific to extended ROWIDs.

Some client applications make use of a binary form of a ROWID, the size being alike for extended and restricted ROWIDs.

Sometimes the rows of some tables like index-organized comprise of addresses that are not physical, not generated by the Oracle Database and just temporary. For these tables Oracle provides logical row identifiers called logical ROWIDs. Oracle associates foreign tables with identifiers called foreign ROWIDs. The logical ROWIDs are created based on the primary key of a table and are constant as long as the primary key is constant. The ROWID pseudo column of an index organized table contains a datatype of UROWID. A column of type UROWID can be defined and used to store the value of the ROWID pseudocolumn.

Even though ROWIDs are unique they should not be used as primary keys because if a row is deleted, the ROWID corresponding to it may be reassigned by Oracle to a new row inserted at a later stage.

FREE Subscription

Subscribe to our mailing list and receive new articles
through email. Keep yourself updated with latest
developments in the industry.

Note : We never rent, trade, or sell my email lists to
anyone. We assure that your privacy is respected
and protected.

_______________________________________