Skip to main content

Oracle Database 23ai - New Features for DBAs

By Gowthami | apps-dba.com | Oracle Database Series

Oracle Database 23ai (formerly known as Oracle Database 23c) is Oracle's latest long-term support release, bringing a significant leap forward with native AI integration directly into the database engine. This post covers the key new features that every Oracle DBA should know about.

Key Insight: Oracle 23ai introduces AI-native capabilities including Vector Search, JSON Relational Duality Views, and True Cache—fundamentally changing how DBAs design and query databases.

AI Vector Search

One of the most significant additions in Oracle 23ai is AI Vector Search. This feature allows the database to store, index, and query vector embeddings natively, enabling semantic similarity searches alongside traditional SQL queries. The new VECTOR data type stores high-dimensional embeddings.

-- Create a table with a vector column
CREATE TABLE documents (
  id      NUMBER PRIMARY KEY,
  content CLOB,
  embedding VECTOR(1536, FLOAT32)
);

-- Similarity search using cosine distance
SELECT content
FROM documents
ORDER BY VECTOR_DISTANCE(embedding, :query_vec, COSINE)
FETCH FIRST 5 ROWS ONLY;

Schema Privileges with SQL Domains

Oracle 23ai introduces SQL Domains, a new schema-level object for centralizing column constraints and properties. DBAs can define a domain once and reuse it across many tables, improving consistency and maintainability.

-- Define a reusable domain
CREATE DOMAIN email_address AS VARCHAR2(255)
  CONSTRAINT email_check CHECK (REGEXP_LIKE(VALUE, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$'));

-- Apply domain to a column
CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  email  email_address
);

JSON Relational Duality Views

A game-changing feature: JSON Relational Duality Views let you expose relational tables as JSON documents and vice versa with full read/write support. This bridges the gap between relational and document-oriented data models.

-- Create a duality view over relational tables
CREATE JSON RELATIONAL DUALITY VIEW emp_dv AS
  SELECT JSON {
    'emp_id'   : e.emp_id,
    'name'     : e.last_name,
    'dept'     : d.dept_name
  }
  FROM employees e
  JOIN departments d ON e.dept_id = d.dept_id
  WITH INSERT UPDATE DELETE;

True Cache for Read Offloading

True Cache is a read-only cache of the primary database that offloads read workloads without requiring application changes. Unlike Oracle GoldenGate replicas, True Cache stays synchronized using redo logs, providing transparent performance improvements.

Developer-Friendly Features

  • IF [NOT] EXISTS for DDL: CREATE TABLE IF NOT EXISTS simplifies deployment scripts
  • Table Value Constructors: Insert multiple rows cleanly and efficiently
  • Annotations: Attach metadata to schema objects for better documentation
  • Property Graph: Graph queries using SQL/PGQ standard

Summary

Oracle 23ai brings AI-native capabilities, developer ergonomics, and architectural innovations that every DBA should understand. From Vector Search to Duality Views, these features change how you design and query databases, making it essential to stay current with this latest release.

Oracle Exadata - The Complete Guide

Want to master every aspect of Exadata? Smart Scan, Storage Indexes, Hybrid Columnar Compression, and Exadata-specific SQL tuning techniques. Get Gowthami's complete guide.

Get the Book
Labels:

Comments

Post a Comment

Popular posts from this blog

Data Safe - Introduction

Oracle Data Safe - Practical Guide Oracle Data Safe learner guide Oracle Data Safe Assess risk, discover sensitive data, audit activity, and mask safely It focuses on what Data Safe helps you do operationally: review security posture, find risky identities, centralize auditing, locate sensitive data, and produce safer non-production copies. Contents 01 Why Data Safe matters 02 Where it fits 03 Capability map 04 Assessments 05 Activity Auditing 06 Discovery and Masking 07 Operating model 08 First 30 days 09 Knowledge check Section 01 Why Data Safe matters Database security work is often fragmented. One process checks configuration drift, another stores audit logs, another team scans for PII, and another team writes masking logic for test refreshes. Data Safe is useful because it turns those separate jobs into one security workflow. Key idea The best way to think about Data Safe is as a control plane for database security posture: assess the target, identify risky accounts, d...
Post a Comment
Read more

Testing Different Access Paths : Concatenated Index

Oracle Concatenated Indexes - Practical Deep Dive Oracle concatenated index deep dive Concatenated Indexes How composite indexes really work, why column order matters, and when skip scan changes the story Concatenated indexes, also called composite indexes, are easy to explain badly and surprisingly rich to explain well. The usual summary is “Oracle can use the index only when the leading column is present,” but that is only the starting point. To design them properly, you need to think about leading portions, equality versus range predicates, ordering requirements, skip scan eligibility, covering behavior, and whether one composite index can replace several single-column indexes in a given workload. Contents 01 What concatenated indexes are 02 Leading edge and leading portion 03 Why column order matters 04 Skip scan and when it helps 05 Access patterns and plan reading 06 Covering and sort elimination 07 Design rules that actually hold 08 Common mistakes 09 End-to-end demo 1...
Post a Comment
Read more

Database Replay - Real Application Testing (RAT)

Oracle Database Replay and RAT - Practical Deep Dive Oracle Database Replay deep dive Database Replay and Real Application Testing How to validate upgrades, patches, migrations, and risky changes with real workload behavior Database Replay is one of the most practical risk-reduction tools in the Oracle DBA toolbox. Instead of trusting synthetic benchmarks, isolated SQL tests, or intuition, you capture a real production workload, restore a test system to the same logical starting point, replay that workload, and analyze whether performance, errors, timing, and transactional behavior still look safe. Contents 01 What RAT actually is 02 Why Database Replay matters 03 End-to-end workflow 04 Capture design and prerequisites 05 Preprocess, calibrate, replay 06 Reading the results well 07 Pitfalls and unreplayable work 08 Database Replay vs SPA 09 Practical playbooks 10 Knowledge check Section 01 What Real Application Testing actually is Real Application Testing, usually shortened...
Post a Comment
Read more