The title above gives you immediate knowledge of the end result of this post. If you want to know the whys and wherefores then by all means read on.
I was on a call yesterday listening to a presentation on Database 12c new features. The In-Memory Column Store received a lot of interest, as did the new JSON functionality.
One person on the call asked an interesting question, it was something along the lines of:
“If we have a table containing a JSON document can we use the In-Memory Column Store to optimise reports on the JSON attributes”
After some discussion it was decided that the best way to do that is to store the required JSON attributes in dedicated regular columns and report on those. After the call I thought about this some more and wondered if we can:
a) Expose JSON attributes as virtual columns and…
b) Utilise the In-Memory Column Store to report on those virtual columns
I thought these tests were worth executing. Here they are:
a) Can we expose JSON attributes as virtual columns
In order to set up the test I create a table with a JSON column and a virtual column using the function JSON_VALUE to retrieve a scalar value for an attribute called “Name”.
SQL> CREATE TABLE json_docs
( id NUMBER (10) NOT NULL
, date_loaded DATE
, doc CLOB
CONSTRAINT ensure_json CHECK (doc IS JSON)
, doc_name AS (JSON_VALUE(doc,'$.Name'))
);
Table created.
SQL> desc json_docs
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
DATE_LOADED DATE
DOC CLOB
DOC_NAME VARCHAR2(4000)
Looks promising. Let’s add some data.
insert into json_docs (id, date_loaded, doc)
values (1,sysdate,'{"Name": "Neil", "Email": "neil@email.com", "Job": "DBA"}');
insert into json_docs (id, date_loaded, doc)
values (2,sysdate,'{"name": "Bob", "Email": "bobl@email.com", "Job": "DBA"}');
commit;
set lines 120
column doc format a60
column doc_name format a10
select * from json_docs;
ID DATE_LOAD DOC DOC_NAME
--- --------- ------------------------------------------------------------ --------
1 13-NOV-14 {"Name": "Neil", "Email": "neil@email.com", "Job": "DBA"} Neil
2 13-NOV-14 {"name": "Bob", "Email": "bobl@email.com", "Job": "DBA"}
And there we have it. When the attribute “Name” is present at the top level of the JSON document then we can show it as a dedicated virtual column. I’m not suggesting this is a good idea but… it works.
b) Can we utilise the In-Memory Column Store to report on virtual columns
First I’ll add more data to the JSON_DOCS table.
delete json_docs;
insert into json_docs (id,date_loaded,doc)
select rownum, sysdate, '{"Name": "'||object_name||'", "Type": "'||object_type||'"}'
from all_objects
where rownum <= 500;
commit;
And set the table to be INMEMORY, excluding the JSON document column.
SQL> show parameter inmemory_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_size big integer 252M SQL> ALTER TABLE json_docs INMEMORY NO INMEMORY(doc);
Next query the data to kick of a load to the “In-Memory Area”
select count(*) from json_docs; column segment_name format a30 select segment_name,populate_status,bytes_not_populated from v$im_segments order by 1; SEGMENT_NAME POPULATE_ BYTES_NOT_POPULATED ------------------------------ --------- ------------------- JSON_DOCS COMPLETED 0
That looks promising. So let’s check which columns are cached.
select TABLE_NAME,COLUMN_NAME,INMEMORY_COMPRESSION from V$IM_COLUMN_LEVEL; TABLE_NAME COLUMN_NAME INMEMORY_COMPRESSION --------------- ---------------- --------------------- JSON_DOCS ID DEFAULT JSON_DOCS DATE_LOADED DEFAULT JSON_DOCS DOC NO INMEMORY
Not the virtual column… Can we force it:
ALTER TABLE json_docs INMEMORY (date_loaded, doc_name) NO INMEMORY (doc) ; * ERROR at line 1: ORA-64359: INMEMORY clause may not be specified for virtual columns
Nope. And probably for good reasons.
ORAganism 