8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Materialized View Support for Queries Containing JSON_TABLE in Oracle Database 19c
Oracle 18c introduced the ability to use the ON STATEMENT
refreshes of materialized views built with JSON_TABLE
function calls. Oracle 19c can perform query rewrites of statements using some SQL/JSON functions (JSON_VALUE
, JSON_EXISTS
) to use a materialized view containing an appropriate JSON_TABLE
call.
Thanks to Connor McDonald for helping me out with this feature. At the time of writing the documentation is very sparse, making it impossible to use the functionality unless you know the secret. :)
Related articles.
- JSON_TABLE Materialized Views : ON STATEMENT Support in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
Setup
Create and populate the following table to provide some JSON data to work with.
-- DROP TABLE json_documents PURGE; CREATE TABLE json_documents ( id RAW(16) NOT NULL, data CLOB, CONSTRAINT json_documents_pk PRIMARY KEY (id), CONSTRAINT json_documents_json_chk CHECK (data IS JSON) ); INSERT INTO json_documents (id, data) VALUES (SYS_GUID(), '{"customerid":1, "firstname":"Wonder", "lastname":"Woman", "item_id":101, "item_qty":1 }'); INSERT INTO json_documents (id, data) VALUES (SYS_GUID(), '{"customerid":1, "firstname":"Wonder", "lastname":"Woman", "item_id":102, "item_qty":3 }'); INSERT INTO json_documents (id, data) VALUES (SYS_GUID(), '{"customerid":2, "firstname":"Iron", "lastname":"Man", "item_id":101, "item_qty":1 }'); INSERT INTO json_documents (id, data) VALUES (SYS_GUID(), '{"customerid":2, "firstname":"Iron", "lastname":"Man", "item_id":102, "item_qty":3 }'); INSERT INTO json_documents (id, data) VALUES (SYS_GUID(), '{"customerid":2, "firstname":"Iron", "lastname":"Man", "item_id":103, "item_qty":1 }'); INSERT INTO json_documents (id, data) VALUES (SYS_GUID(), '{"customerid":3, "firstname":"Spider", "lastname":"Man", "item_id":101, "item_qty":1 }'); INSERT INTO json_documents (id, data) VALUES (SYS_GUID(), '{"customerid":4, "firstname":"Black", "lastname":"Pather", "item_id":101, "item_qty":1 }'); INSERT INTO json_documents (id, data) VALUES (SYS_GUID(), '{"customerid":4, "firstname":"Black", "lastname":"Pather", "item_id":102, "item_qty":6 }'); COMMIT;
History
An Oracle 12.2 performance new feature allowed multiple SQL/JSON calls to be converted to a single JSON_TABLE
call. We can see this in a 10053 trace file. For example, we might do the following.
ALTER SESSION SET EVENTS '10053 trace name context forever, level 8'; SELECT j.data.customerid, j.data.firstname, j.data.lastname FROM json_documents j WHERE j.data.lastname = 'Man'; ALTER SESSION SET EVENTS '10053 trace name context off';
This produces a very big trace file, but if we search for "Final query after transformations" we will see the what was actually run on the server. We can see this below. It's been formatted it a little to make it easier to read.
Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "P"."C_03$" "CUSTOMERID", "P"."C_02$" "FIRSTNAME", "P"."C_01$" "LASTNAME" FROM "TEST"."JSON_DOCUMENTS" "J", JSON_TABLE( "J"."DATA" /*+ LOB_BY_VALUE */ , '$' COLUMNS( "C_01$" VARCHAR2(4000) PATH '$.lastname' ASIS NULL ON ERROR , "C_02$" VARCHAR2(4000) PATH '$.firstname' ASIS NULL ON ERROR , "C_03$" VARCHAR2(4000) PATH '$.customerid' ASIS NULL ON ERROR ) ) "P" WHERE "P"."C_01$"='Man'
So our dot notation query was converted to a JSON_TABLE
call.
The rest of this article discusses an additional optimisation step, allowing this type of query to be rewritten to access an appropriate materialized view.
Create Materialized View
Create a materialized view using the ON STATEMENT
clause. Remember, materialized view logs are not necessary for ON STATEMENT
fast refreshes. Notice the error and null handling is specified explicitly. The rewrites shown here can only happen on columns defined using ERROR ON ERROR NULL ON EMPTY
explicitly. The ENABLE QUERY REWRITE
clause is not needed, but I like to include to show the intention to anyone looking at the code.
-- DROP MATERIALIZED VIEW json_documents_mv; CREATE MATERIALIZED VIEW json_documents_mv REFRESH FAST ON STATEMENT ENABLE QUERY REWRITE AS SELECT j.id, jt.customerid, jt.firstname, jt.lastname, jt.item_id, jt.item_qty FROM json_documents j, JSON_TABLE(data, '$' COLUMNS ( customerid NUMBER PATH '$.customerid' ERROR ON ERROR NULL ON EMPTY, firstname VARCHAR2(20) PATH '$.firstname' ERROR ON ERROR NULL ON EMPTY, lastname VARCHAR2(20) PATH '$.lastname' ERROR ON ERROR NULL ON EMPTY, item_id NUMBER PATH '$.item_id' ERROR ON ERROR NULL ON EMPTY, item_qty NUMBER PATH '$.item_qty' ERROR ON ERROR NULL ON EMPTY ) ) jt;
Query the data from the materialized view to check it is working as expected.
SET LINESIZE 100 COLUMN firstname FORMAT A10 COLUMN lastname FORMAT A10 SELECT * FROM json_documents_mv; ID CUSTOMERID FIRSTNAME LASTNAME ITEM_ID ITEM_QTY -------------------------------- ---------- ---------- ---------- ---------- ---------- 849CF244F7E82503E055000000000001 1 Wonder Woman 101 1 849CF244F7E92503E055000000000001 1 Wonder Woman 102 3 849CF244F7EA2503E055000000000001 2 Iron Man 101 1 849CF244F7EB2503E055000000000001 2 Iron Man 102 3 849CF244F7EC2503E055000000000001 2 Iron Man 103 1 849CF244F7ED2503E055000000000001 3 Spider Man 101 1 849CF244F7EE2503E055000000000001 4 Black Pather 101 1 849CF244F7EF2503E055000000000001 4 Black Pather 102 6 8 rows selected. SQL>
Query Rewrites
In this section we've edited out the query results and some of the output from the DBMS_XPLAN
package, so we just see the execution plan. This is to just make the output more readable.
We clear down the shared pool so previously parsed statements don't interfere with the results.
CONN / AS SYSDBA ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET CONTAINER = pdb1; --ALTER SYSTEM FLUSH SHARED_POOL; CONN test/test@pdb1 SET FEEDBACK ON TAB OFF LINESIZE 200 PAGESIZE 1000
We can see the dot notation calls get rewritten as a JSON_TABLE
call, because we can see the JSONTABLE EVALUATION
step in the plan, and we can see the data has been returned from the JSON_DOCUMENTS_MV
materialized view.
SELECT j.data.customerid, j.data.firstname, j.data.lastname FROM json_documents j WHERE j.data.lastname = 'Man'; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 89 (100)| | | 1 | NESTED LOOPS | | 21781 | 42M| 89 (2)| 00:00:01 | | 2 | NESTED LOOPS | | 3 | 6105 | 6 (17)| 00:00:01 | | 3 | SORT UNIQUE | | 3 | 69 | 3 (0)| 00:00:01 | |* 4 | MAT_VIEW ACCESS FULL | JSON_DOCUMENTS_MV | 3 | 69 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| JSON_DOCUMENTS | 1 | 2012 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | JSON_DOCUMENTS_PK | 1 | | 0 (0)| | | 7 | JSONTABLE EVALUATION | | | | | | ---------------------------------------------------------------------------------------------------
We can see the JSON_VALUE
calls get rewritten as a JSON_TABLE
call, because we can see the JSONTABLE EVALUATION
step in the plan, and we can see the data has been returned from the JSON_DOCUMENTS_MV
materialized view.
SELECT JSON_VALUE(data, '$.customerid') AS customerid, JSON_VALUE(data, '$.firstname') AS firstname, JSON_VALUE(data, '$.lastname') AS lastname FROM json_documents WHERE JSON_VALUE(data, '$.lastname') = 'Man'; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 89 (100)| | | 1 | NESTED LOOPS | | 21781 | 42M| 89 (2)| 00:00:01 | | 2 | NESTED LOOPS | | 3 | 6105 | 6 (17)| 00:00:01 | | 3 | SORT UNIQUE | | 3 | 69 | 3 (0)| 00:00:01 | |* 4 | MAT_VIEW ACCESS FULL | JSON_DOCUMENTS_MV | 3 | 69 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| JSON_DOCUMENTS | 1 | 2012 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | JSON_DOCUMENTS_PK | 1 | | 0 (0)| | | 7 | JSONTABLE EVALUATION | | | | | | ---------------------------------------------------------------------------------------------------
We don't get a query rewrite for a JSON_TABLE
call, even if we use exactly the same query that is used in the materialized view definition. That may be possible in a future version.
SELECT j.id, jt.customerid, jt.firstname, jt.lastname, jt.item_id, jt.item_qty FROM json_documents j, JSON_TABLE(data, '$' COLUMNS ( customerid NUMBER PATH '$.customerid' ERROR ON ERROR NULL ON EMPTY, firstname VARCHAR2(20) PATH '$.firstname' ERROR ON ERROR NULL ON EMPTY, lastname VARCHAR2(20) PATH '$.lastname' ERROR ON ERROR NULL ON EMPTY, item_id NUMBER PATH '$.item_id' ERROR ON ERROR NULL ON EMPTY, item_qty NUMBER PATH '$.item_qty' ERROR ON ERROR NULL ON EMPTY ) ) jt; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 222 (100)| | | 1 | NESTED LOOPS | | 65344 | 126M| 222 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL | JSON_DOCUMENTS | 8 | 16096 | 3 (0)| 00:00:01 | | 3 | JSONTABLE EVALUATION | | | | | | -----------------------------------------------------------------------------------------
If the query is too simple there may not be a rewrite from the SQL/JSON function call to a JSON_TABLE
call, which in turn means it will not be eligible to be rewritten to use the materialized view.
SELECT j.data.item_id, j.data.item_qty FROM json_documents j; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 221 (100)| | | 1 | NESTED LOOPS | | 65344 | 125M| 221 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | JSON_DOCUMENTS | 8 | 16016 | 3 (0)| 00:00:01 | | 3 | JSONTABLE EVALUATION | | | | | | -----------------------------------------------------------------------------------------
The functionality will work inside a bigger statement, provided the JSON work itself is complicated enough to warrant a rewrite to use a JSON_TABLE
call, which can then be optimised to use the materialized view. In this example we use a GROUP BY
on the results of a JSON evaluation.
SELECT j.data.item_id, SUM(j.data.item_qty) AS quantity FROM json_documents j WHERE j.data.lastname = 'Man' GROUP BY j.data.item_id ORDER BY j.data.item_id; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 91 (100)| | | 1 | SORT GROUP BY | | 21781 | 42M| 91 (4)| 00:00:01 | | 2 | NESTED LOOPS | | 21781 | 42M| 89 (2)| 00:00:01 | | 3 | NESTED LOOPS | | 3 | 6105 | 6 (17)| 00:00:01 | | 4 | SORT UNIQUE | | 3 | 69 | 3 (0)| 00:00:01 | |* 5 | MAT_VIEW ACCESS FULL | JSON_DOCUMENTS_MV | 3 | 69 | 3 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| JSON_DOCUMENTS | 1 | 2012 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | JSON_DOCUMENTS_PK | 1 | | 0 (0)| | | 8 | JSONTABLE EVALUATION | | | | | | ----------------------------------------------------------------------------------------------------
Thoughts
At the time of writing the documentation for this feature is really limited. There are only two mentions in the documentation.
The New Features Guide says the following as the first JSON new feature.
"Materialized views query rewriting has been enhanced so that queries with JSON_EXISTS, JSON_VALUE and other functions can utilize a materialized view created over a query that contains a JSON_TABLE function. This feature is particularly useful when the JSON documents in a table contain arrays. This type of materialized view provides fast performance for accessing data within those JSON arrays."
The JSON Developer's Guide says the following in the new features section, once again as the first JSON new feature listed. Notice the phrase I've highlighted in bold.
"Performance enhancement: If you create a refresh-on-statement materialized view over json_table and some other conditions apply then a query that matches the query defining the view can be rewritten to a materialized-view table access. You can use this feature instead of creating multiple functional indexes."
Interestingly, the latter did not have a link to any documentation, as the other new features did, and the phrase I highlighted seems very interesting.
When I first tried this functionality I was unable to get it to work. I tried a number of things including the following.
- Simplifying and complicating the JSON. The documentation mentions arrays, so I tried including those and pulling values from them also, in case they were a trigger in the codepath.
- Switching to
ON DEMAND
andON COMMIT
fast refreshes, with materialized view logs present, in case theON STATEMENT
reference was a mistake. - I looked through 10053 trace files to see if the optimization was happening, but not reflected in the execution plan. I could see the query rewrites from dot notation and
JSON_VALUE
calls to aJSON_TABLE
call, as often happens in previous versions too, but it never seemed to make the connection between that rewritten statement and the materialized view that has already done the work. - A number of settings for the
QUERY_REWRITE_INTEGRITY
parameter. - Setting
"_exadata_feature_on"=true
parameter, in case this was an Exadata-only feature. - Various combinations of statistics and no statistics on the base table and the materialized view.
- Clearing down the shared pool (instance-level and PDB-level) between each statement, in case a previous execution plan was used.
I couldn't find any combination of the above to make the query rewrite happen, which lead me to believe either this feature didn't exist, it was bugged, or there is some really important restriction missing from the documentation. I reached out to Connor McDonald, who got the answer for me. The following is a paraphrased version of that feedback.
- The
ENABLE QUERY REWRITE
clause is not needed. - The materialized view must be created with
REFRESH FAST ON STATEMENT
and include the rowid or primary key. - The materialized view can only be a join between the master table and one
JSON_TABLE
call. - Only
JSON_TABLE
columns defined asERROR ON ERROR NULL ON EMPTY
are considered for rewrite. - The functionality supports dot notation,
JSON_VALUE
andJSON_EXISTS
calls, which can all be rewritten toJSON_TABLE
calls, and are therefore applicable for a rewrite to use the materialized view.
For more information see:
- JSON_TABLE Materialized Views : ON STATEMENT Support in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
Hope this helps. Regards Tim...