Oracle AI Database 26ai — SQL

Comprehensive notes on new SQL features, enhancements, and developer capabilities in Oracle AI Database 26ai (Release 23.26.1).

📑 Table of Contents

1. AI Vector Search New

Oracle AI Database 26ai introduces AI Vector Search, a semantic search capability that allows you to query data based on meaning rather than just keywords. This is achieved through a native VECTOR data type and a suite of SQL functions and operators.

AI Vector Search represents a paradigm shift in how data is queried. Instead of relying on exact keyword matches, it leverages mathematical representations called embeddings. These embeddings capture the semantic meaning of data—such as the context of a sentence or the visual features of an image. By performing distance calculations on these vectors, the database can retrieve results that are contextually similar to the query, even if they don't contain the same exact words.

This technology is powered by in-database embedding models, which run directly on the database engine. This eliminates the latency and security risks of moving data to external AI services. By embedding the AI pipeline directly into the SQL engine, Oracle ensures that vector operations are fully transactional and consistent with traditional relational data, bridging the gap between structured business data and unstructured AI insights.

💡 Key Benefit: Combine semantic search on unstructured data (images, documents) with relational search on business data — all in a single system, eliminating data fragmentation.

VECTOR Data Type

Oracle AI Database 26ai introduces the VECTOR data type to support AI workloads. Vectors can be dense or sparse, and can be stored in columns, used in SQL queries, and manipulated with vector-specific functions. This native type supports various vector dimensions and precision levels, allowing developers to store high-dimensional embeddings directly in database tables alongside standard scalar values.

-- Create a table with a VECTOR column
CREATE TABLE house_for_sale (
    house_id    NUMBER,
    price       NUMBER,
    city        VARCHAR2(400),
    house_photo BLOB,
    house_vector VECTOR
);

Key Vector SQL Functions

Example: Combined Semantic + Relational Query

This example demonstrates the power of hybrid search: filtering houses by price and city (relational predicates) while simultaneously sorting by semantic similarity to a reference image or description (vector distance).

SELECT house_photo, city, price
FROM house_for_sale
WHERE price <= :input_price
  AND city = :input_city
ORDER BY VECTOR_DISTANCE(house_vector, :input_vector);

Vector Indexes

Oracle 26ai supports multiple vector index types for high-performance similarity search. Indexing is critical for production workloads because computing distance against millions of vectors without an index can be prohibitively slow. The database provides several indexing strategies to balance accuracy, memory consumption, and query speed.

HNSWHierarchical Navigable Small World — efficient approximate nearest neighbour search. Ideal for high-recall, low-latency queries.
IVFInverted File Index — scalable for large vector datasets. Offers a trade-off between speed and accuracy, suitable for billion-scale datasets.
Hybrid Vector IndexCombines vector search with traditional relational predicates, pruning candidates based on structured filters before vector distance computation.
Scalar Quantized HNSWReduces memory footprint while maintaining accuracy by compressing vector dimensions, ideal for memory-constrained environments.

Additional Vector Features:

2. JSON Relational Duality New

JSON Relational Duality is a breakthrough data modelling capability that provides fully updatable JSON document views over relational data. Data stays stored efficiently in normalized relational tables but can be accessed by applications as simple JSON documents — and vice versa.

Traditionally, developers had to choose between the flexibility of JSON documents and the integrity of relational tables. JSON Relational Duality transcends this limitation. It allows the same underlying normalized data to be exposed as a rich, nested JSON document for modern microservices, while simultaneously remaining fully ACID-compliant and joinable in SQL for traditional analytics. This eliminates the infamous "impedance mismatch" between object-oriented code and relational databases.

For a typical e-commerce application, a customer record and their associated orders can be exposed as a single nested JSON document. Any update to the JSON document is atomically decomposed into the respective relational tables, ensuring referential integrity. This dramatically simplifies application logic by removing the need for complex ORM (Object-Relational Mapping) frameworks, as the database itself handles the transformation bidirectionally.

✨ Key Advantage: No more object-relational mapping (ORM) complexity. The same data can be a relational tuple and a nested JSON document simultaneously.

Creating a Duality View

Duality views can be created using a concise, GraphQL-based syntax. This syntax allows developers to define exactly how nested JSON documents should be structured from the underlying relational schema.

-- Example: Create a JSON duality view over relational tables
CREATE JSON DUALITY VIEW customer_orders AS
SELECT JSON {
    'customer_id' : c.id,
    'name'        : c.name,
    'email'       : c.email,
    'orders'      : [ SELECT JSON {
                        'order_id'   : o.id,
                        'order_date' : o.order_date,
                        'total'      : o.total
                      } FROM orders o WHERE o.customer_id = c.id ]
} FROM customers c;

