5 Things DBAs Must Know About JSON Relational Duality in Oracle AI Database 26ai

Posted on by sanjeeva
Architecture diagram showing how Oracle AI Database 26ai JSON Relational Duality Views translate JSON writes into row-level DML on underlying relational tables, with lock icons indicating concurrent access behavior.

Last month we were reviewing a staging incident where an application team had migrated their REST layer to use JSON Relational Duality Views in Oracle AI Database 26ai. The feature worked exactly as advertised during unit testing — clean JSON in, clean JSON out, relational tables staying normalized underneath. Then they ran a load test simulating 200 concurrent users updating order records. Wait events spiked. Row lock contention appeared on tables the application team had never touched directly. The AWR report pointed squarely at the base tables behind the duality view, not at the view itself.

They were surprised. We were not. JSON Relational Duality looks seamless from the application side. From the DBA side, there is a lot happening underneath that the documentation summarizes in two sentences.

Validated on: Oracle AI Database 26ai Enterprise Edition Release 23.26.1.2.0 — Production

This post covers the five things we needed to understand before duality views went anywhere near a production workload — using our ora_errors dataset from earlier in this series to keep the examples grounded.

What JSON Relational Duality Actually Is — The DBA Version

The Oracle marketing summary is: “One object, two faces — JSON and relational simultaneously.” That is accurate but incomplete for our purposes.

The more precise DBA description is this: a JSON Relational Duality View is a database object that sits on top of one or more normalized relational tables and presents their data as a JSON document. Reads return JSON. Writes accept JSON. But there is no JSON storage anywhere. The underlying tables hold the data in relational form, exactly as they always did.

That distinction matters because it changes how we think about the feature operationally:

What it isWhat it is NOT
A view layer translating relational rows to/from JSONA JSON document store like MongoDB
Writes that decompose into row-level DML on base tablesA separate copy or shadow table of the data
Locking governed by the base table row structureA JSON column with a trigger attached
Query execution grounded in the relational execution engineA middleware API layer outside the database

We are going to use the ora_errors table we built in Post 2 as our base table throughout this post. Here is a quick reminder of the structure:

DESC ora_errors;

-- Name            Null?    Type
-- -------------- -------- -----------------------
-- ERROR_ID       NOT NULL NUMBER
-- ERROR_CODE     NOT NULL VARCHAR2(20)
-- ERROR_TITLE    NOT NULL VARCHAR2(200)
-- DESCRIPTION    NOT NULL VARCHAR2(1000)
-- ERROR_CATEGORY NOT NULL VARCHAR2(50)
-- SEVERITY_LEVEL NOT NULL VARCHAR2(20)
-- EMBEDDING               VECTOR(8,FLOAT32,DENSE)

Now we create the simplest possible duality view on top of it:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW ora_errors_dv AS
SELECT JSON {
    '_id'           : e.error_id,
    'errorCode'     : e.error_code,
    'errorTitle'    : e.error_title,
    'description'   : e.description,
    'errorCategory' : e.error_category,
    'severityLevel' : e.severity_level
}
FROM ora_errors e WITH INSERT UPDATE DELETE;

A SELECT from ora_errors_dv returns rows that look like this:

{
  "_id"          : 1,
  "_metadata"    : { "etag" : "\"A3F9B1C2D4E5F6A7\"", "asof" : "00000000018C4F2A" },
  "errorId"      : 1,
  "errorCode"    : "ORA-04031",
  "errorTitle"   : "Shared Pool Exhaustion",
  "description"  : "Unable to allocate bytes of shared memory",
  "errorCategory": "Memory",
  "severityLevel": "Critical"
}

Notice the _metadata block Oracle injects automatically. That is not something we defined. It is there on every document returned by every duality view, and it is central to Thing 4.

Thing 1: The WITH Clause Controls DML Access — And Most Teams Get It Wrong

Every duality view has a WITH clause at the table level. The options are:

WITH INSERT UPDATE DELETE      -- full DML access
WITH NODELETE                  -- inserts and updates only
WITH NOUPDATE                  -- inserts and deletes only
WITH NOINSERT                  -- updates and deletes only
WITH INSERT ONLY               -- inserts only, no updates or deletes
WITH READ ONLY                 -- no DML at all through this view

