Indexing and partitioning of JSON Collection Tables in Oracle Database 23ai

In the Oracle Database, a JSON Collection Table is a special table that provides JSON documents in a single JSON-type object column named DATA.

JSON Collection Tables stores JSON documents. You can create such a collection by using the CREATE JSON COLLECTION TABLE command. 

Each document in a JSON Collection Table must have a document-identifier field, _id, at the top level, whose value is unique for the collection. If you insert a document that does not have an _id field into a collection table then it’s added automatically, with a unique value that’s indexed for fast lookup. 

A JSON Collection Table has an additional option, ETAG support. If you provide CREATE JSON COLLECTION TABLE with the keywords WITH ETAG then each JSON document contains a document-handling field _metadata, whose value is an object with etag as its only field. If keywords WITH ETAG are not used then there is no _metadata field.

Let us consider the following collection called SALES meant for selling coffee. For simplicity purposes, we have only 2 documents in it. Here is how it looks like via MongoDB Compass. The Oracle database version is 23.5:

Here are 3 options of creating the collection:

1. create JSON collection table SALES;
2. create JSON collection table SALES with etag;
3. create JSON collection table SALES (price_vc NUMBER GENERATED ALWAYS AS (json_value (DATA, '$.price.number()' ERROR ON ERROR)))
PARTITION BY RANGE (price_vc)
(PARTITION p1 VALUES LESS THAN (6),
PARTITION p2 VALUES LESS THAN (10),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

The 3rd one shows how we can partition SALES on the price field – we are creating a virtual column call price_vc and that is how we can create the 3 partitions. The price_vc virtual column will be the partitioning key.

Let us now insert the 2 documents:

Here is the insert (for copy/paste) in case you would like to try it in your database:

insert into SALES values (json{ '_id' : 1, 'item' : 'Americanos', 'price' : 7, 'size': 'Short', 'quantity' : 15 });
insert into SALES values (json{ '_id' : 2, 'item' : 'Espresso', 'price' : 5, 'size': 'Short', 'quantity' : 22,
'CoffeeItems' : [{ 'Details'     : { 'Description' : 'Italian Espresso',
                                'UnitPrice'   : 5,
                                'Code'        : 28995},
                   'Quantity' : 9.0 },
                 { 'Details'     : { 'Description' : 'Finnish Espresso',
                                'UnitPrice'   : 5,
                                'Code'        : 28996},
                   'Quantity' : 11.0 } ] });

Let us see if the execution of this query below uses partitioning pruning.

The presence of operation PARTITION RANGE SINGLE in the execution plan below indicates that pruning is used (partition P1).

Next, let us see what is new with indexing JSON data. There are several ways to index JSON data, you can:

1. create multivalue indexes to index scalar values within arrays;
2. index JSON data in a general way using a JSON search index, for ad hoc structural queries and full-text queries;
3. create a bitmap index for SQL/JSON function json_value;
4. create a B-tree function-based index for SQL/JSON function json_value;
5. index multiple JSON fields using a composite B-tree index;
6. create a spatial index for scalar GeoJSON data

With Oracle Database 23ai, you can also create subsetting and partial indexes on JSON data.

Here is how to create a path subsetting index for JSON (check the fields being included):

SQL> CREATE SEARCH INDEX sales_search_idx ON SALES (data) FOR JSON
PARAMETERS ('SYNC (ON COMMIT) SEARCH_ON TEXT
INCLUDE ($.size, $.CoffeeItems.quantity) VALUE(VARCHAR2, NUMBER) INCLUDE ($.price) VALUE(NUMBER)');

Index created.

Here is how to create a partial index for JSON (indexing price fields with price up to 5 only):

SQL> CREATE INDEX sales_price_idx on SALES(CASE WHEN JSON_VALUE(data, '$.price') <=5
THEN JSON_VALUE(data, '$.price' RETURNING NUMBER ERROR ON ERROR) ELSE NULL END);

Index created.

Note: you can also create JSON indexes in ADB via Database Actions for JSON:

Read MoreJulian Dontcheff’s Database Blog