Oracle 19c中JSON高阶使用技巧

Multi-model（多模）数据库，指的是在一个数据库中，可以同时支持多种存储引擎或存储类型，为应用提供各种数据服务，如下图所示：

Oracle数据库对于这几种存储格式都能够与进行很好的支持，本文介绍如何在Oracle数据库19c是使用JSON的物化视图来提高性能。

作为DBA或者有经验的开发人员，非常熟悉使用和利用物化视图来改善和优化性能，物化视图是包括一个查询结果的数据库对象，它可以是远程数据的本地副本，或者用来生成基于数据表求和的汇总表。对于数据仓库，创建的物化视图通常情况下是聚合视图。物化视图，就是物理表，不过这张表的数据通过oracle的内部机制可以定期刷新，将一些大的耗时的表连接用物化视图实现，在使用查询重写的时，会提高查询的效率。

JSON物化视图查询重写得到了增强，因此使用JSON_EXISTS、JSON_VALUE和其他函数的查询可以利用在包含JSON_TABLE函数的查询上创建的物化视图。

当表中的JSON文档包含数组时，这个特性特别有用。这种类型的物化视图为访问这些JSON数组中的数据提供了快速的性能。

物化视图使用快速刷新(Fast Refresh)查询重写:

作为Oracle 19c的性能增强，如果在json_table上创建了一个refresh-on-statement物化视图，并且应用了其他一些条件，那么匹配定义该视图的查询的查询可以被重写为物化视图表访问。可以使用这个特性，而不是创建多个函数索引。

使用Oracle Sample Database的Schema OE进行演示，19c数据库安装并不附带Sample Schema的安装，请参考Sample Schema Install文档进行安装。

创建一张物化视图，其中MYJSON表的DOC列是CLOB并通过约束设置它必须为JSON：

查询测试物化视图和查询基表MYJSON进行对比时间差异，比较结果。由于数据量很少只有269条记录，因此所用时间一致并不明显，但是如果有数百万条记录，同时有数千个并发用户，所提升的效率是极其可观的。

直接查询此物化视图的所需时间：

查询原始表MYJOSN来验证查询重写机制：

SELECT JSON_VALUE(doc, '$.geonames[0].fcode') Code,
      JSON_VALUE(doc, '$.geonames[0].countryName') Country,
      JSON_VALUE(doc, '$.geonames[0].adminName1') Region,
      JSON_VALUE(doc, '$.geonames[0].adminName2') Sub_Region,
      JSON_VALUE(doc, '$.geonames[0].toponymName') Title,
      JSON_VALUE(doc, '$.geonames[0].name') Name
from MYJSON
where JSON_VALUE(doc, '$.geonames[0].fcode') = 'CSTL'
  order by Region, Sub_Region;

直接查询基表MYJSON所用时间：

将缓存在内存中的执行计划和SQL刷出去：

对SQL语句进行执行计划解释，如下图所示：

DBMS_XPLAN查看执行计划:

DBMS_XPLAN包提供了一种简单的方式，以几种预定义格式显示EXPLAIN PLAN命令的输出。默认情况下，表功能DISPLAY格式化规划表并显示规划表的内容。我们可以看到查询被重写为JSON_TABLE调用，因为我们可以看到计划中的JSONTABLE评估步骤(6)，并且我们可以看到数据已经从JSON_CASTLES_MV物化视图(4)中返回。如下图所示：

其他:

基于以JSON文档形式持久化应用程序数据的无模式开发使您能够快速响应不断变化的应用程序需求。您可以更改和重新部署应用程序，而不需要更改它使用的存储模式。SQL和关系数据库为复杂的数据分析和报告提供了灵活的支持，以及坚如磐石的数据保护和访问控制。NoSQL数据库通常不是这样的，在过去，NoSQL数据库经常与JSON的无模式开发相关联。Oracle数据库为JSON数据提供了SQL和关系数据库的所有优点，你可以像其他类型的数据库数据一样以同样的方式存储和操作它。限于篇幅，具体请参考手册《JSON Developer's Guide》，链接如下：

https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/changes.html#GUID-D31E988A-82B0-4C21-A617-EC5FC1023821

编辑：殷海英