Oracle 使用带有聚合函数的JSON_TABLE

嗨，团队

数据库版本: 12.2.0.0.3

在高水平上，成功完成脚本 (来自LiveSQL)，您将拥有:
1) 在STAGE_USER表中以JSON格式加载10k个用户数据。
2) 在STAGE_ROLE表中以JSON格式加载500角色数据。
1) 在STAGE_USERMEM表中以JSON格式加载10k用户-角色成员身份数据。每个用户都100角色。

[注意] ==> 您可能会在语句 #9、19和29执行期间收到 “ORA-06502: PL/SQL: numeric or value error”，这将关系数据转换为JSON格式。请忽略错误，数据将被加载到各自的表中。

在成功执行脚本后创建的所有三个暂存表上收集统计信息。

EXEC dbms_stats.gather_table_stats(USER, '');

以下是我看到使用JSON_TABLE运算符的响应时间延迟的两个用例:

1) 在成员资格表中找到分配角色 “DummyRole1” 的用户总数

以下SQL的平均响应时间: 〜3.9秒。
在该成员关系表中，随着数据增加到100k，平均响应时间增加到20秒。

SELECT COUNT(1)
FROM STAGE_USERMEM o,
        json_table(userrolemem_json, '$'
    COLUMNS 
        (
           NESTED PATH '$.user_mem_dtls.user_role_mem[*]'
             COLUMNS (role_id VARCHAR2(32 CHAR) PATH '$.role_id',
                      role_name  VARCHAR2(20 CHAR) PATH '$.role_name')
        )
    ) t
WHERE t.role_name='DummyRole1';

2) 在成员资格表中找到分配给每个用户的角色总数

以下SQL的平均响应时间: 〜4.5秒。
在该成员关系表中，随着数据增加到100k，平均响应时间增加到30秒。

SELECT t.user_id,COUNT(1)
FROM STAGE_USERMEM o,
        json_table(userrolemem_json, '$'
    COLUMNS 
        (
           user_id VARCHAR2(32 CHAR) PATH '$.user_id',
           NESTED PATH '$.user_mem_dtls.user_role_mem[*]'
             COLUMNS (role_id VARCHAR2(32 CHAR) PATH '$.role_id',
                      role_name  VARCHAR2(20 CHAR) PATH '$.role_name')
        )
    ) t
GROUP BY t.user_id;

您能否建议如何加快上述两个用例的响应时间，因为当负载增加时，响应时间会增加。

提前谢谢!

样本数据是否代表您的真实数据？

DummyRole1存在于每个JSON文档中。所以你要归还表上的每一行。

如果这只是一个 “例如”，并且在您的生产数据中，每个角色仅出现在 “几个” 文档中，则创建JSON搜索索引可能会带来一些好处。

为此，您首先需要在列中添加 “is json” 检查约束。然后添加索引:

alter table stage_usermem add constraint j check ( userrolemem_json is json );
create search index stage_usermem_ji on stage_usermem (userrolemem_json) for json;

但是，如果角色出现在大多数/所有文档中，添加这将带来很少或没有好处:

set serveroutput off
SELECT /*+ gather_plan_statistics */COUNT(1)
FROM STAGE_USERMEM o,
        json_table(userrolemem_json, '$'
    COLUMNS 
        (
           NESTED PATH '$.user_mem_dtls.user_role_mem[*]'
             COLUMNS (role_id    VARCHAR2(32 CHAR) PATH '$.role_id',
                      role_name  VARCHAR2(20 CHAR) PATH '$.role_name')
        )
    ) t
WHERE t.role_name='DummyRole1';

select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

Plan hash value: 2625201623                                                                                   
                                                                                                              
------------------------------------------------------------------------------------------------------------  
| Id  | Operation               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  
------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT        |               |      1 |        |      1 |00:00:01.97 |   20134 |  20000 |  
|   1 |  SORT AGGREGATE         |               |      1 |      1 |      1 |00:00:01.97 |   20134 |  20000 |  
|   2 |   NESTED LOOPS          |               |      1 |  10000 |  10000 |00:00:02.01 |   20134 |  20000 |  
|   3 |    TABLE ACCESS FULL    | STAGE_USERMEM |      1 |  10000 |  10000 |00:00:00.02 |     134 |      0 |  
|   4 |    JSONTABLE EVALUATION |               |  10000 |        |  10000 |00:00:01.93 |   20000 |  20000 |  
------------------------------------------------------------------------------------------------------------  
                                                                                                              