The default, if you write WITH INSERT UPDATE DELETE, is full access. That sounds fine until you realize that in a production environment, the application teams consuming the duality view may not be the same teams who understand the base table constraints.

In our ora_errors context, consider that ERROR_ID is the primary key and EMBEDDING is a VECTOR column that gets populated by a separate embedding pipeline — not by the application. If we expose the duality view with full INSERT access, a developer can insert a new error record through the JSON view and leave the EMBEDDING column null. The relational table allows it. The vector index does not break. But the embedding pipeline now has an orphaned record with no vector, and our hybrid search from Post 4 starts returning incomplete results.

The safer pattern for ora_errors_dv is:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW ora_errors_dv AS
SELECT JSON {
    '_id'           : e.error_id,
    'errorCode'     : e.error_code,
    'errorTitle'    : e.error_title,
    'description'   : e.description,
    'errorCategory' : e.error_category,
    'severityLevel' : e.severity_level
}
FROM ora_errors e WITH UPDATE NODELETE;
-- Applications can update category and severity.
-- Inserts and deletes go through the relational layer only.

The WITH clause is not just an access control nicety. It is the DBA’s primary governance boundary on who can do what to the underlying tables through the JSON interface. Define it deliberately, not by default.

Thing 2: Writes Decompose Into Relational DML — And That Affects Every Wait Event You Know

This is the one that caught the application team in our staging incident. When an application sends a JSON document to a duality view via an UPDATE, Oracle does not store the JSON. It parses the incoming document, identifies which fields map to which base table columns, and issues standard row-level DML against those tables.

That means:

  • Row locks are acquired on the base table rows, not on any JSON structure
  • Redo is generated at the base table level, just as if the application had issued a direct UPDATE
  • Rollback segments are allocated at the base table level
  • Deadlock detection operates on the base table row locks — two sessions updating different fields of the same JSON document will contend if they map to the same underlying row

Let us trace what happens when an application updates the severityLevel field for errorId 1 through our view:

-- Application sends this PATCH via REST or direct SQL:
UPDATE ora_errors_dv e
SET    e.data = JSON_TRANSFORM(e.data, SET '$.severityLevel' = 'High')
WHERE  e.data."_id" = 1;

-- What Oracle actually executes against the base table:
-- UPDATE ora_errors
-- SET    severity_level = 'High'
-- WHERE  error_id = 1;

The execution is a direct row-level UPDATE on ora_errors. If another session has that row locked — for any reason, including a direct SQL update from a DBA session — the duality view UPDATE will wait on enq: TX - row lock contention, exactly as it would for any row lock.

We verify this in V$SESSION:

SELECT s.sid,
       s.serial#,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       s.sql_id
FROM   v$session s
WHERE  s.wait_class != 'Idle'
AND    s.username   = 'DBAVERSE'
ORDER  BY s.seconds_in_wait DESC;

--   SID  SERIAL# EVENT                          WAIT_CLASS  SECONDS_IN_WAIT SQL_ID
-- ----- -------- ------------------------------ ----------- --------------- -------------
--   127     4421 enq: TX - row lock contention  Application              47 6km3p9fwrqa8x

The wait event is identical to a standard row lock. Nothing about the JSON duality view surface makes the lock behavior different. AWR will report it correctly. The ash drill-down will show the blocking session. Our standard lock analysis workflow applies without modification.

The practical implication for capacity planning: if a high-concurrency application is updating the same ora_errors rows through a duality view that a batch job is also touching via direct SQL, they will contend. The JSON abstraction does not create any buffer between them.

Thing 3: ETags Are Optimistic Locking — Not Optional in Production

Every JSON document returned by a duality view includes a _metadata.etag field. This is Oracle’s mechanism for optimistic concurrency control, and it is not cosmetic.

When an application reads a document and then attempts to update it, the UPDATE must include the ETag that was returned with the read. Oracle validates that the ETag still matches the current state of the underlying row before applying the write. If another session has modified the row between the read and the write, the ETag has changed, and Oracle rejects the update with:

ORA-42699: duality view document has been modified by another user

The flow looks like this:

