oracle json_value 与 json_table 解释查询计划

Question

我有一个疑问，它是不久前写的。基本上是一个使用 json_table 函数的物化视图。

最近，自从我们迁移到 Oracle 19c 以来，MV 有时有效，有时则无效。 我使用 oracle json_value 函数重写该查询。 查看查询计划，我发现使用 json_table 的查询要慢得多，但我不理解所有数据。

有人可以解释一下字节、CPU、时间等的含义吗？

这是使用 json_value

EXPLAIN PLAN for
SELECT
    JSON_VALUE(response, '$.ErrorRecord[0].xNumber')                   xNumber,
    JSON_VALUE(response, '$.ErrorRecord[0]."error field"')             ERROR_FIELD,
    JSON_VALUE(response, '$.ErrorRecord[0]."value of field in error"') VALUE_OF_FIELD_IN_ERROR,
    JSON_VALUE(response, '$.ErrorRecord[0]."error description"')       ERROR_DESCRIPTION,
    JSON_VALUE(request, '$.Status')                                    STATUS,
    sf.sv_code                                                        CENTER,
    TO_CHAR(arr.created_date_time, 'YYYYMMDD' )                        DATE_OCCURANCE
    
FROM
    aud_request_response arr , 
    person p,
    rep_mapper_svc_fco sf,
    rep_mapper_interface_error re
WHERE 
    JSON_VALUE(response, '$.ErrorRecord[0].xNumber') = p.registration_number (+)
    AND arr.response.Status = 'Error'
    AND arr.request.interfaceName = 'CLAIMS'
    AND JSON_VALUE(request, '$.DataRecord[0].ACO') = sf.fco_code(+)
    AND arr.request.interfaceName = re.interface_name 
    AND coalesce(sf.svc_code,'ATH')
        IN ('XS','YS','XZ','ZS','ASD')
GROUP BY 
    sf.sv_code,
    JSON_VALUE(request, '$.DataRecord[0].ACO'),
    arr.request.interfaceName,
    JSON_VALUE(request, '$.Status'), 
    JSON_VALUE(response, '$.ErrorRecord[0]."error field"'),
    arr.created_date_time,
    arr.updated_date_time,
    JSON_VALUE(response, '$.ErrorRecord[0]."value of field in error"'),
    JSON_VALUE(response, '$.ErrorRecord[0]."error description"'),
    JSON_VALUE(response, '$.ErrorRecord[0].xNumber') ;

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Plan hash value: 241534218
 
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                               |  1094 |   871K|       |  1877K  (1)| 00:01:14 |
|   1 |  HASH GROUP BY           |                               |  1094 |   871K|  4688K|  1877K  (1)| 00:01:14 |
|   2 |   NESTED LOOPS OUTER     |                               |  5259 |  4190K|       |  1877K  (1)| 00:01:14 |
|*  3 |    FILTER                |                               |       |       |       |            |          |
|*  4 |     HASH JOIN RIGHT OUTER|                               |  5259 |  4139K|       |  1866K  (1)| 00:01:13 |
|   5 |      TABLE ACCESS FULL   | REP_MAPPER_SVC_FCO         |    85 |   680 |       |     3   (0)| 00:00:01 |
|*  6 |      HASH JOIN RIGHT SEMI|                               |  5259 |  4098K|       |  1866K  (1)| 00:01:13 |
|*  7 |       TABLE ACCESS FULL  | REP_MAPPER_INTERFACE_ERROR |    33 |   363 |       |     5   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS FULL  | AUD_REQUEST_RESPONSE        |  5259 |  4041K|       |  1866K  (1)| 00:01:13 |
|*  9 |    INDEX UNIQUE SCAN     | PER_ANBR_IDX               |     1 |    10 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

通过使用 json_table

EXPLAIN PLAN for
SELECT
    jtresponse.xNumber as xNumber,
    jtresponse.error_field as ERROR_FIELD,
    replace(jtresponse.value_of_field_in_error, ',interfaceName=INTERFACES','') as VALUE_OF_FIELD_IN_ERROR,
    jtresponse.error_description as ERROR_DESCRIPTION,
    trim(arr.response.Status) as STATUS,
    sf.sv_code  as CENTER,
    TO_CHAR(arr.created_date_time, 'YYYYMMDD' ) as DATE_OCCURANCE
