F5-TTS Model Installation on Windows - Easy Step by Step Tutorial

Need to store large volumes of JSON documents in SODA collections while keeping query performance high? You can now combine the simplicity of SODA with the power of Oracle table partitioning using **Mapped Collections**.

This approach lets you scale SODA collections efficiently without sacrificing flexibility.

Why Partition SODA Collections?

• Improve query performance on time-based or range-based data
• Enable easy data archiving and purging (drop old partitions)
• Better manage storage and maintenance for large document stores
• Support massive document workloads while keeping SODA APIs simple

Two Easy Ways to Add Partitioning

Option 1: Using a Trigger (Most Flexible)

CREATE TABLE MYCOL (
    "ID"           VARCHAR2(255) NOT NULL,
    "CREATED_ON"   TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL,
    "LAST_MODIFIED" TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL,
    "VERSION"      VARCHAR2(255) NOT NULL,
    "JSON_DOCUMENT" BLOB,
    "ORDER_TIMESTAMP" TIMESTAMP NOT NULL,
    PRIMARY KEY ("ID"),
    CHECK ("JSON_DOCUMENT" IS JSON FORMAT JSON STRICT)
)
LOB("JSON_DOCUMENT") STORE AS (CACHE)
PARTITION BY RANGE (ORDER_TIMESTAMP) 
(
    PARTITION p2019 VALUES LESS THAN (TIMESTAMP '2020-01-01 00:00:00'),
    PARTITION p2020 VALUES LESS THAN (TIMESTAMP '2021-01-01 00:00:00'),
    PARTITION p2021 VALUES LESS THAN (TIMESTAMP '2022-01-01 00:00:00')
);

-- Trigger to populate partition key from JSON
CREATE OR REPLACE TRIGGER MYCOL_PART_TRG
BEFORE INSERT OR UPDATE ON MYCOL
FOR EACH ROW
BEGIN
    :NEW.ORDER_TIMESTAMP := JSON_OBJECT_T.PARSE(:NEW.JSON_DOCUMENT).GET('timestamp').TO_TIMESTAMP;
END;
/

Option 2: Using a Virtual Column (Simpler, No Trigger)

CREATE TABLE MYCOL (
    "ID"            VARCHAR2(255) NOT NULL,
    "CREATED_ON"    TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL,
    "LAST_MODIFIED" TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL,
    "VERSION"       VARCHAR2(255) NOT NULL,
    "JSON_DOCUMENT" BLOB,
    "ORDER_TIMESTAMP" TIMESTAMP GENERATED ALWAYS AS 
        (JSON_VALUE("JSON_DOCUMENT", '$.timestamp' RETURNING TIMESTAMP)) NOT NULL,
    PRIMARY KEY ("ID"),
    CHECK ("JSON_DOCUMENT" IS JSON FORMAT JSON STRICT)
)
LOB("JSON_DOCUMENT") STORE AS (CACHE)
PARTITION BY RANGE (ORDER_TIMESTAMP) 
(
    PARTITION p2019 VALUES LESS THAN (TIMESTAMP '2020-01-01 00:00:00'),
    PARTITION p2020 VALUES LESS THAN (TIMESTAMP '2021-01-01 00:00:00')
);

Create a Mapped SODA Collection

DECLARE
    metadata CLOB;
    col      SODA_COLLECTION_T;
BEGIN
    metadata := '{
        "tableName": "MYCOL",
        "keyColumn": {"name":"ID", "sqlType":"VARCHAR2", "maxLength":255, "assignmentMethod":"UUID"},
        "contentColumn": {"name":"JSON_DOCUMENT", "sqlType":"BLOB", "compress":"NONE", "cache":true},
        "versionColumn": {"name":"VERSION", "method":"SHA256"},
        "lastModifiedColumn": {"name":"LAST_MODIFIED"},
        "creationTimeColumn": {"name":"CREATED_ON"}
    }';

    col := DBMS_SODA.CREATE_COLLECTION('MYCOL', metadata, DBMS_SODA.CREATE_MODE_MAP);
END;
/

Best Practices & Tips

• Use ISO 8601 format for timestamp fields in your JSON documents
• Enable row movement if documents may change partitions: ALTER TABLE MYCOL ENABLE ROW MOVEMENT;
• Partition pruning works perfectly with regular SQL queries
• You can still use all SODA operations (insert, find, replace, etc.) normally
• Remember: Drop mapped collections via SODA API first, then drop the table manually if needed

Conclusion

With Mapped Collections, you can bring the full power of Oracle partitioning to your SODA document stores. Whether you prefer triggers or virtual columns, the setup is straightforward and gives you excellent performance and manageability for large-scale JSON workloads.

This pattern is perfect for time-series data, audit logs, event stores, and any high-volume document use case.