-- Step 1: Application reads the document
SELECT JSON_SERIALIZE(e.data PRETTY)
FROM   ora_errors_dv e
WHERE  e.data."_id" = 1;

-- Returns:
-- {
--   "_id"      : 1,
--   "_metadata": { "etag": "\"A3F9B1C2D4E5F6A7\"", "asof": "00000000018C4F2A" },
--   "errorCode"    : "ORA-04031",
--   ...
-- }

-- Step 2: Application constructs the update with the ETag
-- Two rules validated on Oracle AI Database 26ai:
-- Rule 1: _id must appear BEFORE _metadata in the JSON body
-- Rule 2: ETag value is a plain string — no escaped inner quotes
UPDATE ora_errors_dv e
SET    e.data = '{
                  "_id": 1,
                  "_metadata": { "etag": "D036A762A12E1C7CA7C6C4B5BF9AB096" },
                  "errorCode":"ORA-04031",
                  "errorTitle":"Shared Pool Exhaustion",
                  "description":"Unable to allocate bytes of shared memory",
                  "errorCategory":"Memory",
                  "severityLevel":"High"}'
WHERE  e.data."_id" = 1;

If the ETag in the submitted document does not match the current ETag on the row, Oracle rejects the write. The application must re-read the document, pick up the new ETag, and retry.

The DBA concern here is not the ETag logic itself — that is the application team’s domain. Our concern is what happens at scale:

  • High-concurrency workloads with frequent reads followed by delayed writes will generate a high rate of ORA-42699 errors
  • Those errors do not appear in AWR as wait events — they are application-level exceptions that Oracle raises immediately without waiting
  • If the application is not handling ORA-42699 with retry logic, those errors surface as failed transactions silently dropped or surfaced to the end user

We should verify ETag-related failures by monitoring at the application exception level, and by querying active SMON and transaction activity if we suspect high retry loops are generating excess redo:

SELECT name, value
FROM   v$sysstat
WHERE  name IN (
    'user commits',
    'user rollbacks',
    'execute count'
)
ORDER  BY name;

A high ratio of rollbacks to commits in a duality-view-heavy workload is a signal that ETag collisions are driving retry-rollback cycles.

Thing 4: Nested Object Writes Lock Multiple Base Table Rows Simultaneously

The ora_errors example is a flat, single-table duality view. Most production use cases are more complex — a parent document with nested child objects, each mapping to a separate base table.

Here is a multi-table duality view that extends our error tracking to include resolution steps:

-- Base tables for this example
CREATE TABLE ora_resolutions (
    resolution_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    error_id        NUMBER NOT NULL REFERENCES ora_errors(error_id),
    step_number     NUMBER NOT NULL,
    resolution_step VARCHAR2(1000)
);

-- Multi-table duality view
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW ora_error_full_dv AS
SELECT JSON {
    '_id'           : e.error_id,
    'errorCode'     : e.error_code,
    'errorTitle'    : e.error_title,
    'description'   : e.description,
    'errorCategory' : e.error_category,
    'severityLevel' : e.severity_level,
    'resolutionSteps' : [
        SELECT JSON {
            '_id'            : r.resolution_id,
            'stepNumber'     : r.step_number,
            'resolutionStep' : r.resolution_step
        }
        FROM   ora_resolutions r WITH INSERT UPDATE DELETE
        WHERE  r.error_id = e.error_id
    ]
}
FROM ora_errors e WITH UPDATE;

When an application updates an ora_error_full_dv document that includes changes to both the parent error record and one of its resolution steps, Oracle acquires row locks on both the ora_errors row and the corresponding ora_resolutions rows — within the same transaction.

The practical implication:

  • Lock order is always parent → child, following the nesting structure in the duality view definition
  • A write to the nested child only (e.g., updating just resolutionStep) still acquires a lock on the parent ora_errors row, because Oracle validates the parent’s ETag as part of the write
  • Deadlock risk increases when two sessions update documents that share base table rows in different nesting orders — a pattern that can appear when the same base table appears in multiple duality views with different nesting hierarchies

We check for this proactively by reviewing the duality view definitions against our base table access matrix before go-live:

