Beyond Document Storage: Optimizing JSON Collections with Virtual Columns, Indexes, and Automatic List Partitioning

Document databases are often selected because they make it easy to store and retrieve flexible JSON documents. That flexibility is valuable but storing documents is only one part of operating a production data platform.

As a collection grows, teams also need to answer broader questions:

How can analysts access important document attributes through SQL?
How can frequently used predicates avoid repeatedly evaluating JSON expressions?
How can administrators isolate, move, or remove segments of a large collection?
How can document-oriented applications and relational workloads operate against the same data without building additional copies and synchronization pipelines?

Oracle AI Database addresses these requirements by allowing a JSON collection to participate in the same SQL, indexing, partitioning, and operational-management capabilities available to relational tables.

This post demonstrates an incremental optimization strategy for an Oracle JSON collection table:

Create the JSON collection
Project frequently used JSON attributes as virtual columns
Index the virtual columns
Partition the collection by a derived JSON attribute using automatic list partitioning

The result remains a document collection for application developers, while also becoming a SQL-accessible and operationally manageable database object.

The Optimization Pattern

			
Document Applications
        |
        | JSON documents
        v
+-------------------------------+
| Oracle JSON Collection Table  |
|                               |
| DATA          JSON            |
| name_vc      virtual column   |
| email_vc     virtual column   |
| email_domain_vc virt column   |
+-------------------------------+
        |              |
        |              +--> Automatic LIST partitions
        |                   by email_domain_vc
        |
        +--> B-tree indexes on projected attributes
        |
        +--> SQL analytics and operational queries

		

This pattern does not replace the JSON document model. It augments it.

Applications can continue to work with complete JSON documents through SQL, SODA, REST, or Oracle AI Database API for MongoDB. At the same time, database administrators and analysts gain a stable SQL projection over selected document attributes.

Step 0: Create the JSON Collection Table

The initial table can be created with a single statement:

CREATE JSON COLLECTION TABLE matt.registrations;

A JSON collection table exposes a native JSON column named DATA. Each row represents a document, and Oracle manages the document identifier used by the collection.

At this stage, an application can store and retrieve JSON documents but SQL queries that frequently filter on document attributes must repeatedly evaluate JSON path expressions.

For example:

			
SELECT data
FROM matt.registrations
WHERE JSON_VALUE(
        data,
        '$.email' RETURNING VARCHAR2(320 CHAR)
      ) = 'ada@oracle.com';

		

The same lookup can also use Oracle’s simplified JSON dot notation:

			
SELECT data
FROM matt.registrations r
WHERE r.data.email.string() = 'ada@oracle.com';

Both approaches work well for direct JSON access. The optimization opportunity begins when the same document attributes become recurring operational or analytical access paths. Repeating JSON expressions across application queries, reports, indexes, statistics, and operational scripts can create unnecessary complexity.

That progression is important:

			
Explicit SQL/JSON
JSON_VALUE(data, '$.email' ...)
        |
        v
Simplified JSON SQL
r.data.email.string()
        |
        v
Governed relational projection
email_vc
        |
        v
Indexing / Statistics / Partitioning

		

Virtual columns therefore do not replace JSON access. They provide a stable relational projection when selected document attributes become important enough to optimize and manage explicitly.

Step 1: Project JSON Attributes as Virtual Columns

Frequently accessed scalar attributes can be exposed as virtual columns.

			
ALTER TABLE matt.registrations
ADD
(
  name_vc VARCHAR2(200 CHAR)
    GENERATED ALWAYS AS
    (
      JSON_VALUE(
        data,
        '$.name'
        RETURNING VARCHAR2(200 CHAR)
      )
    ),
  email_vc VARCHAR2(320 CHAR)
    GENERATED ALWAYS AS
    (
      JSON_VALUE(
        data,
        '$.email'
        RETURNING VARCHAR2(320 CHAR)
      )
    ),
  email_domain_vc VARCHAR2(255 CHAR)
    GENERATED ALWAYS AS
    (
      CAST(
        LOWER(
          SUBSTR(
            JSON_VALUE(
              data,
              '$.email'
              RETURNING VARCHAR2(320 CHAR)
            ),
            INSTR(
              JSON_VALUE(
                data,
                '$.email'
                RETURNING VARCHAR2(320 CHAR)
              ),
              '@'
            ) + 1,
            255
          )
        )
        AS VARCHAR2(255 CHAR)
      )
    )
);

		

The virtual columns do not create a second stored copy of each attribute. Oracle derives their values from the DATA document when the columns are accessed.

Why explicitly bound the virtual columns?

