One of the new exciting features of Oracle 12cR2 is the ability to use In-Memory technology with JSON data.

Brief background:

• JSON stands for JavaScript Object Notation, JSON is a language-independent data format.

 

• 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 12.2.0.0

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;

SQL> @?/rdbms/admin/utl32k.sql

—– restart the database in normal mode:

SQL> shutdown immediate;

SQL> startup;

SQL> @?/rdbms/admin/utlrp.sql

**** 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:

Inmemory_expression_usage

Inmemory_virtual_columns

 

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

VALUES (

1,

SYSTIMESTAMP,

‘{

“color”: “black”,

“rgb”: [0,0,0],

“hex”: “#000000”}’);

 

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

 

SQL> @?/admin/utlimcjson.sql

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 .