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!!
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