The size declarations are intentional.

The JSON document itself remains flexible but attributes promoted into the relational model should have predictable SQL datatypes. Using very broad expressions such as VARCHAR2(4096) can create avoidable datatype-width problems in multibyte database character sets because Oracle must account for the maximum byte width of the expression.

The example establishes an explicit contract:

			
JSON document
     |
     | flexible document attribute
     v
DATA.email
     |
     | promote for repeated SQL use
     v
email_vc VARCHAR2(320 CHAR)
     |
     | derive a bounded operational key
     v
email_domain_vc VARCHAR2(255 CHAR)
     |
     +--> Indexing
     +--> Optimizer statistics
     +--> Partitioning

		

Three details are important:

VARCHAR2(... CHAR) makes the intended character semantics explicit
SUBSTR(..., 255) places an upper bound on the derived domain expression
CAST(... AS VARCHAR2(255 CHAR)) gives the derived virtual column a clear final datatype

This is more than a workaround for a datatype error. It reflects a useful design principle: the JSON document provides schema flexibility, while a promoted virtual column provides a deliberately bounded relational contract for an attribute that is important to query optimization or operational management.

For this demonstration, the example assumes that name and email are scalar strings that fit within the selected bounds. In a production design, the application or database contract should also define how oversized, missing, or malformed values are handled.

The collection can now be queried through familiar SQL:

			
SELECT
  name_vc,
  email_vc,
  email_domain_vc
FROM matt.registrations
WHERE email_domain_vc = 'oracle.com';

		

Oracle JSON collection expression columns are maintained as invisible columns. They do not appear in an unqualified SELECT * but they can be referenced explicitly in SQL statements, indexes, constraints, statistics, and partition definitions.

Why repeat the email expression?

The email_domain_vc expression repeats the JSON_VALUE operation instead of referring to email_vc.

That is intentional. An Oracle virtual column expression cannot reference another virtual column. Both columns therefore derive their values directly from the underlying DATA column. The email_domain_vc expression additionally bounds the derived value to 255 characters and normalizes it with LOWER() so the same expression can later serve as a predictable partitioning key.

Why virtual columns matter

Virtual columns create a governed relational access layer over selected JSON attributes:

SQL authors use stable column names instead of repeating JSON path expressions
Index definitions become easier to read and maintain
Optimizer statistics can be gathered on important projected attributes
Partitioning can be based on values derived from inside the document
Existing document applications can continue to use the original JSON representation

The collection remains flexible but high-value attributes receive an explicit operational contract.

Step 2: Index the Virtual Columns

Once an attribute has been projected, the index should reference the virtual column directly:

			
CREATE INDEX matt.idx_registrations_email_domain_vc
ON matt.registrations (email_domain_vc);

This is clearer than repeating the complete SUBSTR, INSTR, and JSON_VALUE expression in the index definition.

A point lookup can then use the projected column:

			
SELECT
  name_vc,
  email_vc
FROM matt.registrations
WHERE email_domain_vc = 'oracle.com';

		

For exact email lookups, a second index can be added:

			
CREATE INDEX matt.idx_registrations_email
ON matt.registrations (email_vc);

The optimizer can use these conventional B-tree access paths while the source of each value remains inside the JSON document.

Gather optimizer statistics

After loading a representative volume of data, gather table and column statistics:

			
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'MATT',
    tabname    => 'REGISTRATIONS',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

		

The index provides the access path. Statistics help the optimizer determine when that access path is preferable to a scan.

Step 3: Recreate the Collection with Automatic List Partitioning

For a mature deployment, the collection can be created in its final partitioned form.

The following definition derives email_domain_vc from each document and uses it as an automatic list-partitioning key:

			
CREATE JSON COLLECTION TABLE matt.registrations WITH ETAG
(
  name_vc VARCHAR2(200 CHAR)
    GENERATED ALWAYS AS
    (
      JSON_VALUE(
        data,
        '$.name'
        RETURNING VARCHAR2(200 CHAR)
      )
    ),
  email_vc VARCHAR2(320 CHAR)
    GENERATED ALWAYS AS
    (
      JSON_VALUE(
        data,
        '$.email'
        RETURNING VARCHAR2(320 CHAR)
      )
    ),
  email_domain_vc VARCHAR2(255 CHAR)
    GENERATED ALWAYS AS
    (
      CAST(
        LOWER(
          SUBSTR(
            JSON_VALUE(
              data,
              '$.email'
              RETURNING VARCHAR2(320 CHAR)
            ),
            INSTR(
              JSON_VALUE(
                data,
                '$.email'
                RETURNING VARCHAR2(320 CHAR)
              ),
              '@'
            ) + 1,
            255
          )
        )
        AS VARCHAR2(255 CHAR)
      )
    )
)
PARTITION BY LIST (email_domain_vc) AUTOMATIC
(
  PARTITION p_oracle
    VALUES ('oracle.com')
)
ENABLE ROW MOVEMENT;

		

