Home » Articles » 19c » Here

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.

• Setup
• History
• Create Materialized View
• Query Rewrites
• Thoughts

Related articles.

• JSON_TABLE Materialized Views : ON STATEMENT Support in Oracle Database 18c
• JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, 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

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 and ON COMMIT fast refreshes, with materialized view logs present, in case the ON 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 a JSON_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 as ERROR ON ERROR NULL ON EMPTY are considered for rewrite.
• The functionality supports dot notation, JSON_VALUE and JSON_EXISTS calls, which can all be rewritten to JSON_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, 23c, All Articles

Hope this helps. Regards Tim...

Back to the Top.

Created: 2019-03-23  Updated: 2023-08-08

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer