A column of JSON data can be stored in the In-Memory Column Store (IM column store) to improve query performance.
See Also:Oracle Database In-Memory Guide
25.1 Overview of In-Memory JSON Data
You can move a table with a column of JSON data to the In-Memory Column Store (IM column store), to improve the performance of queries that share costly expressions by caching the expression results. This is especially useful for analytical queries that scan a large number of small JSON documents.
The IM column store is supported only for JSON documents smaller than 32,767 bytes. If you have a mixture of document sizes, those documents that are larger than 32,767 bytes are processed without the In-Memory optimization. For better performance, consider breaking up documents larger than 32,767 bytes into smaller documents.
The IM column store is an optional SGA pool that stores copies of tables and partitions in a special columnar format optimized for rapid scans. The IM column store supplements the row-based storage in the database buffer cache. You do not need to load the same object into both the IM column store and the buffer cache. The two caches are kept transactionally consistent. The database transparently sends online transaction processing (OLTP) queries (such as primary-key lookups) to the buffer cache and analytic and reporting queries to the IM column store.
You can think of the use of JSON data in memory as improving the performance of SQL/JSON path access. SQL/JSON functions
json_value, and SQL condition
json_exists all accept a SQL/JSON path argument, and they can all benefit from loading JSON data into the IM column store. (Full-text search using SQL/JSON function
json_textcontains does not benefit from the IM column store.) Once JSON documents have been loaded into memory, any subsequent path-based operations on them use the in-memory representation, which avoids the overhead associated with reading and parsing the on-disk format.
If queried JSON data is populated into the IM column store, and if there are function-based indexes that can apply to that data, the optimizer chooses whether to use an index or to scan the data in memory. In general, if index probing results in few documents then a functional index can be preferred by the optimizer. In practice this means that the optimizer can prefer a functional index for very selective queries or DML statements.
On the other hand, if index probing results in many documents then the optimizer might choose to scan the data in memory, by scanning the function-based index expression as a virtual-column expression.
Ad hoc queries, that is, queries that are not used frequently to target a given SQL/JSON path expression, benefit in a general way from populating JSON data into the IM column store, by quickly scanning the data. But if you have some frequently used queries then you can often further improve their performance in these ways:
Creating virtual columns that project scalar values (not under an array) from a column of JSON data and loading those virtual columns into the IM column store.
Creating a materialized view on a frequently queried
json_tableexpression and loading the view into the IM column store.
However, if you have a function-based index that projects a scalar value using function
json_value then you need not explicitly create a virtual column to project it. As mentioned above, in this case the function-based index expression is automatically loaded into the IM column store as a virtual column. The optimizer can choose, based on estimated cost, whether to scan the function-based index in the usual manner or to scan the index expression as a virtual-column expression.
The advantages of a virtual column over a materialized view are that you can build an index on it and you can obtain statistics on it for the optimizer.
Virtual columns, like columns in general, are subject to the 1000-column limit for a given table.
Prerequisites For Using JSON Data In Memory
To be able to take advantage of the IM column store for JSON data, the following must all be true:
Database compatibility is 220.127.116.11 or higher.
The values set for
max_string_sizein the Oracle instance startup configuration file must be
Sufficient SGA memory must be configured for the IM column store.
A DBA has specified that the tablespace, table, or materialized view that contains the JSON columns is eligible for population into the IM column store, using keyword
Initialization parameters are set as follows:
ENABLEallows in-memory materialization of dynamic expressions, if used in conjunction with PL/SQL procedure
ENABLE, meaning that the IM column store populates all virtual columns. (The default value is
The columns storing the JSON data must each have
is jsoncheck constraints. (That is, the data must be known to be JSON data.)
You can check the value of each initialization parameter using command
SHOW PARAMETER. (You must be logged in as database user
SYS or equivalent for this.) For example:
SHOW PARAMETER INMEMORY_VIRTUAL_COLUMNS
25.2 Populating JSON Data Into the In-Memory Column Store
ALTER TABLE INMEMORY to populate a table with a column of JSON data into the In-Memory Column Store (IM column store), to improve the performance of queries that share costly expressions by caching the results of evaluating those expressions.
The IM column store is an optional SGA pool that stores copies of tables and partitions in a special columnar format optimized for rapid scans. The IM column store supplements the row-based storage in the database buffer cache. (It does not replace the buffer cache, but you do not need to load the same object into both the IM column store and the buffer cache. The two caches are kept transactionally consistent.)
You specify that a table with a given JSON column (that is, a column that has an
is json check constraint) is to be populated into the IM column store by marking the table as
INMEMORY. Example 25-1 illustrates this.
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.
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 (18.104.22.168), 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 22.214.171.124. 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 an Oracle binary format, OSON.
Example 25-1 Populating JSON Data Into the IM Column Store
SELECT COUNT(1) FROM j_purchaseorder WHERE json_exists(po_document, '$.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(po_document, '$.ShippingInstructions?(@.Address.zipCode == 99236)'); -- The execution plan for the query now shows: TABLE ACCESS INMEMORY FULL
25.3 Upgrading Tables With JSON Data For Use With the In-Memory Column Store
A table with JSON columns created using a database that did not have a compatibility setting of at least 12.2 or did not have
max_string_size = extended must first be upgraded, before it can be populated into the In-Memory Column Store (IM column store). To do this, run script
rdbms/admin/utlimcjson.sql upgrades all existing tables that have JSON columns so they be populated into the IM column store. To use it, all of the following must be true:
compatiblemust be set to
max_string_sizemust be set to
The JSON columns being upgraded must have an
is jsoncheck constraint defined on them.