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 expression column as the partitioning key, extracting the scalar column data from JSON data in the table using SQL/JSON function json_value.

A JSON expression column can be virtual or materialized. Oracle recommends that you use a materialized expression column for data that you query often. In that case, the expression is evaluated at DML time to obtain the column value, which is written to disk. That materialized value is then read from disk when needed. In contrast, a virtual column's expression is evaluated each time the column data is accessed. In effect, a materialized expression column caches the expression value.

Note:

A partitioning key specifies which partition a new table row is inserted into. With a partitioning key defined as a JSON expression column (virtual or materialized), 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, for insertions it can be more performant to partition using a non-JSON column.

For read access, if you use a materialized expression column then there is no difference in performance between a JSON column and a non-JSON column. This is because the expression has already been evaluated at DML time, and its value is simply read from disk.

Rules for Partitioning a Table Using a JSON Expression Column

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

  • The data type of the 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 column must not contain any predicates: the path must be streamable.

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

See Also:

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

This example creates JSON collection table purchaseorders_partitioned, which is partitioned using numeric materialized expression column po_num_vc. The json_value expression that defines the 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))
    MATERIALIZED)
  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