Team:
Below is a test case modelled like our business need.
Kindly help us to understand why the index build on the function "json_value" is not getting used. when exposed from views?
but the same works, without views in place.
we are on Oracle 18c (18.10) on Exacc platform.
<code>
Sample json:
{
"memberId" : ":B1",
"claimType" : "Dental Dental",
"claimNumber" : "19A561721900",
"billed" : "550.00",
"paid" : "0.00",
"serviceDateFrom" : "05/01/2019",
"serviceDateTo" : "05/01/2019",
"memberName" : "MEMBER NAME",
"status" : "Encounter Processed",
"checkNumber" : null,
"preAuthId" : " ",
"payee" : null,
"providerNum" : null,
"createdBy" : "PRBATCH",
"providerName" : null,
"versNbr" : "1",
"etlDateCreated" : "10/27/2020T21:45:58.560-0400",
"etlDateUpdated" : "10/27/2020T21:45:58.560-0400",
"etlCreatedBy" : "WSMART",
"brand" : "HOT",
"remitNumber" : null,
"claimSiteId" : " ",
"ediClaim" : "Yes",
"taxId" : "1234567",
"checkDate" : null,
"facetMemberId" : ":B2",
"npi" : null,
"providerLastName" : null,
"claimCheckCashedDate" : null,
"codeForDenyingClaim" : " ",
"referringProvnpi" : " ",
"referringProvTaxId" : " ",
"renderingProvTaxId" : null,
"payeeProvId" : " ",
"servicingProvnpi" : "123456789",
"renderingProvnpi" : null,
"claimSubType" : "Dental",
"employeeFlag" : "No",
"tenantId" : "HELLOWORLD",
"paidDate" : "01/01/1753",
"sdmSrcSysCd" : "ITS_ME",
"productCategory" : "A001",
"applicationTypeCode" : "M",
"lobId" : "1008",
"legacyPpoMedClmStatCd" : null
}
drop table t2 purge;
create table t2 ( data_json clob constraint t2_chk check( data_json is json ) )
tablespace es_index
compress for query high
nologging
lob(data_json)
store as securefile ( nocache nologging tablespace es_index compress high);
declare
l_sql long;
begin
l_sql := q'# {
"memberId" : ":B1",
"claimType" : "Dental Dental",
"claimNumber" : "19A561721900",
"billed" : "550.00",
"paid" : "0.00",
"serviceDateFrom" : "05/01/2019",
"serviceDateTo" : "05/01/2019",
"memberName" : "MEMBER NAME",
"status" : "Encounter Processed",
"checkNumber" : null,
"preAuthId" : " ",
"payee" : null,
"providerNum" : null,
"createdBy" : "PRBATCH",
"providerName" : null,
"versNbr" : "1",
"etlDateCreated" : "10/27/2020T21:45:58.560-0400",
"etlDateUpdated" : "10/27/2020T21:45:58.560-0400",
"etlCreatedBy" : "WSMART",
"brand" : "HOT",
"remitNumber" : null,
"claimSiteId" : " ",
"ediClaim" : "Yes",
"taxId" : "1234567",
"checkDate" : null,
"facetMemberId" : ":B2",
"npi" : null,
"providerLastName" : null,
"claimCheckCashedDate" : null,
"codeForDenyingClaim" : " ",
"referringProvnpi" : " ",
"referringProvTaxId" : " ",
"renderingProvTaxId" : null,
"payeeProvId" : " ",
"servicingProvnpi" : "123456789",
"renderingProvnpi" : null,
"claimSubType" : "Dental",
"employeeFlag" : "No",
"tenantId" : "HELLOWORLD",
"paidDate" : "01/01/1753",
"sdmSrcSysCd" : "ITS_ME",
"productCategory" : "A001",
"applicationTypeCode" : "M",
"lobId" : "1008",
"legacyPpoMedClmStatCd" : null
} #';
insert /*+ append */ into t2(data_json)
select replace( replace(l_sql,':B1','JUT'||rownum), ':B1','JUT'||rownum)
from all_objects, all_users
where rownum <=100000;
commit;
end;
/
create or replace view sc_vw
as
SELECT json_value(data_json,'$.serviceDateFrom') AS servicestartdt
,json_value(data_json,'$.providerLastName') AS providerlastname
,json_value(data_json,'$.claimNumber') AS claim_number
,json_value(data_json,'$.paid') AS amtpaid
,json_value(data_json,'$.checkNumber') AS chknumber
,json_value(data_json,'$.claimCheckCashedDate') AS chkcashedon
,json_value(data_json,'$.paidDate') AS settledate
,json_value(data_json,'$.codeForDenyingClaim') AS eoc_code
,json_value(data_json,'$.claimSubType') AS claim_type
,json_value(data_json,'$.memberId') as memberId
,json_value(data_json,'$.claimSubType') as claimSubType
FROM t2 ;
create index t2_idx
on t2( json_value(data_json,'$.memberId') )
tablespace es_index
compress advanced low
nologging;
exec dbms_stats.gather_table_stats(user,'T2',method_opt=>'for all columns size auto for all hidden columns size 2048');
demo@PDB1> set autotrace traceonly exp
demo@PDB1> select * from sc_vw where memberid ='JUT23' ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3533032265
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168K| 6683M| 2724K (1)| 00:01:47 |
| 1 | NESTED LOOPS | | 8168K| 6683M| 2724K (1)| 00:01:47 |
| 2 | TABLE ACCESS STORAGE FULL| T2 | 100K| 79M| 2739 (1)| 00:00:01 |
|* 3 | JSONTABLE EVALUATION | | | | | |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("P"."C_01$"='JUT23')
</code>
Here is the row source execution statistics.
<code>
demo@PDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
SQL_ID 4y53zts1f4gdx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from sc_vw where memberid
='JUT23'
Plan hash value: 3533032265
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 10108 |
| 1 | NESTED LOOPS | | 1 | 8168K| 1 |00:00:00.01 | 10108 |
| 2 | TABLE ACCESS STORAGE FULL| T2 | 1 | 100K| 100K|00:00:00.22 | 10108 |
| 3 | JSONTABLE EVALUATION | | 100K| | 1 |00:00:04.40 | 0 |
---------------------------------------------------------------------------------------------
16 rows selected.
</code>
without the view, if we write code on the base table as such it works nice.
<code>
demo@PDB1> set autotrace traceonly exp
demo@PDB1> SELECT json_value(data_json,'$.serviceDateFrom') AS servicestartdt
2 ,json_value(data_json,'$.providerLastName') AS providerlastname
3 ,json_value(data_json,'$.claimNumber') AS claim_number
4 ,json_value(data_json,'$.paid') AS amtpaid
5 ,json_value(data_json,'$.checkNumber') AS chknumber
6 ,json_value(data_json,'$.claimCheckCashedDate') AS chkcashedon
7 ,json_value(data_json,'$.paidDate') AS settledate
8 ,json_value(data_json,'$.codeForDenyingClaim') AS eoc_code
9 ,json_value(data_json,'$.claimSubType') AS claim_type
10 ,json_value(data_json,'$.memberId') as memberId
11 ,json_value(data_json,'$.claimSubType') as claimSubType
12 FROM t2
13 where json_value(data_json,'$.memberId') = 'JUT23';
Execution Plan
----------------------------------------------------------
Plan hash value: 2178202383
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8191 | 6911K| 31 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 8191 | 6911K| 31 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 846 | 2 (0)| 00:00:01 |
| 3 | SORT CLUSTER BY ROWID BATCHED | | 1 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | JSONTABLE EVALUATION | | | | | |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(JSON_VALUE("DATA_JSON" /*+ LOB_BY_VALUE */ FORMAT JSON , '$.memberId'
RETURNING VARCHAR2(4000) NULL ON ERROR)='JUT23')
demo@PDB1> set autotrace off
demo@PDB1>
demo@PDB1> SELECT /*+ gather_plan_statistics */
2 json_value(data_json,'$.serviceDateFrom') AS servicestartdt
3 ,json_value(data_json,'$.providerLastName') AS providerlastname
4 ,json_value(data_json,'$.claimNumber') AS claim_number
5 ,json_value(data_json,'$.paid') AS amtpaid
6 ,json_value(data_json,'$.checkNumber') AS chknumber
7 ,json_value(data_json,'$.claimCheckCashedDate') AS chkcashedon
8 ,json_value(data_json,'$.paidDate') AS settledate
9 ,json_value(data_json,'$.codeForDenyingClaim') AS eoc_code
10 ,json_value(data_json,'$.claimSubType') AS claim_type
11 ,json_value(data_json,'$.memberId') as memberId
12 ,json_value(data_json,'$.claimSubType') as claimSubType
13 FROM t2
14 where json_value(data_json,'$.memberId') = 'JUT23';
demo@PDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID 0rp7a71xgnspj, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */
json_value(data_json,'$.serviceDateFrom') AS servicestartdt
,json_value(data_json,'$.providerLastName') AS providerlastname
,json_value(data_json,'$.claimNumber') AS claim_number
,json_value(data_json,'$.paid') AS amtpaid
,json_value(data_json,'$.checkNumber') AS chknumber
,json_value(data_json,'$.claimCheckCashedDate') AS chkcashedon
,json_value(data_json,'$.paidDate') AS settledate
,json_value(data_json,'$.codeForDenyingClaim') AS eoc_code
,json_value(data_json,'$.claimSubType') AS claim_type
,json_value(data_json,'$.memberId') as memberId
,json_value(data_json,'$.claimSubType') as claimSubType FROM t2 where
json_value(data_json,'$.memberId') = 'JUT23'
Plan hash value: 2178202383
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | |
| 1 | NESTED LOOPS | | 1 | 8191 | 1 |00:00:00.01 | 3 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 3 | SORT CLUSTER BY ROWID BATCHED | | 1 | 1 | 1 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | 1 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
| 5 | JSONTABLE EVALUATION | | 1 | | 1 |00:00:00.01 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."SYS_NC00003$"='JUT23')
34 rows selected.
demo@PDB1>
</code>