Oracle 18c introduced the ability to use the refreshes of materialized views built with function calls. Oracle 19c can perform query rewrites of statements using some SQL/JSON functions ( , ) to use a materialized view containing an appropriate 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. :) - Setup - History - Create Materialized View - Query Rewrites - Thoughts Related articles. Create and populate the following table to provide some JSON data to work with. An Oracle 12.2 performance new feature allowed multiple SQL/JSON calls to be converted to a single call. We can see this in a 10053 trace file. For example, we might do the following. 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. So our dot notation query was converted to a 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 a materialized view using the clause. Remember, materialized view logs are not necessary for fast refreshes. Notice the error and null handling is specified explicitly. The rewrites shown here can only happen on columns defined using explicitly. The clause is not needed, but I like to include to show the intention to anyone looking at the code. Query the data from the materialized view to check it is working as expected. In this section we've edited out the query results and some of the output from the 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. We can see the dot notation calls get rewritten as a call, because we can see the step in the plan, and we can see the data has been returned from the materialized view. We can see the calls get rewritten as a call, because we can see the step in the plan, and we can see the data has been returned from the materialized view. We don't get a query rewrite for a 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. If the query is too simple there may not be a rewrite from the SQL/JSON function call to a call, which in turn means it will not be eligible to be rewritten to use the materialized view. The functionality will work inside a bigger statement, provided the JSON work itself is complicated enough to warrant a rewrite to use a call, which can then be optimised to use the materialized view. In this example we use a on the results of a JSON evaluation. 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 and fast refreshes, with materialized view logs present, in case the 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 calls to a 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 parameter. - Setting 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 clause is not needed. - The materialized view must be created with and include the rowid or primary key. - The materialized view can only be a join between the master table and one call. Only columns defined as are considered for rewrite. The functionality supports dot notation, and calls, which can all be rewritten to calls, and are therefore applicable for a rewrite to use the materialized view. - Only columns defined as are considered for rewrite. - The functionality supports dot notation, and calls, which can all be rewritten to calls, and are therefore applicable for a rewrite to use the materialized view. For more information see: Hope this helps. Regards Tim...