Oracle Database supports storing up to approximately 4000 bytes of data “inline” in the table row and if greater than 4000 bytes then the data is stored outside the table row (i.e. out-of-line). Since the VARCHAR2 data type is limited to 4000 bytes it is really just LOB data that is affected by this. However, starting in Oracle Database 12c Release 1 (12.1) we introduced the ability to have “Extended Data Types” which support up to 32K byte VARCHAR2 data types. This requires the init.ora parameter MAX_STRING_SIZE to be set to EXTENDED, but the whole in-line versus out-of-line storage issue still applies. So what does this have to do with Database In-Memory you might ask? The In-Memory column store (IM column store) will only populate data that is stored in-line in the on-disk segment. This means that for columns with data greater in size than 4000 bytes, the on-disk segment only stores a locator to the data, the actual data is stored in a separate LOB segment (i.e. out-of-line), and only the locator will be populated into the IM column store.

For most LOB data that was OK because in general, LOBs aren’t really something that you’re accessing in analytic queries. But what about JSON documents? We introduced support for JSON in 12.1 and in Oracle Database 12c Release 2 (12.2) we support a binary JSON format in the IM column store to leverage DBIM features to allow high performance analytics directly on the JSON data. You might also be thinking that typically JSON documents can be quite large, and to add them to Oracle Database 12c you’ve had to use a LOB or a 32K VARCHAR2 because a normal 4000 byte VARCHAR2 was just too small. There are also many customers who would like to scan their JSON documents that they have stored in the database with analytic queries. Can these customers store these documents in the IM column store, even though they are larger than 4000 bytes?

The answer is yes. If you do have JSON documents that are larger than 4000 bytes we can still populate them in the IM column store. There are a couple of things that we do specifically to optimize JSON data with Database In-Memory. The first is that when you define JSON columns we will automatically define virtual columns for those JSON columns under a setting to ensure that if they are populated they will be populated in a special binary format, similar to what we do with binary XML. We refer to this format as OSON, or Oracle JSON. This allows us to populate up to 32K bytes of OSON data in the column store. We do this by storing the OSON data in an In-Memory Expression Unit (IMEU), the same structure that we use for In-Memory Expressions. 

This behavior requires that you verify the setting of two init.ora parameters and ensure that each JSON column has an “is json” check constraint:

  • The use of extended data types must be enabled so you have to set MAX_STRING_SIZE to EXTENDED
  • INMEMORY_EXPRESSIONS_USAGE must be set to ENABLE or STATIC_ONLY
  • When a JSON column is defined a corresponding is json check constraint must also be defined

The following shows the creation of the J_PURCHASEORDER table from the JSON Developer’s Guide (Section 4 Creating a Table With a JSON Column) and the corresponding virtual column definition:

That section also shows the insertion of two rows which I do not show here.

Since the binary format of JSON (i.e. OSON) is typically much smaller than the text based format, this means that we can populate much larger JSON documents in the IM column store. This also means that you should be able to run analytic queries on a lot more of your JSON documents. This also applies to In-Memory Expressions (IME) as well. If you have large IMEs, that is greater than 4000 bytes but less than 32K bytes, we can also store those in the IM column store.

Section 25 In-Memory JSON Data in the JSON Developer’s Guide shows the steps necessary to populate the J_PURCHASEORDER table into the IM column store, and the expected result. The following is the plan I got from using SQL*Plus and the autotrace feature:

Note that we have a TABLE ACCESS INMEMORY FULL access path and in the “Predicate Information” we can see that we’re accessing the SYS_IME_OSON_000100000BF73832 column in-memory (from line 2). I have also listed the output from the V$IMEU_HEADER dynamic performance view to show that the 2 column values for the virtual column have actually been populated into an IMEU (and the corresponding object_id to show that it is associated with the J_PURCHASEORDER table):

The following chart shows how JSON data is populated in the IM column store:

If you’re interested in exploring this further you can reference this information and examples in the In-Memory Guide and in the Database JSON Developer’s Guide.

Original publish date: November 29, 2017