Single-Cluster Duality View 🃏

In DynamoDB, a single-table design stores one-to-many relationships in a single physical block while still following relational-like normal form decomposition. In MongoDB, the Single Collection Pattern unnests relationships from a single document, but goes against the general recommendation as it sacrifices one of MongoDB’s key advantages—keeping a document in a single block. In Oracle Database and MySQL, JSON-relational duality views normalize JSON documents into relational tables that span multiple blocks, also without the data-locality benefits of MongoDB. Can we turn these duality views into a document-database equivalent that keeps together the data accessed together?

Here is the Single-Cluster Duality View 🥁.

DynamoDB

NoSQL started with a simple key‑value API. For example, DynamoDB can get an item using the full key, query multiple items using a partial key, or scan all items without a key. The value, in this key‑value datastore, is atomic, but sometimes you need partial reads or writes. Then you have two solutions: read or write the full item, which is inefficient if you don't need it, or change your schema design to split it into multiple items.

The latter was known as the single table design because it not only splits items into multiple entities but also stores them in the same table, sharing a key prefix, to retrieve all items with a single query. The idea is that you can access individual objects while also benefiting from data locality when querying the full aggregate.

The benefit of this design relies heavily on DynamoDB specifics: the storage internals, where items are partitioned and clustered by their key, and the billing model, where you pay per table request unit.

Here is an example, using the traditional one-to-many schema with departments employees:

• One DynamoDB table
• Department and employees stored as separate items
• Same partition key (DEPT#<deptno>) for both:

aws dynamodb create-table \
  --table-name scott \
  --attribute-definitions \
    AttributeName=PK,AttributeType=S \
    AttributeName=SK,AttributeType=S \
  --key-schema \
    AttributeName=PK,KeyType=HASH \
    AttributeName=SK,KeyType=RANGE \
  --billing-mode PAY_PER_REQUEST

The logical model, where the relationship is materialized by sharing the department number as the same partition key, is also the physical model in which they are stored together, since items are partitioned on this key:

PK	SK	Meaning
DEPT#10	DEPT	Department row
DEPT#10	EMP#7782	Employee
DEPT#10	EMP#7839	Employee
DEPT#20	DEPT	Department row
DEPT#20	EMP#7369	Employee

I insert some data

aws dynamodb put-item --table-name scott --item '{
  "PK": {"S": "DEPT#10"},
  "SK": {"S": "DEPT"},
  "deptno": {"N": "10"},
  "dname": {"S": "ACCOUNTING"},
  "loc": {"S": "NEW YORK"}
}'

aws dynamodb put-item --table-name scott --item '{
  "PK": {"S": "DEPT#10"},
  "SK": {"S": "EMP#7782"},
  "empno": {"N": "7782"},
  "ename": {"S": "CLARK"},
  "job": {"S": "MANAGER"},
  "sal": {"N": "2450"}
}'

Here is a query that retrieves the whole aggregate by its partition key:

aws dynamodb query \
  --table-name scott \
  --key-condition-expression "PK = :d" \
  --expression-attribute-values '{":d":{"S":"DEPT#10"}}' \
  --consistent-read \
  --return-consumed-capacity TOTAL 


{
    "Items": [
        {
            "deptno": { "N": "10" },
            "PK": { "S": "DEPT#10" },
            "loc": { "S": "NEW YORK" },
            "dname": { "S": "ACCOUNTING" },
            "SK": { "S": "DEPT" }
        },
        {
            "ename": { "S": "CLARK" },
            "PK": { "S": "DEPT#10" },
            "job": { "S": "MANAGER" },
            "empno": { "N": "7782" },
            "sal": { "N": "2450" },
            "SK": { "S": "EMP#7782" }
        }
    ],
    "Count": 2,
    "ScannedCount": 2,
    "ConsumedCapacity": {
        "TableName": "scott",
        "CapacityUnits": 1.0
    }
}

All items are read from a single partition with 1 consistent read capacity unit (RCU), thanks to physical colocation by key design.

MongoDB

