One of the new exciting features of Oracle 12cR2 is the ability to use In-Memory technology with JSON data.
- In Oracle database JSON data are stored in columns with VARCHAR2, CLOB, or BLOB. These columns have “is json” check constraint.
Let us explore the setup:
The following prerequisites should be met before using In-Memory with JSON:
The compatibility of the database should be 22.214.171.124
SQL> show parameter compatible
The following parameter max_string_size must be set to “extended”
If its set to “standard” as normally it would be in your database, change it to “extended” by following the steps:
—— Shutdown the database
SQL> shutdown immediate;
—— Startup the database in upgrade mode
SQL> startup upgrade
—— change the parameter setting
SQL> alter system set MAX_STRING_SIZE =extended scope=spfile;
—– restart the database in normal mode:
SQL> shutdown immediate;
**** Remark: The reason you need to change to extended data type, is that when you create a JSON column with VARCHAR2 you will have the ability to store documents up to 32,767 bytes.
Also ensure the following init.ora parameter files are set:
And of course ensure that you enabled your in-memory area (feature).
Now, we can proceed in creating a dummy table with JSON for testing:
CREATE TABLE dummy_json
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
json_doc VARCHAR2 (23767)
CONSTRAINT ensure_json CHECK (json_doc IS JSON));
Then I will insert few records, for example:
INSERT INTO DUMMY_JSON
Remark: While inserting records if you face this error:
ORA-02290: check constraint (HR.ENSURE_JSON) violated
This means that the JSON data was inserted in a wrong format….so review your JSON code.
ALTER TABLE dummy_json INMEMORY;
Checking it by querying user_tables :
select TABLE_NAME,INMEMORY from user_tables where INMEMORY=’ENABLED’;
—— If you are already upgrading the database from 12cR1 to 12cR2 and have many tables with JSON column and would like to utilize the in-memory capability, you need first to ensure you met the perquisites as stated above are met and then execute the following script
This script will upgrade all currently existing tables with JSON columns so it can be populated in the in-memory area whenever this is required.
Finally, Having tables with JSON column in in-memory will improve performance for the following especially when using SQL Functions: json_table, json_query, and json_value .
One thought on “Oracle 12cR2 In-Memory for JSON Columns”