Skip to Main Content
  • Questions
  • BTree index on JSON_VALUE function is not getting used.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: November 18, 2020 - 5:55 pm UTC

Answered by: Chris Saxon - Last updated: November 19, 2020 - 5:48 pm UTC

Category: Database Development - Version: 18.10

You Asked

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.

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')



Here is the row source execution statistics.

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.   


without the view, if we write code on the base table as such it works nice.

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>

and we said...

To improve performance, the database transforms multiple calls to JSON_value to a single JSON_table call.

So when you query the view, the final query is something like:

select ...
from  "T2",
     json_table ("DATA_JSON",'$'
       columns (
         ...
       )
     ) "P"
where "P"."C_01$" = 'JUT23'


Sadly JSON_table doesn't use indexes currently.

There is a fix for this in 20c so JSON_table can use indexes; speak to support about getting a backport for this.

and you rated our response

  (1 rating)

Reviews

support fix

November 19, 2020 - 12:00 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Thanks chris - that help us a lot.

So how should i reach support to ask for the Fix ? any bug/patch/reference number to it? please share.
Chris Saxon

Followup  

November 19, 2020 - 5:48 pm UTC

BUG 28855636 - it's an internal bug, so you won't be able to see it yourself.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.