1. JSON Developer's Guide
  2. Store and Manage JSON Data
  3. Partitioning JSON Data

10 Partitioning JSON Data

Partitioning can increase performance by using only a particular subset of the data in a table. To partition JSON data you use a JSON virtual column as the partitioning key, extracting the scalar column data from JSON data in the table using SQL/JSON function json_value.

Note:

A partitioning key specifies which partition a new table row is inserted into. With a partitioning key defined as a JSON virtual column, the partition-defining json_value expression is evaluated each time a row is inserted. This can be costly, especially for insertion of large JSON documents. For this reason, if your use case is a hybrid one, which uses relational as well as JSON data, it can be more performant to partition using a non-JSON column instead.

Rules for Partitioning a Table Using a JSON Virtual Column

  • The virtual column that serves as the partitioning key must be defined using SQL/JSON function json_value.

  • The data type of the virtual column is that returned by the json_value expression (determined by the RETURNING clause or a type-conversion item method).

  • The path expression used to extract the data for the virtual column must not contain any predicates: the path must be streamable.

  • The JSON column referenced by the expression that defines the virtual column can have an is json check constraint, but it need not have such a constraint.

See Also:

Partitioning Overview in Oracle Database VLDB and Partitioning Guide

Example 10-1 Creating a Partitioned Collection Table Using a JSON Virtual Column

This example creates JSON collection table purchaseorders_partitioned, which is partitioned using numeric virtual column po_num_vc. The json_value expression that defines the virtual column extracts field PONumber from the documents as a number using item method number(). 

CREATE JSON COLLECTION TABLE orders
  (po_num_vc NUMBER GENERATED ALWAYS AS
    (json_value (DATA, '$.PONumber.number()'
     ERROR ON ERROR)))
  PARTITION BY RANGE (po_num_vc)
   (PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000));

See JSON Storage Clause in Oracle Database SQL Language Reference for information about CREATE JSON COLLECTION TABLE

This execution of this query uses partitioning pruning. 

SELECT DATA FROM orders p
  WHERE p.data.PONumber.number() = 1234;

The presence of operation PARTITION RANGE SINGLE in the execution plan indicates that pruning is used. The plan shows that only partition 2 is accessed. (Partition 2 contains part number 1234, because 1000 <= 1234 < 2000.) 

---------------------------------------------------------
| Id  | Operation              | Name   | Pstart| Pstop |
---------------------------------------------------------
|   0 | SELECT STATEMENT       |        |       |       |
|   1 |  PARTITION RANGE SINGLE|        |     2 |     2 |
|   2 |   TABLE ACCESS FULL    | ORDERS |     2 |     2 |
---------------------------------------------------------

Related Topics

Parent topic: Store and Manage JSON Data