31.2 Populating JSON Data Into the In-Memory Column Store

Use ALTER TABLE … INMEMORY to populate a column of JSON data, or a table with such a column, into the In-Memory Column Store (IM column store), to improve the performance of JSON queries.

You specify that a table with one or more columns of JSON data is to be populated into the IM column store, by marking the table as INMEMORY. Example 31-1 illustrates this.

A column is guaranteed to contain only well-formed JSON data if (1) it is of data type JSON or (2) it is of type VARCHAR2, CLOB, or BLOB and it has an is json check constraint. (Database initialization parameter compatible must be at least 20 to use data type JSON.)

The IM column store is used for queries of documents that are smaller than 32,767 bytes. Queries of documents that are larger than that do not benefit from the IM column store.

Note:

If a JSON column in a table that is to be populated into the IM column store was created using a database that did not have a compatibility setting of at least 12.2 or did not have max_string_size set to extended (this is the case prior to Oracle Database 12c Release 2 (12.2.0.1), for instance) then you must first run script rdbms/admin/utlimcjson.sql. It prepares all existing tables that have JSON columns to take advantage of the In-Memory JSON processing that was added in Release 12.2.0.1. See Upgrading Tables With JSON Data For Use With the In-Memory Column Store.

After you have marked a table that has JSON columns as INMEMORY, an In-Memory virtual column is added to it for each JSON column. The corresponding virtual column is used for queries of a given JSON column. The virtual column contains the same JSON data as the corresponding JSON column, but in OSON format, regardless of the data type of the JSON column (VARCHAR2, CLOB, BLOB, or JSON type). OSON is Oracle's optimized binary JSON format for fast query and update in both Oracle Database server and Oracle Database clients.

Populating JSON data into the IM column store using ALTER TABLE … INMEMORY provides support for ad hoc structural queries, that is, queries that you might not anticipate or use regularly.

If a column is of data type JSON then you can populate it into the IM column store using ALTER TABLE … INMEMORY TEXT, to provide support for full-text search. (Using ALTER TABLE … INMEMORY both with and without keyword TEXT for the same JSON column provides support for both ad hoc and full-text queries.)

Note:

If a JSON search index is defined for a JSON column (of any data type) that is populated into the IM Column Store then the search index, not the IM Column Store, is used for queries of that column.

See Also:

Example 31-1 Populating JSON Data Into the IM Column Store For Ad Hoc Query Support

SELECT COUNT(1) FROM j_purchaseorder
  WHERE json_exists(data,
                    '$.ShippingInstructions?(
                       @.Address.zipCode == 99236)');

-- The execution plan shows: TABLE ACCESS FULL


-- Specify table as INMEMORY, with default PRIORITY setting of NONE,
-- so it is populated only when a full scan is triggered.

ALTER TABLE j_purchaseorder INMEMORY;

-- Query the table again, to populate it into the IM column store.
SELECT COUNT(1) FROM j_purchaseorder
  WHERE json_exists(data,
                    '$.ShippingInstructions?(
                       @.Address.zipCode == 99236)');

-- The execution plan for the query now shows:
-- TABLE ACCESS INMEMORY FULL

Example 31-2 Populating a JSON Type Column Into the IM Column Store For Full-Text Query Support

This example populates column data of table j_purchaseorder into the IM column store for full-text support (keyword TEXT). 

ALTER TABLE j_purchaseorder INMEMORY TEXT (data);

If column data is not of JSON data type, and if no JSON search index is defined on the column, then JSON full-text querying is not supported. Trying to use json_textcontains to search the data raises an error in that case.

Related Topics

Parent topic: In-Memory JSON Data