Optimizing Once, Accelerating Everywhere: SQL and MongoDB on a Converged Data Platform

Modernization initiatives often begin with a simple objective: migrate existing workloads to a new platform with minimal disruption. While successful migration is an important milestone, it is rarely where organizations realize the greatest value. The real opportunity begins after migration, when optimization efforts improve performance, simplify operations, and establish a foundation for innovation.

In traditional architectures, optimization efforts are often isolated within individual technologies. A database administrator tunes SQL indexes. A separate development team optimizes MongoDB collections. Analytics teams build ETL pipelines into a warehouse. AI initiatives create yet another copy of the data for vector processing. Each optimization benefits only a single workload while increasing operational complexity.

A converged database platform changes this equation.

Instead of maintaining multiple specialized databases synchronized through replication and ETL pipelines, Oracle AI Database stores a single authoritative copy of the data while exposing multiple APIs that allow applications to continue using familiar programming models.

This article demonstrates a simple but powerful example: creating one index that simultaneously accelerates both SQL applications and MongoDB applications without duplicating data or maintaining separate database platforms.

The Hidden Cost of Polyglot Persistence

Over the past decade many organizations adopted specialized databases to support different application patterns.

A typical architecture often looks like this:

              Business Applications

      SQL Apps        MongoDB Apps       Analytics
           |                |               |
           ▼                ▼               ▼
    Relational DB     MongoDB Cluster   Data Warehouse
           │                │               │
           └────── CDC / ETL / Sync ────────┘

While this approach enables specialized capabilities, it introduces significant operational challenges:

Multiple database platforms to manage
Duplicate storage
Continuous synchronization
Operational drift
Additional licensing and infrastructure
Increased security and governance complexity

Every additional copy of the data becomes another system that must be monitored, secured, patched, backed up and kept synchronized.

A Converged Alternative

Oracle AI Database approaches the problem differently.

Instead of duplicating data across multiple engines, it exposes the same data through multiple APIs.

              Oracle AI Database

             JSON Collection Table
           +-----------------------+
           |      employees        |
           +-----------+-----------+
                       |
         Function-Based B-tree Index
                on $.empID
                       |
      +----------------+----------------+
      |                                 |
      ▼                                 ▼
 SQL Applications                 MongoDB Applications
 SQLcl / SQL Developer             mongosh / Compass

Both application models share:

  • the same data
  • the same optimizer
  • the same transaction engine
  • the same security model
  • the same backup strategy
  • the same indexes

Optimization performed once immediately benefits every application accessing the data.

Demonstration Overview

The accompanying demonstration creates an Oracle JSON Collection Table containing 100,000 employee documents.

Each document resembles the following:

{
  "_id" : 50000,
  "empID" : 50000,
  "fname" : "FirstName50000",
  "lname" : "LastName50000",
  "salary" : 97500.25,
  "commission" : 4200.50
}

The script then creates a single function-based B-tree index on the JSON empID field:

CREATE INDEX employees_empid_idx
ON employees (
    JSON_VALUE(
        data,
        '$.empID'
        RETURNING NUMBER
        ERROR ON ERROR
    )
);

Although this appears to be an ordinary SQL optimization, the impact extends well beyond SQL.

Accessing the Same Data Using SQL

A traditional SQL application can immediately use the index but let’s set up a relational view to represent the data in a form that our SQL users expect.

CREATE OR REPLACE VIEW employees_v AS
SELECT
    e.data.empID.number() as empID,
    e.data.fname.string() as fname,
    e.data.lname.string() as lname,
    e.data.salary.number() as salary,
    e.data.commission.number() as commission,
    e.data as rawjson
FROM employees e;

The number() and string() are data-type conversion methods that ensure the returning data is typed as regular scalar SQL types.

Let’s query the JSON data with SQL simple dot-notation leveraging the index.

select * from employees e where e.data.empID = 50000;

Now let’s utilize the view for convenience.

select * from employees_v where empID = 50000;

Oracle recognizes that the predicate exactly matches the indexed expression and performs an INDEX RANGE SCAN thereby avoiding a full table scan.

For database administrators, this represents a familiar optimization pattern.

