Oracle 23ai Data Structures Overview

Oracle’s feature set has expanded substantially since the Oracle7/Oracle8 era, when the database was commonly described as “primarily relational” and compared against object-oriented database systems. Oracle 23ai is best described as a converged database: it supports relational workloads and multiple modern data models (JSON, vectors for AI search, graph, spatial) within one engine, one security model, and one operational footprint.

For performance tuning, “data structures” means more than data types. It includes: schema objects (tables, indexes, partitions, materialized views), specialized storage structures (LOBs, immutable/blockchain tables), and multimodel structures (JSON duality views, vector columns and indexes). These structures shape what the optimizer can do and often determine whether a query uses efficient access paths or falls back to full scans, heavy joins, and disk sorts.

Oracle’s tuning fundamentals still start with classic relational structures. These are the most common “performance levers” because they directly influence access paths, join strategies, and sort avoidance.

• Tables and constraints: well-defined keys and constraints improve data quality and help the optimizer reason about cardinality and join uniqueness.
• Indexes: the primary structure Oracle uses to avoid full-table scans and large sorts. This module focuses on:
  1. B-tree indexes (general-purpose OLTP indexing)
  2. Bitmap indexes (often best for low-cardinality columns in read-mostly analytics)
  3. Function-based indexes (index expressions used in predicates)
  4. Index-organized tables (IOTs) (store row data in a B-tree ordered by primary key)
• Partitioning: partitions reduce I/O by letting Oracle prune data ranges and parallelize large scans and loads. Even if partitioning is not used everywhere, understanding it matters because it changes execution plans and maintenance strategy.
• LOBs instead of LONG: legacy LONG and LONG RAW types limit functionality and tooling. In modern Oracle, store large text and binary content in CLOB/BLOB for maintainability and feature support.

Index examples (illustrative):

-- B-tree index (default)
CREATE INDEX customer_lname_ix ON customer(lastname);

-- Function-based index (supports predicates like WHERE UPPER(lastname) = 'SMITH')
CREATE INDEX customer_lname_upper_ix ON customer(UPPER(lastname));

-- Bitmap index (typical analytic use case)
CREATE BITMAP INDEX sales_region_bix ON sales(region_code);

Oracle 23ai extends the relational model with native support for modern application data patterns. These are not “separate engines”; they are database structures designed to participate in SQL, security, backup/recovery, and performance tooling.

JSON Storage and JSON Relational Duality Views

Oracle 23ai supports high-performance JSON use cases, including JSON Relational Duality Views, which provide updatable JSON document views over relational tables. This is a major structural improvement because you can keep normalized tables (consistency, constraints, indexing) while giving developers a document-style interface for read/write operations. :contentReference[oaicite:0]{index=0}

-- Conceptual example: relational tables surfaced as updatable JSON views (design pattern)
-- (Exact DDL depends on your schema and duality view definition.)

Vector Data Type for AI Vector Search

Oracle 23ai introduces the VECTOR data type and AI Vector Search capabilities for semantic similarity queries. This enables storage of embeddings and “nearest neighbor” style retrieval directly in the database. :contentReference[oaicite:1]{index=1}

-- Example: vector column for embeddings
CREATE TABLE doc_store (
  doc_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  doc_text   CLOB,
  embedding  VECTOR
);

Boolean Data Type in SQL

Oracle 23ai supports a standard-compliant BOOLEAN data type in SQL, reducing the need for NUMBER(1) or CHAR(1) conventions and simplifying predicates. :contentReference[oaicite:2]{index=2}

CREATE TABLE feature_flags (
  feature_name  VARCHAR2(100) PRIMARY KEY,
  enabled       BOOLEAN
);

Immutable and Blockchain Tables

Immutable and blockchain tables support tamper-evident, retention-oriented designs. Blockchain tables organize rows into hash-linked chains to detect modification attempts; immutable tables are insert-only with retention controls. :contentReference[oaicite:3]{index=3}

-- Illustrative example (exact options vary by use case)
-- CREATE BLOCKCHAIN TABLE audit_chain (...) NO DROP UNTIL ... NO DELETE UNTIL ...;

The most important architectural shift since Oracle 11g is the multitenant architecture. Oracle 23ai environments are built around: Container Databases (CDB) and Pluggable Databases (PDB). From a tuning perspective, this affects resource management, consolidation strategy, and how you interpret performance views (for example, system-wide vs PDB-specific workload signals).

In later lessons, you will connect these structures to monitoring views and tuning workflows, such as identifying heavy SQL, analyzing waits, and interpreting memory usage in multitenant deployments.

Oracle still supports object-relational features that were introduced to model complex business objects directly in the database: user-defined types (ADTs), nested tables, VARRAYs, object references, and object views. These features remain valid tools, but in modern application design they are typically used for specialized requirements (strong typing in PL/SQL, controlled collection behavior, or legacy compatibility), not as a default replacement for normalization.

When you choose an object-relational structure, you must evaluate performance tradeoffs carefully: fewer joins can be attractive, but embedded collections can introduce storage overhead, complicated access paths, and harder cardinality estimation. In many systems, JSON duality views now provide an alternative approach for “document access with relational storage.” :contentReference[oaicite:4]{index=4}

Object-relational opportunities discussed in this module include:

1. User-defined data types (Abstract Data Types, ADTs)
2. Nested tables
3. VARRAYs (repeating groups stored as a column collection)
4. Object identifiers and references between objects
5. Aggregate objects composed of references to other objects

Oracle is designed for high concurrency. It coordinates access automatically so many sessions can read and write data safely. Most application code does not need explicit locking beyond normal transaction boundaries. However, when a business workflow requires exclusive control (for example, “reserve inventory row X and prevent other sessions from touching it”), Oracle provides explicit locking mechanisms.

Common locking controls:

1. LOCK TABLE to lock an entire table in a specific lock mode.
2. SELECT ... FOR UPDATE to lock selected rows while you perform dependent work in the same transaction.

-- Row-level lock pattern
SELECT *
FROM orders
WHERE order_id = :id
FOR UPDATE;

-- Table-level lock pattern
LOCK TABLE orders IN EXCLUSIVE MODE;

As with any tuning decision, use explicit locks sparingly: they can reduce concurrency and increase waiting. A performance-friendly design aims to lock the minimum data for the minimum time, with indexes and access paths that keep transactions short.

In Oracle, a schema is a collection of logical objects owned by a user: tables, indexes, views, procedures, and other objects. The database engine maintains a clear separation between physical storage and logical structure, which lets you tune and evolve a system without rewriting every application query.

In the next lessons, you will move from this overview into hands-on use of specific structures (indexes and collections) and measure how each choice can help—or hinder—SQL performance.