from 
    aud_request_response arr,
    person p,
    rep_mapper_svc_fco sf,
    rep_mapper_interface_error re,
    json_table(response, '$'
        COLUMNS (
        nested path '$.ErrorRecord[*]' columns (
            aNumber path '$.xNumber' null on error,
            error_field path '$."error field"' null on error,
            value_of_field_in_error path '$."value of field in error"' null on error,
            error_description path '$."error description"' null on error
    ))) jtresponse
    ,json_table(request, '$'
        COLUMNS (
        nested path '$.DataRecord[*]' columns (
            fileControl path '$.ACO' null on error
    ))) jtrequest
where  jtrequest.fileControl =sf.fco_code(+)
    and arr.request.interfaceName = 'CLAIMS'
    and arr.request.interfaceName = re.interface_name
    and jtresponse.xNumber = p.registration_number (+)
    and arr.response.Status='Error'
    and coalesce(sf.sv_code,'ATH') in('XS','YS','XZ','ZS','ASD')
GROUP BY 
    sv_code,
    jtrequest.fileControl,
    arr.request.interfaceName,
    arr.response.Status, 
    jtresponse.error_field,
    arr.created_date_time,
    arr.updated_date_time,
    jtresponse.value_of_field_in_error,
    jtresponse.error_description,
    jtresponse.xNumber;

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Plan hash value: 834586449
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                               |   350G|   260T|       |  1908M  (2)| 20:42:27 |
|   1 |  HASH GROUP BY             |                               |   350G|   260T|   290T|  1908M  (2)| 20:42:27 |
|   2 |   NESTED LOOPS             |                               |   350G|   260T|       |  1168M  (1)| 12:40:35 |
|*  3 |    FILTER                  |                               |       |       |       |            |          |
|*  4 |     HASH JOIN RIGHT OUTER  |                               |    42M|    32G|       |  2009K  (1)| 00:01:19 |
|   5 |      TABLE ACCESS FULL     | REP_MAPPER_SVC_FCO         |    85 |   680 |       |     3   (0)| 00:00:01 |
|   6 |      NESTED LOOPS          |                               |    42M|    32G|       |  2009K  (1)| 00:01:19 |
|*  7 |       HASH JOIN RIGHT SEMI |                               |  5259 |  4098K|       |  1866K  (1)| 00:01:13 |
|*  8 |        TABLE ACCESS FULL   | REP_MAPPER_INTERFACE_ERROR |    33 |   363 |       |     5   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL   | AUD_REQUEST_RESPONSE        |  5259 |  4041K|       |  1866K  (1)| 00:01:13 |
|  10 |       JSONTABLE EVALUATION |                               |       |       |       |            |          |
|  11 |    JSONTABLE EVALUATION    |                               |       |       |       |            |          |
--------------------------------------------------------------------------------------------------------------------

谢谢！

Answer 1

首先：这两个查询不等价！

json_value 查询获取 DataRecord 和 ErrorRecord 数组中的第一个条目。使用 json_table ，数据库为数组中的每个元素生成一行。

我发现 jtrequest 和 jtresponse 之间没有连接。因此查询正在生成这些数组的笛卡尔积。即，它为每个文档的第一个数组中的每个元素与第二个数组中的每个元素创建一行。

行/字节/时间列都是估计值。优化器根据表统计信息认为这是多少行/数据大小/查询持续时间。

计划中的第一行是查询将返回的（估计）内容。因此，对于 json_table，估计为：

• 350G => 350十亿行
• 260T => 260 TB 数据
• 20:42:27 => 20+ 小时的运行时间

这些数字可能由于多种原因而出错，但即使它们超出了 1000 倍，您仍然会看到大量数据。

我认为您需要弄清楚原始查询的目的 - 特别是为什么它生成两个数组的笛卡尔积。这会迅速增加数据量。