For the business, it means lower CPU utilization, reduced latency, and improved scalability.

Accessing the Same Data Using MongoDB

Now connect using the Oracle Database API for MongoDB and issue the functionally equivalent query.

db.employees.findOne({
    empID: 50000
});

The MongoDB application is accessing exactly the same underlying data.

No synchronization.

No ETL.

No duplicated collection.

No second database.

The optimizer leverages the very same index created for SQL.

From the application’s perspective, nothing has changed. Developers continue using familiar MongoDB tools such as mongosh, MongoDB Compass, or Studio 3T while Oracle AI Database provides enterprise-grade transactional consistency, security and performance underneath.

Demonstrating True Convergence

The most compelling aspect is not that SQL and MongoDB both work.

It is that they work against the same physical data.

For example, inserting a document through the Oracle Database API for MongoDB:

db.employees.insertOne({
    _id: 100001,
    empID: 100001,
    fname: "Matt",
    lname: "DeMarco",
    salary: 50000
});

Immediately becomes visible to SQL:

SELECT *
FROM employees_v
WHERE empID = 100001;

Likewise, updating the document through SQL:

UPDATE employees
SET data.salary = 100000
WHERE data.empID.number() = 100001;
COMMIT;

Is immediately reflected when queried from mongosh:

db.employees.findOne({
    empID: 100001
});

No synchronization process is required because there is only one copy of the data.

Optimization Still Matters

The demonstration also illustrates an important optimization principle.

This query is SARGable:

SELECT
       data
FROM 
    employees
WHERE 
    JSON_VALUE(data, '$.empID' RETURNING NUMBER ERROR ON ERROR) = 100001;

Oracle can satisfy the predicate using the function-based index.

By contrast:

SELECT
       data
FROM 
    employees
WHERE 
    TO_CHAR(
          JSON_VALUE(data, '$.empID' RETURNING NUMBER ERROR ON ERROR)
      ) = '100001';

The TO_CHAR function transforms the indexed value from a number to a string and prevents Oracle from using the existing index efficiently, resulting in a full table scan.

The same concept applies to Oracle Database API for MongoDB with a MongoDB query.

A simple predicate:

db.employees.find({
    empID: 100001
});

can leverage the index.

A MongoDB expression using $expr and $toString may prevent efficient index utilization because the indexed value is transformed before comparison.

The optimization principle is identical across both APIs because they ultimately share the same optimizer.

Optimization Enables Innovation

This is where modernization becomes far more interesting.

Once workloads reside on a converged platform, organizations are no longer optimizing isolated databases, rather they are optimizing a shared foundation that enables entirely new capabilities.

The same employee documents can immediately participate in:

  • SQL analytics
  • Oracle Database API for MongoDB
  • JSON-Relational Duality Views
  • AI Vector Search
  • Property Graph analysis
  • Spatial operations
  • Full Text Search
  • Oracle Machine Learning

Each capability operates against the same transactional data without requiring additional synchronization pipelines or duplicate storage.

Optimization is no longer confined to improving query performance. It becomes an investment that accelerates every application and every innovation built on the platform.

Final Thoughts

Database modernization should not end with migration.

Migration moves workloads.

Optimization improves efficiency.

Innovation creates competitive advantage.

The demonstration presented here illustrates a broader architectural principle than simply creating a fast query. By building a single index on a JSON document, Oracle AI Database simultaneously accelerates SQL applications and MongoDB applications while maintaining a single authoritative copy of the data.

That is the promise of a converged database platform: optimize once, accelerate everywhere.

As organizations continue investing in AI, analytics and modern application development, architectures that eliminate unnecessary data duplication while exposing multiple programming models will be increasingly well positioned to reduce operational complexity, lower total cost of ownership and accelerate innovation without introducing yet another specialized database into the enterprise.

Appendix

Demonstration

Oracle JSON and Converged Data Access

Shared SQL and MongoDB Indexing

SQL/JSON Relational Projection and Type Methods

Execution-Plan Verification

Converged Database Innovation Capabilities

Leave a Reply

Discover more from OraMatt: YABAOracle

Subscribe now to keep reading and get access to the full archive.

Continue reading