This definition combines several capabilities:

WITH ETAG adds document version metadata that can support optimistic concurrency.
The virtual columns expose selected document fields to SQL using explicit character-length contracts.
email_domain_vc is normalized, bounded, and explicitly cast before being used as the partitioning key.
PARTITION BY LIST ... AUTOMATIC creates new partitions as previously unseen domain values arrive.
ENABLE ROW MOVEMENT allows an update that changes the partitioning value to move the row to the correct partition.

An automatic list-partitioned table must begin with at least one explicitly defined partition. It cannot use a DEFAULT partition because Oracle must be able to create a new partition when it encounters a new partition-key value.

Load Sample Documents

The following inserts create documents for several email domains:

			
INSERT INTO matt.registrations
VALUES
(
  '{
     "_id"   : 1001,
     "name"  : "Ada Lovelace",
     "email" : "ada@oracle.com"
   }'
);
INSERT INTO matt.registrations
VALUES
(
  '{
     "_id"   : 1002,
     "name"  : "Grace Hopper",
     "email" : "grace@example.com"
   }'
);
INSERT INTO matt.registrations
VALUES
(
  '{
     "_id"   : 1003,
     "name"  : "Margaret Hamilton",
     "email" : "margaret@engineering.example"
   }'
);
COMMIT;

		

The first document is stored in P_ORACLE. The other domain values cause Oracle to create automatic list partitions on demand.

The resulting partitions can be inspected through the data dictionary:

			
SELECT
  partition_position,
  partition_name,
  high_value
FROM user_tab_partitions
WHERE table_name = 'REGISTRATIONS'
ORDER BY partition_position;

		

Automatically created partitions receive system-generated names. Administrators can rename important partitions later when operationally meaningful names are required.

Verify Partition Pruning

A query that filters on the partitioning key can eliminate unrelated partitions:

			
EXPLAIN PLAN FOR
SELECT
  name_vc,
  email_vc
FROM matt.registrations
WHERE email_domain_vc = 'oracle.com';

		

Display the execution plan with partition details:

			
SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY(
    NULL,
    NULL,
    'BASIC +PARTITION +PREDICATE'
  )
);

		

The plan should show that Oracle accesses only the partition associated with oracle.com.

Partition pruning and indexing solve different problems:

Partition pruning reduces the number of physical segments considered by the query.
An index reduces the number of rows examined inside the selected segment or partitions.

For a query that supplies both domain and full email address, the two techniques can work together:

			
SELECT
  name_vc,
  email_vc
FROM matt.registrations
WHERE email_domain_vc = 'oracle.com'
  AND email_vc = 'ada@oracle.com';

		

Reconsider the Index Strategy After Partitioning

An index on email_domain_vc is useful while the table is nonpartitioned. After the table is partitioned by email_domain_vc, that same index can become less valuable because partition pruning already isolates the domain.

A more useful partition-aligned index may target the value used for point lookups inside each domain:

			
CREATE INDEX matt.idx_registrations_email_local
ON matt.registrations (email_vc)
LOCAL;

A local index is partitioned in alignment with the table. This alignment can simplify partition maintenance because table and index partitions can be managed together.

The appropriate design depends on the workload:

Query pattern	Primary optimization
All registrations for one domain	Partition pruning
One exact email within a domain	Partition pruning plus local email index
Search by name within a domain	Partition pruning plus local name index
Cross-domain aggregation	Partition-wise or parallel scanning, depending on the query
Document retrieval by `_id`	Collection-managed document identifier access

This is why indexing and partitioning should be treated as complementary design decisions rather than interchangeable features.

Operational Benefits Beyond Query Performance

Partitioning is not only a query optimization feature. It is also a data-management boundary.

Once the collection is partitioned, administrators can perform operations against a subset of the collection rather than the entire object.

Examples include:

			
-- Count documents in a known partition.
SELECT COUNT(*)
FROM matt.registrations PARTITION (p_oracle);

			
-- Rebuild or relocate one partition without treating the
-- collection as a single monolithic segment.
ALTER TABLE matt.registrations
MOVE PARTITION p_oracle
ONLINE
UPDATE INDEXES;

		

			
-- Remove all documents from a partition when the business
-- lifecycle permits that operation.
ALTER TABLE matt.registrations
TRUNCATE PARTITION p_oracle
UPDATE INDEXES;

		

