Hi Guys
DB version: 12.2.0.1.0
I have the following "Collection" table created in DB which contains various relationship information for a user in the system.
DESC "USERRELATIONSHIPS";
Name Null Type
------------- -------- -------------
ID NOT NULL VARCHAR2(255)
CREATED_ON NOT NULL TIMESTAMP(6)
LAST_MODIFIED NOT NULL TIMESTAMP(6)
VERSION NOT NULL VARCHAR2(255)
JSON_DOCUMENT BLOB
And have the following index on the table:
CREATE INDEX idx_usermem_1 ON "USERRELATIONSHIPS" (json_value(json_document, '$.user_id' RETURNING NUMBER ERROR ON ERROR));
In my DB I have the following "Collection" tables already created, along with the above one:
1) Users: contains 1M users
2) Roles: contains 50k roles
In "USERRELATIONSHIPS" collection table, I have 100k rows with each corresponding to users in the system along with their various relationship information. Each user is assigned 100 roles.
Below is output from BLOB JSON document column for a particular user.
{
"user_id":1087,
"user_rel_dtls":{
"user_role_mem":[
{
"role_key":1,
"role_name":"DummyRole1",
"mem_start_date":"13-JUN-01",
"mem_end_date":"13-JUN-30"
},
....
....
....
{
"role_key":100,
"role_name":"DummyRole100",
"mem_start_date":"13-JUN-01",
"mem_end_date":"13-JUN-30"
}
]
}
}
Now I am interested in finding the below:
1) Find total count of users assigned role DummyRole1
I have tried the below but it takes ~13-14 secs to produce output.
SELECT COUNT(1)
FROM "USERRELATIONSHIPS" o,
JSON_TABLE ( o.json_document,'$.user_rel_dtls.user_role_mem[*]'
COLUMNS (role_name VARCHAR2 ( 1000 CHAR ) PATH '$.role_name'
)
) t
WHERE t.role_name='DummyRole1';
How can I speed up this operation?
2) Find total no of roles assigned to each of the users in system
I have tried the below, but its not correct.
SELECT COUNT(1), json_value(json_document, '$.user_id' RETURNING NUMBER)
FROM "USERRELATIONSHIPS"
GROUP BY json_value(json_document, '$.user_id' RETURNING NUMBER);
Could you help me correct this one?
Many Thanks in advance!
So since you are examining fields that occur more than once, your options are (from the docs)
Queries That Access the Values of Fields That Can Occur More Than Once in a Given Document
In particular, this is the case when you access fields that are contained within an array.
There are three techniques you can use to tune the performance of such queries:
Place the table that contains the JSON data in the IM column store.
Use a JSON search index.
This indexes all of the fields in a JSON document along with their values, including fields that occur inside arrays. The index can optimize any path-based search, including those using path expressions that include filters and full-text operators. The index also supports range-based searches on numeric values.
Use a materialized view of non-JSON columns that are projected from JSON field values using SQL/JSON function json_table.
You can generate a separate row from each member of a JSON array, using the NESTED PATH clause with json_table.
A materialized view is typically used for optimizing SQL-based reporting and analytics for JSON content.
So a search index is useful for your first requirement, eg
SQL> create table t (
2 ID VARCHAR2(255) not null,
3 JSON_DOCUMENT BLOB not null,
4 constraint jchk check ( JSON_DOCUMENT is json )
5 );
Table created.
SQL>
SQL> declare
2 l_fragment varchar2(100) := '{"role_key":@@,"role_name":"DummyRole@@","mem_start_date":"13-JUN-01","mem_end_date":"13-JUN-30"},';
3 l_json varchar2(32000);
4 begin
5 for i in 1 .. 10000 loop
6 l_json := '{"user_id":'||i||',"user_rel_dtls":{ "user_role_mem":[';
7 for j in 1 .. dbms_random.value(10,50) loop
8 l_json := l_json || replace(l_fragment,'@@',j);
9 end loop;
10 l_json := rtrim(l_json,',') || ']}}';
11 insert into t values (i,utl_raw.cast_to_Raw(l_json));
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> CREATE SEARCH INDEX t_ix1 ON t (JSON_DOCUMENT) FOR JSON;
Index created.
SQL>
SQL> set autotrace on
SQL> SELECT COUNT(*)
2 FROM t
3 WHERE JSON_TEXTCONTAINS(JSON_DOCUMENT, '$.user_rel_dtls.user_role_mem.role_name', 'DummyRole17');
COUNT(*)
----------
8360
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1680799719
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2014 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2014 | | |
|* 2 | DOMAIN INDEX | T_IX1 | | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("T"."JSON_DOCUMENT",'DummyRole17
INPATH (/user_rel_dtls/user_role_mem/role_name)')>0)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
308 recursive calls
0 db block gets
645 consistent gets
3 physical reads
0 redo size
543 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
but for the second one, I don't think you can escape visiting every row and hence every JSON document, either at query time, or by expanding it to a materialized view.