Key JSON Duality Features

Combine JSON Relational Duality with AI Vector Search for hybrid AI+JSON applications. This combination allows developers to build sophisticated AI applications that store and query both structured JSON metadata and semantic vectors within the same unified platform.

3. Operational Property Graphs in SQL New

Oracle AI Database 26ai introduces SQL Property Graphs, allowing you to create and query property graphs using standard SQL, as defined in the SQL:2023 standard. This replaces the earlier PGQL (Property Graph Query Language) approach. Property graphs are essential for modelling complex relationships where connections between entities are as important as the entities themselves.

Graph analytics are indispensable in domains such as fraud detection, where tracing connections between accounts reveals money laundering rings; in social networks, where identifying communities of influence is key to marketing; and in supply chain management, where mapping dependencies between suppliers, manufacturing plants, and retailers helps mitigate risk. Oracle's native SQL graph support brings these capabilities directly into the operational database, enabling real-time graph queries on live transactional data without the need for data duplication into separate graph databases.

Key Capabilities

-- Example: Creating a property graph from relational tables
CREATE PROPERTY GRAPH social_network
  VERTEX TABLES (persons)
  EDGE TABLES (friendships
    SOURCE persons(id) REFERENCES persons(id)
    DESTINATION persons(id) REFERENCES persons(id)
  );

-- Query the graph using SQL/PGQ
SELECT *
FROM GRAPH_TABLE (social_network
  MATCH (a IS persons) -[e IS friendships]-> (b IS persons)
  WHERE a.city = 'London'
  COLUMNS (a.name, b.name, e.since)
);

Note: SQL Property Graphs are supported only in Oracle AI Database 26ai. This is a significant differentiator from earlier Oracle versions, which relied on separate graph tools.

4. SQL Firewall New

SQL Firewall is a new security feature built directly into the Oracle AI Database 26ai kernel. It inspects all incoming SQL statements and ensures that only explicitly authorized SQL is executed. In today's threat landscape, SQL injection and credential abuse remain top attack vectors.

Traditional Web Application Firewalls (WAF) operate outside the database and are easily bypassed by encrypted traffic or sophisticated obfuscation techniques. Oracle's SQL Firewall shifts the defensive perimeter directly into the kernel. By embedding protection inside the database, it can inspect the actual parsed SQL tree rather than relying on network packet inspection, making it immune to evasion techniques. Furthermore, it uses session context (IP address, OS user, program name) to enforce context-aware policies—allowing a DBA to run administrative queries from a trusted machine while blocking the same query from an untrusted web server.

🛡️ Real-Time Protection: SQL Firewall mitigates SQL injection attacks, anomalous access, and credential theft or abuse. It cannot be bypassed because it's embedded in the database kernel.

How SQL Firewall Works

Configuration via DBMS_SQL_FIREWALL

The lifecycle of a SQL Firewall policy typically involves a "learning" phase where SQL statements are collected, followed by policy generation and enforcement. This is managed entirely through PL/SQL APIs.

-- Enable SQL Firewall
EXEC DBMS_SQL_FIREWALL.ENABLE;

-- Start SQL collection for a specific user
EXEC DBMS_SQL_FIREWALL.START_COLLECTION('SCOTT');

-- Generate and enforce policy
EXEC DBMS_SQL_FIREWALL.GENERATE_POLICY('SCOTT');
EXEC DBMS_SQL_FIREWALL.ENABLE_POLICY('SCOTT');

SQL Firewall is part of Oracle Database Vault and can be used in both CDB root and PDBs, providing centralized protection across multitenant architectures.

5. Data Use Case Domains New

A Data Use Case Domain is a dictionary object that encapsulates properties and constraints for common values like email addresses, credit card numbers, URLs, or passwords. This feature shifts data validation from the application layer to the database layer.

By centralizing validation logic inside the database, organizations ensure consistent data quality across all consuming applications. When a domain is updated (for example, to strengthen password complexity rules), all tables using that domain automatically inherit the new rule without requiring application code changes. This significantly reduces maintenance overhead and enforces enterprise-wide data standards.