Depending on the business requirement, partitions can also support differentiated tablespace placement, backup strategy, lifecycle processing, and data-retention operations.

A large JSON collection therefore becomes a set of manageable physical units rather than one continuously growing segment.

Important Design Guardrails

1. Use a bounded partitioning key

Automatic list partitioning creates a partition for each distinct key value.

That is useful when the values represent a controlled set such as:

tenant identifier
region
business unit
lifecycle state
application identifier
a governed set of email domains

It is less suitable when the partition key has unbounded or highly volatile cardinality.

A public registration system could receive thousands of unique email domains. Creating one partition per domain may produce more partitions than the operational model can reasonably manage.

The technical capability should therefore be matched to the expected data distribution.

2. Define behavior for missing or malformed attributes

If email is absent, not a scalar string, exceeds the selected return size, or does not contain the structure expected by the domain-extraction expression, the resulting virtual-column value may not represent a useful partitioning key. JSON_VALUE returns NULL on error by default unless a different error clause is specified.

Production designs should deliberately choose one of these approaches:

reject documents that do not contain a valid email
allow a NULL or quarantine partition
derive a normalized fallback category
partition on a more reliable business attribute

3. Bound promoted attributes deliberately

Avoid treating a promoted virtual column as an unbounded copy of the JSON field.

The document layer can remain flexible but fields selected for B-tree indexes, optimizer statistics, or partition keys should use lengths appropriate for their operational meaning. Explicit character semantics are especially useful in multibyte database character sets because they make the intended number of characters clear.

For this example:

name_vc is bounded at 200 characters.
email_vc is bounded at 320 characters.
email_domain_vc is bounded at 255 characters.
The domain extraction itself is limited to 255 characters and explicitly cast to the same datatype as the virtual column.

This alignment keeps the JSON projection, derived expression, indexable column, and partition key consistent.

4. Normalize the partitioning value

Values such as Oracle.com, ORACLE.COM, and oracle.com are distinct strings unless the expression normalizes them.

The demonstration normalizes the domain with LOWER() and explicitly bounds the result:

			
CAST(
  LOWER(
    SUBSTR(
      JSON_VALUE(
        data,
        '$.email'
        RETURNING VARCHAR2(320 CHAR)
      ),
      INSTR(
        JSON_VALUE(
          data,
          '$.email'
          RETURNING VARCHAR2(320 CHAR)
        ),
        '@'
      ) + 1,
      255
    )
  )
  AS VARCHAR2(255 CHAR)
)

		

Normalization prevents logically identical values from creating separate partitions, while the explicit SUBSTR length and CAST keep the partitioning expression within the intended relational datatype contract.

5. Plan for partition-key updates

Changing a registration email can also change its domain and therefore its target partition.

ENABLE ROW MOVEMENT permits Oracle to move the row but the application and operational teams should still understand that such an update is a physical row movement rather than an in-place change.

6. Use representative statistics and execution plans

The presence of a virtual column, index, or partition does not guarantee that every query will use it.

Validate the strategy with:

representative data volumes,
realistic value distributions,
optimizer statistics,
execution plans, and
workload-level performance testing.

A Converged Data Platform Advantage

The broader value of this design is not a single SQL feature.

It is the ability to apply multiple data-management capabilities to the same JSON collection:

			
One JSON Collection
      |
      +--> Document-oriented application access
      +--> SQL reporting and analytics
      +--> Virtual relational projections
      +--> B-tree indexing
      +--> Automatic list partitioning
      +--> Partition pruning
      +--> Lifecycle and partition-level operations
      +--> ETAG-based optimistic concurrency

		

A single-purpose document platform can be effective at document persistence and retrieval. The architectural challenge begins when the organization also needs SQL analytics, operational segmentation, lifecycle management, and enterprise database controls.

The common response is to copy data into additional systems:

			
Document Database
      |
      +--> ETL to analytics platform
      +--> replication to reporting database
      +--> export to operational archive
      +--> synchronization to specialized services

		

Every additional copy introduces latency, synchronization logic, operational ownership, security scope, and failure modes.

Oracle AI Database provides another option: retain the document model while bringing SQL and enterprise database capabilities directly to the collection.

Recommended Final Design

For this demonstration, the recommended implementation is:

Create the JSON collection table with the required virtual columns from the beginning
Promote only document attributes that have recurring SQL, indexing, statistics, or operational value
Give promoted attributes explicit and realistic VARCHAR2(... CHAR) bounds
Bound derived expressions with an explicit SUBSTR length and CAST
Normalize the partitioning key
Use automatic list partitioning only when the key cardinality is governed
Enable row movement when the partitioning attribute can change
Use local indexes for common lookups within a pruned partition
Gather statistics after loading representative data
Validate the design with execution plans and operational maintenance tests

A refined final definition could use the normalized domain expression:

			
CREATE JSON COLLECTION TABLE matt.registrations WITH ETAG
(
  name_vc VARCHAR2(200 CHAR)
    GENERATED ALWAYS AS
    (
      JSON_VALUE(
        data,
        '$.name'
        RETURNING VARCHAR2(200 CHAR)
      )
    ),
  email_vc VARCHAR2(320 CHAR)
    GENERATED ALWAYS AS
    (
      JSON_VALUE(
        data,
        '$.email'
        RETURNING VARCHAR2(320 CHAR)
      )
    ),
  email_domain_vc VARCHAR2(255 CHAR)
    GENERATED ALWAYS AS
    (
      CAST(
        LOWER(
          SUBSTR(
            JSON_VALUE(
              data,
              '$.email'
              RETURNING VARCHAR2(320 CHAR)
            ),
            INSTR(
              JSON_VALUE(
                data,
                '$.email'
                RETURNING VARCHAR2(320 CHAR)
              ),
              '@'
            ) + 1,
            255
          )
        )
        AS VARCHAR2(255 CHAR)
      )
    )
)
PARTITION BY LIST (email_domain_vc) AUTOMATIC
(
  PARTITION p_oracle
    VALUES ('oracle.com')
)
ENABLE ROW MOVEMENT;

		

Add a local index for exact email lookups:

			
CREATE INDEX matt.idx_registrations_email_local
ON matt.registrations (email_vc)
LOCAL;

Then gather statistics:

			
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'MATT',
    tabname    => 'REGISTRATIONS',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade    => TRUE
  );
END;
/

		

Conclusion

JSON flexibility does not have to require operational simplicity.

By combining JSON collection tables, virtual columns, B-tree indexes, and automatic list partitioning, Oracle AI Database can support document-oriented applications while also providing SQL access, optimizer-aware access paths, partition pruning, and partition-level maintenance.

The important architectural shift is this:

The JSON document remains the flexible application model while selected high-value attributes can be promoted into bounded relational contracts for SQL optimization and operational management.

That means the document model does not have to become the limit of the data platform.

This approach allows organizations to optimize a document workload in place rather than immediately creating additional databases, copies, and synchronization pipelines for every new analytical or operational requirement.

Beyond Document Storage: Optimizing JSON Collections with Virtual Columns, Indexes, and Automatic List Partitioning

The Optimization Pattern

Step 0: Create the JSON Collection Table

Step 1: Project JSON Attributes as Virtual Columns

Why explicitly bound the virtual columns?

Why repeat the email expression?

Why virtual columns matter

Step 2: Index the Virtual Columns

Gather optimizer statistics

Step 3: Recreate the Collection with Automatic List Partitioning

Load Sample Documents

Verify Partition Pruning

Reconsider the Index Strategy After Partitioning

Operational Benefits Beyond Query Performance

Important Design Guardrails

1. Use a bounded partitioning key

2. Define behavior for missing or malformed attributes

3. Bound promoted attributes deliberately

4. Normalize the partitioning value

5. Plan for partition-key updates

6. Use representative statistics and execution plans

A Converged Data Platform Advantage

Recommended Final Design

Conclusion

Appendix: Oracle Documentation

Like this:

Related

Leave a ReplyCancel reply

The Optimization Pattern

Step 0: Create the JSON Collection Table

Step 1: Project JSON Attributes as Virtual Columns

Why explicitly bound the virtual columns?

Why repeat the email expression?

Why virtual columns matter

Step 2: Index the Virtual Columns

Gather optimizer statistics

Step 3: Recreate the Collection with Automatic List Partitioning

Load Sample Documents

Verify Partition Pruning

Reconsider the Index Strategy After Partitioning

Operational Benefits Beyond Query Performance

Important Design Guardrails

1. Use a bounded partitioning key

2. Define behavior for missing or malformed attributes

3. Bound promoted attributes deliberately

4. Normalize the partitioning value

5. Plan for partition-key updates

6. Use representative statistics and execution plans

A Converged Data Platform Advantage

Recommended Final Design

Conclusion

Appendix: Oracle Documentation

Share this:

Like this:

Related

Leave a ReplyCancel reply

Discover more from OraMatt: YABAOracle