Lesson 8

In this module you examined Oracle’s object-relational data structures—features introduced in the Oracle 8i era and still supported today, including Oracle 19c and Oracle 23ai. The goal was not to replace relational modeling, but to understand how Oracle extends the relational engine with optional object and collection capabilities—and what those choices can imply for performance, maintainability, and query complexity.

You also modernized the historical perspective: in Oracle 23ai, many teams prefer relational tables (with keys and constraints) plus JSON features (and, when appropriate, JSON Relational Duality Views) for application-facing document shapes. Object types, object tables, nested tables, and VARRAYs remain valuable when they match the workload and when you can justify the tradeoffs.

1. Abstract Data Types (ADTs / object types): You can define reusable structures with CREATE TYPE ... AS OBJECT and use them as column types or as element types inside collections.
2. Object tables and OIDs: An object table stores row objects. Oracle assigns each row an internal identifier (OID) and enables object-style references when you use REF columns.
3. REF and DEREF usage: DEREF dereferences a REF to return the referenced object value. This is specific to object-relational designs. For most OLTP schemas, foreign keys and joins remain the default pattern.
4. Nested tables: A nested table is an unbounded collection that can be stored as a column. Oracle persists nested rows in a separate store table and exposes them with TABLE().
5. VARRAYs: A VARRAY is a bounded, ordered collection. It is a good fit for small lists that are nearly always retrieved with the parent row, but it can complicate searching and updates compared to a normalized child table.

Oracle did not rebuild its database engine as a “pure” object database. Instead, Oracle retained a relational core and added object-relational capabilities on top. Conceptually, that object layer supports:

1. abstract data typing,
2. aggregate objects (types composed of other types),
3. optional coupling of data and behavior (member methods),
4. inheritance and polymorphism in type hierarchies,
5. encapsulation and extensibility.

Performance note: object-relational features can reduce application-side marshaling and can simplify some access patterns, but they can also introduce complexity in indexing, querying, and change management. In Oracle tuning work, the correct decision is workload-dependent.

In an object table, each row is an object instance of an object type. Oracle assigns an internal OID when the row is created, and other objects can reference that row via a REF.

CREATE OR REPLACE TYPE animal_ty AS OBJECT (
  breed     VARCHAR2(25),
  name      VARCHAR2(25),
  birthdate DATE
);
/

CREATE TABLE animal OF animal_ty;

When object references are used, a column can store a REF to an object row, and DEREF can return the object value. This is not a general replacement for relational foreign keys; it is a specialized object-relational mechanism.

-- Illustrative pattern (requires a REF column defined against an object table)
-- SELECT DEREF(t.some_ref).name
-- FROM   some_table t;

Oracle 23ai keeps all of the object-relational constructs discussed in this module, but modern schema design frequently takes one of these paths:

• Relational-first: normalized tables with foreign keys, indexes, and constraints (default for most transactional systems).
• JSON-first in a single column: store nested arrays and objects in a native JSON column when flexibility and document exchange dominate the requirements.
• Relational storage with a JSON interface: use JSON Relational Duality Views when you want relational integrity and performance, but applications want to read and write a single JSON document shape.

The tuning mindset remains the same: choose the structure that fits the access pattern, volume, update frequency, and operational constraints.

1. Object ID (OID): An internal identifier for an object row in an object table.
2. REF / pointer (conceptual): A reference value that points to an object row in an object table (used in object-relational designs).
3. VARRAY: A bounded, ordered collection type used for small repeating groups stored with a parent row.
4. Nested table: An unbounded collection type stored in a separate store table and accessed with TABLE().

The next module discusses using Oracle dictionary views to monitor performance and validate tuning changes.

Before moving on to the next module, click the Quiz link below to test your understanding of tuning with data structures.
Object Data Constructs - Quiz