我有一个查询，它是不久前写的。基本上是一个使用json_table函数的Materialized View。
最近，自从我们搬到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 | | | | | | |
--------------------------------------------------------------------------------------------------------------------
谢谢！
首先：这两个查询不等价！
json_value查询获取DataRecord和ErrorRecord数组中的第一个条目。对于json_table，数据库为数组中的每个元素生成一行。
我看不到jtrequest和jtresponse之间的联接。因此，查询正在生成这些数组的笛卡尔乘积。即，它为第一个数组中的每个元素创建一行，并为每个文档创建第二个数组的每个元素。
行/字节/时间列都是估计值。优化器认为这是基于表统计数据的行数/大小数据/查询持续时间。
计划的最上面一行是查询将返回的(估计(值。因此，对于json_table，它估计：
- 350G=>350亿行
- 260T＝>260TB的数据
- 20:42:27=>运行时间超过20小时
这些数字可能是错误的，原因有很多，但即使它们超出了1000倍，你仍然会看到大量的数据。
我认为您需要弄清楚原始查询的目的，特别是为什么它生成两个数组的笛卡尔乘积。这会迅速增加数据量。