Skip to Main Content
  • Questions
  • Cost increases using json_value: jsontable evaluation

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ANDRES.

Asked: November 30, 2023 - 2:20 pm UTC

Last updated: December 04, 2023 - 7:04 am UTC

Version: 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi everyone!
I'm working with 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

I'm trying to obtain json elements as columns in a view.
When I saw the plan for the following sql I realized that the cost increases when json_value is used more than once in the same column.


/*Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production*/

/*TESTING JSON_VALUE SELECTING FROM DUAL */
--COST OK
WITH Example AS(SELECT '{"element_1":"Radio controlled plane","element_2":"Ship"}' Json_1, '{"element_3":"Car","element_4":"Train"}' Json_2 FROM DUAL)
SELECT json_value(json_1,'$.element_1') element_1_value,json_value(json_1,'$.element_2') element_2_value,json_value(json_2,'$.element_3') element_3_value
FROM   Example A;



/*TESTING JSON_VALUE FROM EXAMPLE TABLE*/
drop table JSON_TEST;

CREATE TABLE JSON_TEST
(
  test_id number(4),
  JSON_1  VARCHAR2(100 BYTE),
  JSON_2  VARCHAR2(100 BYTE)
);

/*INSERT TEST DATA*/
Insert into JSON_TEST
   (test_id,JSON_1, JSON_2)
 Values
   (1,'{"element_1":"Plane","element_2":"Ship"}', '{"element_3":"Car","element_4":"Train"}');
   
Insert into JSON_TEST
   (test_id,JSON_1, JSON_2)
 Values
   (2,'{"element_1":"Duck","element_2":"Dog"}', '{"element_3":"Cat","element_4":"Mouse"}');

Insert into JSON_TEST
   (test_id,JSON_1, JSON_2)
 Values
   (3,'{"element_1":"Glass","element_2":"Knife"}', '{"element_3":"Bottle","element_4":"Cup"}');
   
      
COMMIT;

/*TESTING COST WITH ONLY ONE ELEMENT FROM EACH JSON COLUMN (JSON_1, JSON_2)*/
--COST OK
SELECT json_value(json_1,'$.element_1') element_1_value,json_value(json_2,'$.element_3') element_3_value
FROM   JSON_TEST ;

/*TESTING COST WITH TWO ELEMENTS FROM ONE JSON COLUMN (JSON_1)*/
--COST INCREASES A LOT, JSONTABLE EVALUATION IS NOW PRESENT IN THE PLAN
SELECT json_value(json_1,'$.element_1') element_1_value,json_value(json_1,'$.element_2') element_2_value
FROM   JSON_TEST ;


There's a bug or i'm doing something wrong?
How can I or our DBA's fix this problem?

Thanks!!

and Connor said...

By default, once we start seeing multiple probes into the JSON, we remap it to JSON table to make things more efficient, ie

SELECT 
  json_value(json_1,'$.element_1') element_1_value,
  json_value(json_1,'$.element_2') element_2_value 
FROM   JSON_TEST ;


becomes

select p.c_02$ element_1_value,p.c_01$ element_2_value 
from json_test json_test,
  json_table( json_test.json_1, 
    '$' columns( c_01$ varchar2(4000) path '$.element_2' null on error , 
                 c_02$ varchar2(4000) path '$.element_1' null on error ) 
  )  p


which yields a different costing.

If you want to avoid that, you can use the NO_JSON_TABLE_TRANSFORM hint as per the docs

https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/function-JSON_QUERY.html#GUID-555D8633-8AA9-4878-A72A-C665FA769D91

but that is not officially supported until 23c

More to Explore

JSON

Need more information on JSON? Check out the JSON dev guide for the Oracle Database