Note                                                                                                          
-----                                                                                                         
   - statistics feedback used for this statement 

alter table stage_usermem add constraint j check ( userrolemem_json is json );
create search index stage_usermem_ji on stage_usermem (userrolemem_json) for json;

SELECT /*+ gather_plan_statistics */COUNT(1)
FROM   STAGE_USERMEM o,
      json_table(userrolemem_json, '$'
       COLUMNS 
        (
           NESTED PATH '$.user_mem_dtls.user_role_mem[*]'
             COLUMNS (role_id    VARCHAR2(32 CHAR) PATH '$.role_id',
                      role_name  VARCHAR2(20 CHAR) PATH '$.role_name')
        )
    ) t
WHERE t.role_name='DummyRole1';

select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

Plan hash value: 3790423225                                                                                            
                                                                                                                       
---------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  
---------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT              |                  |      1 |        |      1 |00:00:02.07 |   21148 |  20877 |  
|   1 |  SORT AGGREGATE               |                  |      1 |      1 |      1 |00:00:02.07 |   21148 |  20877 |  
|   2 |   NESTED LOOPS                |                  |      1 |    408 |  10000 |00:00:01.96 |   21148 |  20877 |  
|   3 |    TABLE ACCESS BY INDEX ROWID| STAGE_USERMEM    |      1 |      5 |  10000 |00:00:00.06 |    1147 |    877 |  
|*  4 |     DOMAIN INDEX              | STAGE_USERMEM_JI |      1 |        |  10000 |00:00:00.03 |    1020 |    877 |  
|   5 |    JSONTABLE EVALUATION       |                  |  10000 |        |  10000 |00:00:01.84 |   20001 |  20000 |  
---------------------------------------------------------------------------------------------------------------------  
                                                                                                                       
Predicate Information (identified by operation id):                                                                    
---------------------------------------------------                                                                    
                                                                                                                       
   4 - access("CTXSYS"."CONTAINS"("O"."USERROLEMEM_JSON",'{DummyRole1} INPATH                                          
              (/user_mem_dtls/user_role_mem/role_name)')>0)  

请注意，使用索引时，缓冲区列中的值会增加。所以这使得查询做更多的工作!

因此，如果角色是常见的，您可能更适合在查询中创建物化视图。然后查询。例如:

create materialized view json_mv
as
SELECT t.role_name, COUNT(1)
FROM STAGE_USERMEM o,
        json_table(userrolemem_json, '$'
    COLUMNS 
        (
           NESTED PATH '$.user_mem_dtls.user_role_mem[*]'
             COLUMNS (role_id    VARCHAR2(32 CHAR) PATH '$.role_id',
                      role_name  VARCHAR2(20 CHAR) PATH '$.role_name')
        )
    ) t
group by t.role_name;

select /*+ gather_plan_statistics */* from json_mv
where  role_name = 'DummyRole1';

select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

Plan hash value: 445572365                                                                  
                                                                                            
------------------------------------------------------------------------------------------  
| Id  | Operation            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT     |         |      1 |        |      1 |00:00:00.01 |       9 |  
|*  1 |  MAT_VIEW ACCESS FULL| JSON_MV |      1 |      1 |      1 |00:00:00.01 |       9 |  
------------------------------------------------------------------------------------------  
                                                                                            
Predicate Information (identified by operation id):                                         
---------------------------------------------------                                         
                                                                                            
   1 - filter("ROLE_NAME"='DummyRole1')                                                     
                                                                                            
Note                                                                                        
-----                                                                                       
   - dynamic statistics used: dynamic sampling (level=2)  

请注意，缓冲区 (工作) 已急剧下降。你也应该看到更快的执行时间。

当然，值得一问:

你need要将数据存储为JSON？你可以用正则关系表代替吗？

这使得索引编制更加直接。

而且，如果您仔细查看原始计划，您会发现大多数时间都在进行 “JSONTABLE评估” 操作。即将数据从JSON转换为关系是大多数时间的地方。摆脱这一点，你可以让你的查询更快。