-- Identify which base tables back our duality views
SELECT v.view_name,
       d.referenced_owner,
       d.referenced_name,
       d.referenced_type
FROM   user_views       v
JOIN   user_dependencies d
    ON d.name  = v.view_name
    AND d.type = 'VIEW'
WHERE  v.view_name LIKE '%_DV'
ORDER  BY v.view_name,
          d.referenced_name;

Any base table appearing in multiple duality views is a candidate for lock contention analysis before the application team adds concurrent writers.

Thing 5: The Execution Plan Looks Relational — Because It Is

One misconception we see regularly is that queries against duality views require special tuning approaches or that the JSON layer introduces an optimizer barrier. It does not.

Oracle parses the JSON predicate in a duality view query and translates it into a standard relational predicate against the base table. The execution plan reflects the base table access path:

EXPLAIN PLAN SET STATEMENT_ID = 'OE_DV_TEST' FOR
SELECT JSON_SERIALIZE(e.data PRETTY)
FROM   ora_errors_dv e
WHERE  e.data."errorCategory" = 'Memory';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'OE_DV_TEST', 'ALL'));

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Plan hash value: 2847391056
-- 
-- -----------------------------------------------------------------------
-- | Id | Operation          | Name       | Rows | Bytes | Cost (%CPU) |
-- -----------------------------------------------------------------------
-- |  0 | SELECT STATEMENT   |            |    3 |   612 |       3 (0) |
-- |* 1 |  TABLE ACCESS FULL | ORA_ERRORS |    3 |   612 |       3 (0) |
-- -----------------------------------------------------------------------
-- 
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--    1 - filter("ERROR_CATEGORY"='Memory')

The predicate data."errorCategory" = 'Memory' has been translated to filter("ERROR_CATEGORY"='Memory') — a direct column predicate on the base table. Standard index tuning applies. If we create an index on ORA_ERRORS(ERROR_CATEGORY), the optimizer uses it through the duality view, just as it would for a direct base table query.

CREATE INDEX idx_ora_errors_cat ON ora_errors(error_category);

-- Re-explain:
-- -----------------------------------------------------------------------
-- | Id | Operation                   | Name                | Rows | ... |
-- -----------------------------------------------------------------------
-- |  0 | SELECT STATEMENT            |                     |    3 | ... |
-- |  1 |  TABLE ACCESS BY INDEX ROWID| ORA_ERRORS          |    3 | ... |
-- |* 2 |   INDEX RANGE SCAN          | IDX_ORA_ERRORS_CAT  |    3 | ... |
-- -----------------------------------------------------------------------

This means our standard tuning toolkit works without modification for duality view performance work:

  • AWR SQL reports show the base table SQL, not a JSON-layer wrapper
  • SQL Tuning Advisor operates on the translated relational statement
  • Index recommendations from DBMS_SQLTUNE apply directly to the base tables
  • Bind variable analysis, cursor sharing, and cardinality estimation all work at the base table level

The one area where duality views add complexity to the execution plan is multi-table documents with nested arrays. Oracle uses a correlated subquery or lateral join internally to assemble the nested child rows. Those show up in the plan as additional TABLE ACCESS operations on the child table. Cardinality estimates for the nested collections can be off if statistics on the child table are stale — the usual solution.

Summary — The Five Things

#What to KnowWhy It Matters in Production
1WITH clause controls DML access per tableGovernance boundary for which layers can insert, update, or delete
2Writes decompose to relational DML with row-level locksStandard wait event analysis applies — no special duality view instrumentation needed
3ETags enforce optimistic locking — ORA-42699 on collisionApplication must implement retry; high retry rate generates excess redo
4Nested object writes lock parent AND child rowsMulti-table duality view designs need lock order analysis before go-live
5Execution plans are relational — standard tuning appliesAWR, SQL Tuning Advisor, and index recommendations work without modification

Where We Go From Here

Post 6 covered the DBA mechanics of JSON Relational Duality — what happens underneath the clean JSON surface and where production risk actually lives.

The next posts in Pillar 1 continue the hands-on progression:

If your team has hit unexpected behavior with JSON Relational Duality Views in 26ai — particularly around locking or ETag retry loops — drop it in the comments. The staging incident that opened this post was not a one-off.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.