Navigating Oracle Object IDs (OIDs)

Oracle’s object-relational features introduced the idea of row objects: rows stored in object tables that have a database-managed identity known as an object identifier (OID). OIDs uniquely identify row objects in object tables and are the basis for Oracle’s object references (REF) and dereferencing (DEREF).

Terminology warning: “OID” is also used in Oracle documentation and industry discussions to mean Oracle Internet Directory. In this lesson, “OID” refers to object identifiers in Oracle’s object-relational model.

Historically, the idea was attractive: store a reference to a related object and “navigate” directly to that object instead of joining tables with foreign keys. In modern Oracle (including 23ai), the mechanism still exists and is supported—but it is no longer a common default design choice for typical relational schemas.

In an object-relational design, you can define an object type, create an object table of that type, and then store a REF to those row objects in other object types/tables. Oracle does not expose the OID directly; instead, you work with the REF, and you can use DEREF to retrieve the referenced object.

Example: Traditional relational join

Here is the conventional (and still most common) way to return employee and department attributes: store a department key in the employee table and join.

SELECT
  e.emp_name,
  d.dept_name,
  d.dept_supervisor
FROM emp e
JOIN dept d
  ON d.dept_id = e.dept_id;

Example: Object-relational navigation using REF and DEREF

The next example shows the historical object-relational approach: store a REF to a department row object. The SCOPE clause constrains the REF to a specific object table, which improves safety and can enable better enforcement/optimization.

-- 1) Define an object type for departments
CREATE OR REPLACE TYPE dept_t AS OBJECT (
  dept_id         NUMBER,
  dept_name       VARCHAR2(40),
  dept_supervisor VARCHAR2(40)
);
/

-- 2) Create an object table of that type
CREATE TABLE dept_objtab OF dept_t (
  CONSTRAINT dept_objtab_pk PRIMARY KEY (dept_id)
);

-- 3) Define an employee object type that stores a REF to dept_t
CREATE OR REPLACE TYPE emp_t AS OBJECT (
  emp_id     NUMBER,
  emp_name   VARCHAR2(40),
  department REF dept_t
);
/

-- 4) Create an employee object table and constrain the REF with SCOPE
CREATE TABLE emp_objtab OF emp_t (
  CONSTRAINT emp_objtab_pk PRIMARY KEY (emp_id),
  SCOPE FOR (department) IS dept_objtab
);

-- 5) Insert sample data
INSERT INTO dept_objtab VALUES (dept_t(10, 'SALES', 'KIM'));
INSERT INTO dept_objtab VALUES (dept_t(20, 'IT', 'RIVERA'));

INSERT INTO emp_objtab
SELECT emp_t(1, 'ALEX', REF(d))
FROM dept_objtab d
WHERE d.dept_id = 10;

-- 6) Dereference the REF to return department attributes
SELECT
  e.emp_name,
  DEREF(e.department).dept_name       AS dept_name,
  DEREF(e.department).dept_supervisor AS dept_supervisor
FROM emp_objtab e;

This pattern demonstrates the lesson’s objective: navigating through an object reference based on the underlying OID. However, it is important to set expectations correctly: object navigation does not magically eliminate cost. Oracle still has to locate the referenced row object, and in real systems the optimizer can often execute a well-indexed relational join extremely efficiently.

Object views are another historical bridge: you can expose relational data “as objects” without converting base tables into object tables. The key is that an object view needs an object identifier. In practice, this identifier is typically derived from a primary key using the WITH OBJECT IDENTIFIER clause.

CREATE OR REPLACE TYPE employee_t AS OBJECT (
  employee_id NUMBER(6),
  last_name   VARCHAR2(25),
  job_id      VARCHAR2(10)
);
/

CREATE OR REPLACE VIEW it_prog_view OF employee_t
  WITH OBJECT IDENTIFIER (employee_id)
AS
SELECT e.employee_id, e.last_name, e.job_id
FROM employees e
WHERE e.job_id = 'IT_PROG';

Object views are most useful for prototyping, integration with object-oriented code paths, or phased transitions where applications expect object semantics but the underlying storage remains relational.

Oracle 23ai continues to support object tables, OIDs, REFs, and DEREF as part of the object-relational feature set. That said, embedding object references as a primary navigation technique is generally not the mainstream approach for typical relational application schemas.

1. Use standard primary keys and foreign keys for relationships. Joins are a first-class, highly optimized operation in Oracle. A clear PK/FK design improves integrity, portability, and tooling compatibility.
2. Prefer identity columns (or sequences) for surrogate keys. For row identity, use a stable logical key. Avoid treating ROWID as a permanent identifier because it is a physical locator that can change after certain maintenance operations.
3. Use REFs/OIDs only when you need object-relational semantics. Examples: strong typing of object references, specialized object interfaces, legacy ORDBMS patterns, or specific advanced features that rely on object types and references. When you do use REFs, constrain them (for example, with SCOPE) to keep the model predictable.
4. For document-style workloads, use converged features instead of pointer-style navigation. For example, JSON-centric applications often work better with modern JSON capabilities rather than modeling document identity through embedded object references.

Bottom line: treat OID-based navigation as an important historical concept and a specialized tool—not the default relational modeling technique. The next lesson continues the object-relational discussion by exploring nested tables.

Oracle also uses “OID” in a second, separate way: the optional OID clause on CREATE TYPE assigns a user-specified identifier to the type definition itself, which helps establish type equivalence across databases (for example, when deploying the same user-defined type in multiple databases).

CREATE OR REPLACE TYPE type_name OID 'oid'
AS OBJECT (attribute_name datatype /*, ... */);

This is distinct from the row-object OIDs used internally for rows in object tables. Keep these two meanings separate when reading documentation.