Oracle 12cR2 In-Memory for JSON Columns

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”

max string size standard

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));

 

dummy table with 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;

 

dummy in memory

Checking it by querying user_tables :

select TABLE_NAME,INMEMORY from user_tables where INMEMORY=’ENABLED’;

user table query

—— 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 .

 

 

 

One thought on “Oracle 12cR2 In-Memory for JSON Columns

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s