-- Define a domain for email addresses
CREATE DOMAIN email_domain AS VARCHAR2(320)
  CONSTRAINT email_format CHECK (REGEXP_LIKE(VALUE, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'));

-- Use the domain in a table
CREATE TABLE employees (
    emp_id   NUMBER,
    emp_name VARCHAR2(100),
    emp_email email_domain   -- inherits validation
);

Applications can use this metadata to automatically generate validation code or UI components. Front-end frameworks can query the data dictionary to build dynamic forms with pre-configured validation, creating a self-documenting and self-validating data layer.

6. Assertions New

Assertions allow you to define declarative business rules on one or more tables, ensuring that DML operations always honour those rules. Unlike traditional check constraints which are limited to a single table, assertions can enforce complex business invariants that span multiple tables.

Assertions embed business logic directly in the database, providing a clear, declarative approach to data integrity that goes beyond traditional constraints. This is particularly useful for enforcing financial rules (e.g., "total debits equals total credits across all ledgers") or operational constraints (e.g., "no more than 5 active projects per employee"). The database optimizer checks these rules at the end of each transaction, guaranteeing that the system is always in a compliant state.

-- Example: Ensure that an employee's salary never exceeds their department's budget
CREATE ASSERTION salary_within_budget
CHECK (
    NOT EXISTS (
        SELECT 1
        FROM employees e
        JOIN departments d ON e.dept_id = d.id
        WHERE e.salary > d.budget
    )
);

7. Lock-Free Reservations New

Lock-Free Reservation enables concurrent transactions to proceed without being blocked on updates of heavily updated rows. Instead of locking rows, reservations are held and verified at commit time. This is a revolutionary approach to concurrency control in high-contention environments.

In traditional locking, a transaction updating a popular inventory item would block all other transactions attempting to update the same item, creating a performance bottleneck. Lock-Free Reservations invert this model: transactions proceed speculatively and only perform a final verification at commit time. If a conflict is detected, the transaction is rolled back, but because the bottleneck is deferred, overall throughput improves dramatically. This is particularly effective for retail, ticketing, and financial trading systems where thousands of requests per second target the same resources.

⚡ Concurrency Boost: Improves end-user experience and concurrency by deferring row-level contention until commit time. 
-- Example: Using lock-free reservation for an inventory system
UPDATE products
SET quantity = quantity - :order_qty
WHERE product_id = :pid
  AND quantity >= :order_qty
RESERVE FOR UPDATE;   -- reserves the row without blocking others

8. Wide Tables New

Oracle AI Database 26ai increases the column limit for tables and views from 1,000 to 4,096 columns by setting the MAX_COLUMNS initialization parameter to EXTENDED. This expansion is critical for modern AI and IoT applications, which often generate thousands of feature columns from sensor data or embedding vectors.

For enterprises migrating legacy systems from other databases that already support higher column limits, this removes a major porting hurdle. Additionally, it facilitates denormalized data modelling, allowing analysts to store flat tables with all relevant dimensions without performing complex joins—accelerating query performance in data warehousing scenarios.

-- Enable wide tables
ALTER SYSTEM SET MAX_COLUMNS = EXTENDED;

-- Create a table with 4000 columns
CREATE TABLE wide_table (
    col1   NUMBER,
    col2   VARCHAR2(100),
    -- ... up to 4096 columns ...
);

Important Notes:

9. SQL Language Enhancements New

Oracle AI Database 26ai introduces several SQL syntax simplifications and new functions designed to boost developer productivity and reduce code verbosity. These enhancements reflect decades of feedback from the developer community, focusing on making SQL more intuitive and less repetitive.

GROUP BY ALL

Automatically groups by all non-aggregated columns in a query, reducing repetitive SQL syntax. This eliminates the common error of forgetting to add a new column to the GROUP BY clause when modifying the SELECT list, thus making queries more maintainable and less error-prone.

-- Old way
SELECT department, region, SUM(salary)
FROM employees
GROUP BY department, region;

-- New way with GROUP BY ALL
SELECT department, region, SUM(salary)
FROM employees
GROUP BY ALL;   -- automatically groups by department and region

DATEDIFF Function

New DATEDIFF function to find datetime boundaries crossed, supporting units from years down to nanoseconds. This simplifies date arithmetic significantly, replacing complex formulaic calculations with a single, readable function call.

SELECT DATEDIFF('day', hire_date, SYSDATE) AS days_employed
FROM employees;

Non-Positional INSERT

Insert using column names without positional mapping. This improves code clarity and prevents data corruption when the underlying table schema changes (e.g., when a new column is added).

INSERT INTO employees
    (emp_id, emp_name, hire_date)   -- columns specified
VALUES
    (101, 'Alice', SYSDATE);        -- values map by name, not position

IF [NOT] EXISTS Syntax

DDL operations now support IF EXISTS and IF NOT EXISTS to control error handling. This is a game-changer for scripting and CI/CD pipelines, allowing idempotent deployments without writing complex PL/SQL blocks to check for object existence.

DROP TABLE IF EXISTS temp_data;
CREATE TABLE IF NOT EXISTS logs (log_id NUMBER, message VARCHAR2(4000));

Calendar Functions & Aggregation Filters

SELECT department,
       SUM(salary) AS total_salary,
       SUM(salary) FILTER (WHERE bonus > 1000) AS bonus_above_1000
FROM employees
GROUP BY department;

Flashback Time Travel Enhancements

Automatically tracks and archives transactional changes to tables, maintaining history of both data and schema evolution. This simplifies compliance with data retention regulations (like GDPR) and enables "point-in-time" queries without restoring from backups.

Blockchain Table Log History

Tracks changes to regular tables in a blockchain table, maintained as part of Flashback Data Archive. This provides an immutable audit trail of who changed what and when, which is essential for financial ledgers and certified audit processes.

Implicit Connection Pooling (DRCP)

Automatic assignment of Database Resident Connection Pooling (DRCP) servers at runtime, even when applications don't explicitly close connections. This greatly reduces the overhead of establishing new database sessions for high-volume web applications, leading to faster response times and more efficient resource usage.

10. Select AI (Natural Language to SQL) AI

Select AI provides a natural-language layer over enterprise data, enabling users to generate SQL, chat with data, and create synthetic data. This bridges the gap between business users and technical data engineers, making data insights accessible to everyone in the organization.

Select AI translates natural language questions into optimized SQL, enabling non-technical stakeholders to derive insights instantly. Imagine a marketing manager asking, "Show me revenue growth for the last quarter by product category" and immediately receiving the correct aggregated result without writing a single line of SQL. This is made possible by a large language model (LLM) integrated into the database pipeline.

In release 26ai, Select AI introduces Feedback and Auto Object Selection. The feedback loop allows users to correct or rate the generated SQL, which fine-tunes the model for the specific enterprise schema, improving accuracy over time. Auto Object Selection uses semantic similarity to automatically choose the correct tables and columns from the schema, reducing the need for users to specify table names—a significant step toward fully autonomous AI-driven analytics.

NL2SQLDescribe results in natural language; Select AI generates the SQL. Supports complex joins, filters, and aggregations.
Feedback (26ai)Users can provide feedback on generated SQL to improve accuracy over time, creating a self-improving analytic engine.
Auto Object Selection (26ai)Automatically selects relevant table metadata using semantic similarity, removing the need for explicit table references in prompts.
RAG SupportRetrieval-augmented generation for grounded responses, ensuring the AI uses enterprise-specific context before generating SQL.

Select AI supports multiple AI providers: OCI Generative AI, OpenAI, Azure OpenAI, Cohere, Google, Anthropic, Hugging Face, Amazon, and private LLM deployments. This flexibility ensures that organizations can use their preferred AI models while maintaining data sovereignty.

-- Example: Using Select AI from SQL*Plus
SELECT AI 'Show me all employees hired in the last 30 days, grouped by department';

11. Performance & Tuning New

Oracle AI Database 26ai brings significant performance enhancements for SQL workloads. Managing AI workloads introduces new performance bottlenecks, primarily around memory bandwidth and parallel scan operations. Oracle 26ai addresses these with sophisticated optimizations that span traditional OLTP, data warehousing, and vector processing.

SQL Analysis Reports

New SQL Analysis Reports highlight inefficient SQL patterns and provide actionable tuning recommendations. Unlike traditional explain plans which only show the execution path, these reports analyze historical trends, identify "slow by design" patterns, and suggest specific index creation or rewriting strategies.

Real-Time SQL Plan Management

Monitors SQL execution plans and automatically fixes regressions. If a plan becomes suboptimal due to changing data distributions, the database can automatically revert to a known good plan or evolve the plan baseline, ensuring consistent performance for critical applications.

Priority Transactions

Handle lock contention by prioritising critical transactions over less important ones. This is invaluable in mixed-workload environments—ensuring that a high-priority executive report runs without being blocked by a routine batch maintenance job.

Telemetry Streaming

Ultra‑fast metrics ingestion for observability using REST, SQL, and PromQL. This allows integration with modern monitoring stacks (like Prometheus/Grafana) to provide real-time visibility into database performance metrics, query latency, and vector throughput.

Vector Performance Optimizations

Vector In-Lists

Efficient method for processing large SQL IN-lists, making analytics workloads faster. This optimization specifically targets filtering on high-cardinality column values, a common pattern in data warehouse queries.

📚 References:

All documentation links are for Oracle AI Database 26ai (Release 23.26.1).

© Copyright Westminster College of AI and Quantum Computing Limited, 2026. | info@wcc.co.uk | https://wcc.co.uk