Maybe not so evident to make a second post about Oracle 12.2.0.1 about the JSON Index we already came to know in 12.1.0.2, but there has been some changes that make a nice bridge towards some real new functionality and the “JSON hook-in” towards JSON In-Memory Column Store support…
Hope you like this small overview / insight in the 12.2.0.1 version of the database.
Have a go at it via Oracle’s Exadata Express Cloud Service.
JSON Text Index support in 12.1.0.2
So what did we have in the 12.1.0.2 database version? Database 12.1.0.2 introduced JSON Text Index support via a Text Index “CTXSYS.JSON_SECTION_GROUP” context group definition.
If used in conjunction with JSON_TEXTCONTAINS( ) one could use Oracle Text select statements, operators, to find and search the JSON content.
JSON Text Index support in 12.2.0.1
In Oracle 12.2.0.1 this “create index” statement for JSON content changed:
- In its default form, it became simpler to apply/create
- JSON Dataguide functionality was added
- Automated Virtual Column creation functionality was added
In its most simple form, you can now just create an Oracle Text for JSON index via the following statement
CREATE SEARCH INDEX … FOR JSON
It will create a search index, a JSON dataguide, or both at the same time. The default behavior is to create both.
An example of creating such a “search index” for JSON might be (AFAIK at the time of writing)
CREATE SEARCH INDEX json_search_idx ON my_json_table (my_json_column)
PARAMETERS (DATAGUIDE OFF);
So in short the statement above, leaves only the search functionality enabled (dataguide now disabled), and represents the new improved 12.2.0.1 version of the Oracle Text index JSON version available in 12.1.0.2.
To enable “JSON Dataguide” for the search index created above, you use the ALTER INDEX statement, for example, like
ALTER INDEX json_search_idx REBUILD INDEX PARAMETERS (DATAGUIDE ON);
If you had created a default search index for JSON with both options enabled, but decided that you only needed the “JSON Dataguide” functionality, then the statement would be (to disable search)
ALTER INDEX json_search_idx REBUILD INDEXPARAMETERS (SEARCH_ON NONE);
So up till now we have 2 options for influencing the functionality of such an Oracle Search index for JSON: “search” and “dataguide”. But there is currently also a third one for use with virtual columns. The following example is from the (JSON) hands-on labs during Oracle OpenWorld 2015 and this year, 2016.
Note the “change add_vc“.
This example of the “create search index” statement will cause virtual columns to be automatically added to the base table. These virtual columns will contain the relational projected content of your JSON data. The In-Memory Column store can use this content of the virtual columns to it in memory.
Syntax
Below the syntax diagram as mentioned in the Oracle Text Reference
ADD_VC
Indicates whether virtual columns are created based on the dataguide.Function_name
Specifies the function to be executed when the dataguide changes.ON | OFF
Turns the dataguide tracking ON or OFF.TEXT
Indicates CONTAINS search only. Only $I is created.TEXT_VALUE
Indicates CONTAINS + RANGE_SEARCH_ENABLE with ALL option.NONE
Indicates that $I and $S* tables are not populated. This option enables you to have
dataguide only and no index tables..IS JSON check constraint is required for creating an index with simplified syntax.
The following are the default values when IS JSON check is enabled:
- DATAGUIDE ON
- SEARCH_ON TEXT_VALUE
- EMPTY STOPLIST
- SYNC ON COMMIT
TEXT_VALUE in short means “strings and numbers”.
To be continued…
“Eh wait! You haven’t explained…!”
I realise that I, intentionally, left a lot to be still explained.
For example: “What is JSON Dataguide…?“.
If I really wanted, for now, to go into that, this blog post would become waaaaayyy too big, so I will explain this in separate blog posts.
But to give you something to think about…
The JSON Dataguide allows you to discover information about the structure and content of JSON documents stored in the Oracle Database.
This information can be used in a number of ways including:
- Generating a JSON Schema document that describes the set of JSON documents
- Creating views that enable relational SQL operations on the JSON documents
- Automatically adding virtual columns based on simple key:value pairs
HTH/M
