Oracle urowid Datatype

by
Using the urowid datatype in sql developer tool

In this Oracle tutorial, we will learn about the urowid datatype in the oracle database. Also, we will illustrate how to use it to create urowid columns for a table in oracle.

Introduction to Oracle urowid Datatype

In Oracle 21c, a datatype known as a Universal Rowid (UROWID) is used to hold the actual physical address of a row in a database. Rows may be readily retrieved using this type of data type, and addresses can be saved for backup and recovery purposes.

It’s used to allow quick access to particular rows within a table and is stored in the database as a fixed-length string of characters.

The UROWID datatype supports the following formats:

  • URowid binary: This format stores the address as a binary number. This is the most efficient format for storage, as it requires the least amount of space.
  • URowid character: This format stores the address as a character string. This format is used when the application needs to display the address to the user or when it needs to store the address in a non-binary format.

Also, check: How to check database status in Oracle

Oracle urowid datatype Syntax

The syntax for declaring a column of urowid data type in Oracle 21c is as follows:

column_name UROWID;

Here, column_name is the name of the column and UROWID is the data type.

Oracle urowid datatype Example

Here’s an example of creating a table with a urowid column in Oracle 21c.

CREATE TABLE my_table (
   id     NUMBER,
   value  RAW(16)
);

In this example, a table named my_table is created with two columns: id and value. The value column is defined as a RAW data type with a length of 16 bytes. 

INSERT INTO my_table (id, value) VALUES (1, HEXTORAW('48656C6C6F20576F726C64'));

When the SELECT statement is executed, the RAWTOHEX function is used to convert the value column to its hexadecimal representation.

Using the urowid datatype in oracle 21c
Using the urowid datatype in oracle 21c

Now we will use the rowid datatype in a SQL developer tool using oracle 21c.

Using the urowid datatype in sql developer tool
Using the urowid datatype in sql developer tool

Read: Oracle nclob Datatype

Advantages to Oracle urowid Datatype

The Oracle urowid (Universal ROW ID) datatype has several advantages over other datatypes:

  • Each row in a database is given a unique identification called the urowid datatype. It is a binary value that is promised to be distinct throughout a database, including between various tables and schemas. This makes it perfect for connecting tables, monitoring modifications, and locating certain rows.
  • Compact storage: Unlike other datatypes like VARCHAR2 or NUMBER, the urowid datatype is stored in a compact binary format that takes up less storage space. This implies that the total storage needs of the database won’t be much affected by the addition of urowid columns to tables.
  • Effective operations: Compared to character-based datatypes, the urowid datatype can be compared and sorted significantly more quickly because it is a binary value. As a result, query execution times may be accelerated.
  • Support for distributed databases: The urowid datatype can be used to uniquely identify rows in a table across multiple databases or nodes in a distributed database environment. This makes it easier to manage and track data across a distributed database architecture.
  • Overall, the urowid datatype is a powerful tool for managing and querying data in an Oracle database. Its unique identifier, compact storage, efficient operations, integration with Oracle features, and support for distributed databases make it a valuable addition to any database schema.

Disadvantages to Oracle urowid Datatype

  • Limited readability: The binary nature of the urowid datatype makes it challenging for humans to comprehend. This can make it difficult to use for debugging or troubleshooting purposes, especially for non-technical people.
  • Costs of maintenance: The urowid datatype requires ongoing maintenance. The urowid value associated with a deleted row must be modified because the deletion of a row invalidates it. Database operations may therefore have increased processing overhead.
  • The urowid values associated with each record may change as a result of upgrades to the database structure. Columns can be added or removed as examples. As a result, it could be difficult to maintain consistency between different database versions.
  • Security considerations: Because the urowid datatype is a unique identifier for each row, it can be used to track and identify individual rows of data. This can present security risks if the urowid values are exposed to unauthorized users or used for malicious purposes.

Features of Oracle urowid Datatype

  • Unique identifier: The database automatically assigns the UROWID unique identifier for each row in a table. It cannot be changed by users or apps because it is immutable.
  • Storage: Because UROWID values are saved as binary data, they take up less room than character data types.
  • Platform independence: UROWID values can be utilized on various hardware platforms and operating systems since they are platform-independent.
  • The longest UROWID can be is 4000 bytes long.
  • Application programming is not the primary purpose of the UROWID data type; rather, internal database operations are. It can be used to track changes to rows during certain database operations as well as to identify rows in tables without the usage of primary keys.
  • Performance: UROWID data type has excellent performance characteristics, especially when used in database operations that involve large tables.

Conclusion:

In this oracle tutorial, we have covered the UROWID datatype in the oracle database. Also, we covered illustrated how to use it to create UROWID columns for a table in oracle.

Also, take a look at some more Oracle tutorials.