MongoDB doesn't need to split documents to get partial reads and writes. The data modeling objective is to keep aggregates in a single document and use the advanced API to access individual items. For example, you can use covering indexes or search indexes with returnStoredSource to avoid reading the whole document. And you can use $push or $set with arrayFilters to update individual array items.

Still, some users preferred to split the documents and, by analogy to DynamoDB, called it the Single Collection Pattern. In truth, it is rarely useful. MongoDB is not DynamoDB. Storing documents in one or multiple collections doesn't impact billing, and using the same key prefix doesn't co‑locate them — except in two cases: clustered collections (special‑purpose, not generally recommended), and sharing a sharding key to co‑locate data on the same shard (but still different blocks in the filesystem).

At the storage block level, documents may be stored together only if they were inserted together.

Although this is not a recommendation when you use the full power of the MongoDB API, here is an example using a single collection and embedding only references, following the documentation: one document per department and one document per employee:

db.empdept.insertMany([
{
  _id: "DEPT#10",
  doc_type: "dept",
  deptno: 10,
  dname: "ACCOUNTING",
  loc: "NEW YORK",
  links: [
    { target: "DEPT#10", doc_type: "dept" },
    { target: "EMP#7782", doc_type: "emp" },
    { target: "EMP#7839", doc_type: "emp" }
  ]
},{
  _id: "EMP#7782",
  doc_type: "emp",
  empno: 7782,
  ename: "CLARK",
  job: "MANAGER",
  sal: 2450,
  deptno: 10,
  links: [
    { target: "EMP#7782", doc_type: "emp" },
    { target: "DEPT#10", doc_type: "dept" }
  ]
},{
  _id: "EMP#7839",
  doc_type: "emp",
  empno: 7839,
  ename: "KING",
  job: "PRESIDENT",
  sal: 5000,
  deptno: 10,
  links: [
    { target: "EMP#7839", doc_type: "emp" },
    { target: "DEPT#10", doc_type: "dept" }
  ]
}];

With this schema, the following gets the full aggregate as multiple documents without an aggregation pipeline:

db.empdept.find({ "links.target": "DEPT#10" })

This can identify the document using a single index range with a multi-key index on "links.target", but it will have to fetch multiple small documents, introduces strong coupling between those documents without the possibility of validating the reference, and requires an explicit transaction and retry logic to update them.

MongoDB is designed to store aggregates as a single document, and the right schema is simply:

db.empdept.insertOne({
  _id: 10,
  dname: "ACCOUNTING",
  loc: "NEW YORK",
  employees: [
    { empno: 7782, ename: "CLARK", job: "MANAGER", sal: 2450 },
    { empno: 7839, ename: "KING",  job: "PRESIDENT", sal: 5000 }
  ]
})

With this schema, you can still query the full aggregate with a simple find():

db.empdept.find({ _id: 10 })

and you can still update a single employee in‑place:

db.empdept.updateOne(
  { _id: 10, "employees.empno": 7782 },
  { $set: { "employees.$.sal": 2600 } }
)

This updates the first matching array item using the positional $ operator. You can be more precise with arrayFilters

db.empdept.updateOne(  
  { _id: 10 },  
  { $set: { "employees.$[e].sal": 2600 } },  
  { arrayFilters: [{ "e.empno": 7782 }] }  
)  

I've written about this and measured the efficiency in a previous post:

Updating "denormalized" aggregates with "duplicates": MongoDB vs. PostgreSQL

Franck Pachot

Franck Pachot

Franck Pachot

Follow

Mar 3

Updating "denormalized" aggregates with "duplicates": MongoDB vs. PostgreSQL

#mongodb #postgres #oracle #denormalization

2 reactions

Add Comment

9 min read

Emulations

A single document per aggregate, with embedded one-to-many relationships, is efficient in MongoDB, not in emulations. For example, in Oracle Autonomous Database, where the document is stored as OSON, the update will rewrite the full document:

ora> db.empdept.updateOne(
  { _id: 10, "employees.empno": 7782 },
  { $set: { "employees.$.sal": 2600 } }
)

{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}

ora> db.aggregate( [ { $sql : " select * from dbms_xplan.display_cursor( format=>'BASIC') " } ] ).forEach(row => print(row.PLAN_TABLE_OUTPUT));

EXPLAINED SQL STATEMENT:
------------------------
update "ORA"."empdept" set "DATA" = :1 where ("RESID" = :2 )  returning "RESID", "ETAG" into :3 , :4

Plan hash value: 893016358

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | UPDATE STATEMENT             |              |
|   1 |  UPDATE                      | empdept      |
|   2 |   TABLE ACCESS BY INDEX ROWID| empdept      |
|   3 |    INDEX UNIQUE SCAN         | SYS_C0031043 |
-----------------------------------------------------

You have also the possibility to store the collection with JSON-relational duality views (JDV), but this partial update is not supported:

arrayFilters option is not supported on duality view collections

However, even if the JDV doesn't allow partial updates, the aggregate is stored as individual in SQL tables, and users can switch to SQL statements for partial updates.

Duality Views

In relational databases, applications typically work with a logical view of the data model, while the storage layer may transparently cluster or scatter data into fixed-size blocks. Oracle Database (and MySQL) added JSON-Relational Duality Views (JDV) to present a single logical view for JSON documents, while splitting them to multiple SQL tables. As each SQL tables have their own physical segment(s), aggregates are stored across multiple physical blocks.

If you are a fan of the single‑table or single‑collection idea, you may create a single view. However, it has no advantage in data locality, as you explicitly normalized to multiple tables:

CREATE TABLE dept (
  deptno NUMBER CONSTRAINT dept_pk PRIMARY KEY,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);

CREATE TABLE emp (
  empno  NUMBER CONSTRAINT emp_pk PRIMARY KEY,
  ename  VARCHAR2(10),
  job    VARCHAR2(9),
  sal    NUMBER(7,2),
  deptno NUMBER NOT NULL,
  CONSTRAINT emp_dept_fk FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

CREATE INDEX emp_dept_fk ON emp (deptno);

INSERT INTO dept (deptno, dname, loc)  VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO emp (empno, ename, job, sal, deptno)  VALUES (7782, 'CLARK', 'MANAGER', 2450, 10);
INSERT INTO emp (empno, ename, job, sal, deptno)  VALUES (7839, 'KING', 'PRESIDENT', 5000, 10);
-- add many departments to check the number of blocks scanned
INSERT INTO dept(deptno) SELECT rownum+1000
 FROM xmltable('0 to 999')
;

-- one view over multiple tables with JDV

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW empdept_dv AS
SELECT JSON {
  '_id'  : d.deptno,
  'dname': d.dname,
  'loc'  : d.loc,
  'employees' : [
    SELECT JSON {
      'empno': e.empno,
      'ename': e.ename,
      'job'  : e.job,
      'sal'  : e.sal
    }
    FROM emp e WITH INSERT UPDATE DELETE
    WHERE e.deptno = d.deptno
  ]
}
FROM dept d WITH INSERT UPDATE DELETE
;

The view looks like a document collection with an embedded one-to-many relationship, similar to MongoDB’s recommended model, but it’s not truly embedded—the view actually splits it into two tables. We have lost the main advantage of MongoDB: data that's accessed together should be stored together.

I can query it as if it were a single table:

SELECT data 
FROM empdept_dv
WHERE
JSON_EXISTS("DATA",'$?(@._id.numberOnly() == $B0)' passing 10 as "B0"
type(strict))
;

but it actually reads two tables:

SQL> select * from dbms_xplan.display_cursor(format=>'ALLSTATS LAST -COST');

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________________
SQL_ID  gfr8jkdwatdnz, child number 0
-------------------------------------
SELECT data  FROM empdept_dv WHERE JSON_EXISTS("DATA",'$?(@._id.numberOnly() == $B0)' passing 10 as "B0" type(strict))

Plan hash value: 2755083285

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |      1 |       3 |       |       |          |
|   1 |  SORT GROUP BY                       |             |      1 |      1 |      1 |       2 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP         |      1 |      1 |      2 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | EMP_DEPT_FK |      1 |      1 |      2 |       1 |       |       |          |
|   4 |  TABLE ACCESS BY INDEX ROWID         | DEPT        |      1 |      1 |      1 |       3 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN                  | DEPT_PK     |      1 |      1 |      1 |       2 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPTNO"=:B1)
   5 - access("D"."DEPTNO"=10)

The access is efficient, using the index on the foreign key, but it doesn't colocate a one-to-many relationship as we expect when storing an aggregate into a single document, or when using the single-table design.

You can update through the view, but the following will update a lot more than what you expect, as it rewrites the full document:

UPDATE empdept_dv
SET data = JSON_TRANSFORM(
  data,
  SET '$.employees[0].sal' = 2600
)
WHERE
JSON_EXISTS("DATA",'$?(@._id.numberOnly() == $B0)' passing 10 as "B0"
type(strict))
;

Using duality views instead of OSON storage allows you to fall back to SQL to update a single row, similar to a single-table design. However, unlike DynamoDB, you cannot group different item types in the same table. These are SQL tables with a fixed schema and no polymorphism, so you cannot store both department and employee attributes in a single table. JSON-Relational Duality Views don’t replace document databases: they act like an object-relational mapper (ORM) with all logic deployed in the SQL database.

Cluster

To get closer to the performance of a document database, I need a single-table duality view capable of storing documents that are split across multiple SQL tables, yet behave as if they were in a single physical table. In Oracle Database, this corresponds to a CLUSTER, where only the key is declared, like DynamoDB tables, and an estimated value size are predefined to fill the fixed-size blocks:

CREATE CLUSTER dept_cluster (
  deptno NUMBER ------------------------------------- clustering key
) 
SIZE 1024 --  expected bytes per cluster key
;

CREATE INDEX dept_cluster_idx ON CLUSTER dept_cluster 
;

The logical SQL tables are stored in the cluster under a common clustering key, instead of allocating their own physical segment:

CREATE TABLE dept ( 
  deptno NUMBER CONSTRAINT dept_pk PRIMARY KEY,   --- clustering key
  dname  VARCHAR2(14), 
  loc    VARCHAR2(13)  
 )
 CLUSTER dept_cluster (deptno) ---------------------- clustering key
; 

CREATE TABLE emp (
  empno  NUMBER CONSTRAINT emp_pk PRIMARY KEY, 
  ename  VARCHAR2(10),  
  job    VARCHAR2(9), 
  sal    NUMBER(7,2), 
  deptno NUMBER NOT NULL,      ---------------------- clustering key
  CONSTRAINT emp_dept_fk 
             FOREIGN KEY (deptno) 
             REFERENCES dept(deptno)
 )
 CLUSTER dept_cluster (deptno) ---------------------- clustering key
;

INSERT INTO dept (deptno, dname, loc)  VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO emp (empno, ename, job, sal, deptno)  VALUES (7782, 'CLARK', 'MANAGER', 2450, 10);
INSERT INTO emp (empno, ename, job, sal, deptno)  VALUES (7839, 'KING', 'PRESIDENT', 5000, 10);
-- add many departments to check the number of blocks scanned
INSERT INTO dept(deptno) SELECT rownum+1000
 FROM xmltable('0 to 999')
;

I created the same duality view as before, as the logical tables are the same, and query it:

SELECT data 
FROM empdept_dv
WHERE
JSON_EXISTS("DATA",'$?(@._id.numberOnly() == $B0)' passing 10 as "B0"
type(strict))
;

Physically, it accesses the logical tables in the pre-joined cluster, via its index:

SQL> select * from dbms_xplan.display_cursor(format=>'ALLSTATS LAST');

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  26py2vsmch5kx, child number 0
-------------------------------------
SELECT data FROM empdept_dv WHERE JSON_EXISTS("DATA",'$?(@._id.numberOnl
y() == $B0)' passing 10 as "B0" type(strict))

Plan hash value: 176041294

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | A-Rows | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |      1 |      1 |       3 |      2 |       |         |          |
|   1 |  SORT GROUP BY              |                  |      1 |      1 |       3 |      2 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS CLUSTER      | EMP              |      1 |      2 |       3 |      2 |       |         |          |
|*  3 |    INDEX UNIQUE SCAN        | DEPT_CLUSTER_IDX |      1 |      1 |       2 |      2 |       |         |          |
|   4 |  TABLE ACCESS BY INDEX ROWID| DEPT             |      1 |      1 |       3 |      2 |       |         |          |
|*  5 |   INDEX UNIQUE SCAN         | DEPT_PK          |      1 |      1 |       2 |      2 |       |         |          |
------------------------------------------------------------------------------------------------------------------------

The execution plan shows access to the cluster using its index on DEPTNO. However, the plan is unclear and appears to use both the DEPT index and the cluster index. I displayed the number of buffer gets, but the math doesn’t add up: a total of three buffers seems reasonable—two index blocks (root and leaf) and one cluster block—yet the detailed output shows three for each index, which should mean six in total.

I confirmed that all my data is in a single block—my goal—by checking the ROWID, which contains the block identifier:

select  
  rowid,  
  dbms_rowid.rowid_relative_fno(rowid)  as file_no,  
  dbms_rowid.rowid_block_number(rowid)  as block_no,
to_char(dbms_rowid.rowid_block_number(rowid),'xxxxxxxx') as block_hex,
dept.* from dept where deptno = 10
;

ROWID                 FILE_NO   BLOCK_NO BLOCK_HEX     DEPTNO DNAME          LOC
------------------ ---------- ---------- --------- ---------- -------------- -------------
AAASKBAAAAAAK5hAAA          0      44641      ae61         10 ACCOUNTING     NEW YORK

select  
  rowid,  
  dbms_rowid.rowid_relative_fno(rowid)  as file_no,  
  dbms_rowid.rowid_block_number(rowid)  as block_no,
to_char(dbms_rowid.rowid_block_number(rowid),'xxxxxxxx') as block_hex,
emp.* from emp where deptno = 10
;

ROWID                 FILE_NO   BLOCK_NO BLOCK_HEX      EMPNO ENAME      JOB              SAL     DEPTNO
------------------ ---------- ---------- --------- ---------- ---------- --------- ---------- ----------
AAASKBAAAAAAK5hAAA          0      44641      ae61       7782 CLARK      MANAGER         2450         10
AAASKBAAAAAAK5hAAB          0      44641      ae61       7839 KING       PRESIDENT       5000         10

They are all in block ae61. To try to understand better, I traced the consistent reads (with event 10200):

ktrgtc2(): started for block <0x0000 : 0x0000ae71> objd: 0x00012288, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae71> objd: 0x00012288 ret:0x0 flg:0x7fff
ktrgtc2(): started for block <0x0000 : 0x0000ae72> objd: 0x00012288, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae72> objd: 0x00012288 ret:0x0 flg:0x7fff
ktrgtc2(): started for block <0x0000 : 0x0000ae61> objd: 0x00012281, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae61> objd: 0x00012281 ret:0x0 flg:0x2f04d
ktrgtc2(): started for block <0x0000 : 0x0000ae69> objd: 0x00012282, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae69> objd: 0x00012282 ret:0x0 flg:0x90040
ktrgtc2(): started for block <0x0000 : 0x0000ae6a> objd: 0x00012282, DirtyRead: 0:0 env [0x7ffff8925fd8]: (scn: 0x00000000004cdc0e  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000000000000000  hi-scn: 0x00000000004cdc0e  ma-scn: 0x00000000004cdbea  flg: 0x00000662)ktrgtc2(): completed for block <0x0000 : 0x0000ae6a> objd: 0x00012282 ret:0x0 flg:0x90040

I checked from DBA_OBJECTS and DBA_EXTENTS:

• blocks 0xae71 and 0xae72 are DEPT_PK (objd:0x12282)
• block ae61 is DEPT_CLUSTER (objd:0x12281), the cluster's block for DEPT=10
• blocks ae69 and ae6a are DEPT_CLUSTER_IDX (objd:0x12282)

Note that I've run the query with the same predicate that is generated from the MongoDB emulation, and you can see the full SQL query with:

variable c clob;
set autoprint on long 10000 pagesize 1000
exec DBMS_UTILITY.EXPAND_SQL_TEXT( q'[
SELECT data FROM empdept_dv WHERE JSON_VALUE(data, '$._id') = 10
]', :c)

I got the same execution plan with the simplified syntax:

SELECT data 
 FROM empdept_dv d 
 WHERE d.data."_id"=10
;

With the simplest join syntax, on SQL tables, I got a similar plan reading 6 blocks:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  cys0qt7ttbkzd, child number 0
-------------------------------------
select * from dept , emp where emp.deptno=dept.deptno and dept.deptno=10 and emp.deptno=10

Plan hash value: 3732773526

----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |      2 |       6 |
|   1 |  NESTED LOOPS                |         |      1 |      2 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |       3 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |      1 |      1 |       2 |
|*  4 |   TABLE ACCESS CLUSTER       | EMP     |      1 |      2 |       3 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT"."DEPTNO"=10)
   4 - filter(("EMP"."DEPTNO"=10 AND "EMP"."DEPTNO"="DEPT"."DEPTNO"))

This NESTED LOOP with TABLE ACCESS CLUSTER is the typical join on pre-joined rows in a cluster. One index entry gets all employee rows together.

This is likely the closest we can get to a true "single-table" design for JSON-relational duality views. An Oracle indexed cluster stores related rows in a single block, but the optimizer, as shown in execution plans, still does not share the cluster index across the two tables and fully exploit the pre-joined one-to-many relationship.

We’ve come full circle. Using features from 1980 (CLUSTER) and 2020 (JDV), we have achieved a single‑duality that preserves physical colocation like a document database — while still declaring multiple SQL tables underneath, with proud respect for normal forms.

---

Conclusion

This is mainly interesting for fun (note the publication date) and for how it challenges our mental models. Don't apply this design to your application. Adding layers instead of reducing complexity isn’t why you choose a document model.

Data locality has always driven performance because, despite advances in hardware and software, the laws of physics still apply. In the 1980s, when relational databases abstracted away the physical model, Oracle adopted this abstraction with SQL tables but also introduced physical clustering to preserve data colocation and avoid benchmark regressions. The idea that data accessed together should be stored together stayed relevant in the 2000s, as databases scaled horizontally and document databases like MongoDB emerged from this principle. It remains true today: you get the best performance by storing an application's object aggregates in a single block on disk. MongoDB adopted WiredTiger as its storage engine because it offers a flexible block-size B-tree that stores collection documents without fragmentation.

You should avoid using the clustering technique described here for Oracle tables, as it is not very flexible when data updates occur on fixed-size block storage. The CLUSTER clause in CREATE TABLE is also ignored by the Autonomous Database managed service. Interestingly, you may be surprised to realize that indexed clusters are actually used daily in Oracle databases to store dictionary tables. Oracle's implementation of the system dictionary treats these tables as logical relational tables, but physically denormalizes them. If you're skeptical, ask your DBA to run:

SELECT COUNT(*)
FROM dba_objects
WHERE object_type = 'CLUSTER';

You likely have at least nine of them—ten if you created one for EMP/DEPT. If you EXPLAIN PLAN FOR this query on dba_objects, you’ll see what today would be stored as a document: a polymorphic aggregate to describe the various database object attributes in the database catalog.

I’ve written this for educational purposes only, and published it on a specific date, where any article can be a joke even when fact-based. When you read recommendations about design patterns, database choices, or data modeling rules, always cross‑check with how the system really works — and whether data accessed together is actually stored together, or presented that way by multiple internal abstraction layers and transformations. In case of doubts, look at the execution plan.