Weird behavior of UPDATE when a SDO_GEOMETRY column needs to be updated

A few days ago I posted a comment in the SQL & PL/SQL forum regarding a strange situation that occurs when updating a SDO_GEOMETRY column with a value resulting from the execution of a user-defined function, i.e.:

UPDATE TEST_GEOM
   SET SHAPE = MY_FUNCTION(SHAPE)
 WHERE ID = 1;

It turns out that in the case of a SDO_GEOMETRY column MY_FUNCTION is executed 8 times for each row being updated.

This phenomenon is further enhanced by adding a "sleep" of 1 second inside the function, which causes the update to take 8 seconds. 

This has been verified on Oracle 12.2.0.1.0 and Oracle Version 19.18.0.0.0.

Oddly enough, if you update another non-geometry column with a second user-defined function at the same time, the latter is executed only once.

UPDATE TEST_GEOM
   SET SHAPE = MY_FUNCTION(SHAPE),
       NUM   = ANOTHER_FUNCTION(10)
 WHERE ID = 1;

You can find below the code to reproduce the problem.

Anton Scheffer, who replied to my comment, pointed out that adding DETERMINISTIC to the definition of the function would fix the problem, which is an excellent workaround, but it is still a workaround, I mean, why should the function gets executed 8 times in the first place?

And why a second numeric function doesn't need the DETERMINISTIC clause to be added but still works as expected?

These questions remain unanswered.

 

CREATE SEQUENCE DUMMY_SEQ MINVALUE 0 ORDER;
CREATE SEQUENCE DUMMY2_SEQ MINVALUE 0 ORDER;

SELECT DUMMY_SEQ.NEXTVAL FROM DUAL;
SELECT DUMMY2_SEQ.NEXTVAL FROM DUAL;


CREATE TABLE "TEST_GEOM" 
( "ID"    INTEGER, 
  "NUM"   NUMBER,
  "SHAPE" SDO_GEOMETRY
 );

SET DEFINE OFF;
Insert into TEST_GEOM (ID, SHAPE) 
values (1, MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1, 53, 2003, 1), MDSYS.SDO_ORDINATE_ARRAY(1617857.39992003, 4927454.61993996, 1617859.95997274, 4927451.20990379, 1617869.76990222, 4927448.42992377, 1617871.36989997, 4927394.54999967, 1617870.37998793, 4927351.50990073, 1617883.78998438, 4927349.87990116, 1617900.64988697, 4927346.58992017, 1617900.31990137, 4927344.38001657, 1617893.83995069, 4927309.679913, 1617915.1700947, 4927297.58996787, 1617928.12005408, 4927335.75984816, 1617940.09995791, 4927328.25990929, 1617945.94009625, 4927325.55002712, 1617943.32575782, 4927334.82798004, 1617943.28014197, 4927334.99066672, 1617944.51009718, 4927354.62002357, 1617936.85009823, 4927386.20001927, 1617932.40883821, 4927415.70410291, 1617932.61397489, 4927415.84461214, 1617938.54007733, 4927412.46993659, 1617944.46009097, 4927419.68995848, 1617951.05009863, 4927434.1299835, 1617957.74998072, 4927474.19009812, 1617887.96998141, 4927460.48009826, 1617866.40997905, 4927456.55009778, 1617857.39992003, 4927454.61993996, 1617887.6001005, 4927375.78008369, 1617887.72009935, 4927395.83989044, 1617905.43989081, 4927394.20990962, 1617903.38991087, 4927372.97011743, 1617887.6001005, 4927375.78008369)));

COMMIT;

CREATE OR REPLACE FUNCTION DUMMY2(
  P_NUM IN NUMBER
)
RETURN NUMBER
AS
 I INTEGER;
BEGIN
  I := DUMMY2_SEQ.NEXTVAL;
  RETURN P_NUM;
END;
/

CREATE OR REPLACE FUNCTION DUMMY(
  P_SHAPE IN SDO_GEOMETRY
)
RETURN SDO_GEOMETRY
AS
 I INTEGER;
BEGIN
  DBMS_SESSION.SLEEP(1);
  I := DUMMY_SEQ.NEXTVAL;
  RETURN P_SHAPE;
END;
/

SET TIMING ON
UPDATE TEST_GEOM
   SET SHAPE = DUMMY(SHAPE),
       NUM   = DUMMY2(10)
 WHERE ID = 1;

COMMIT;

SET TIMING OFF

SELECT DUMMY_SEQ.CURRVAL FROM DUAL;
SELECT DUMMY2_SEQ.CURRVAL FROM DUAL;

DROP SEQUENCE DUMMY_SEQ;
DROP SEQUENCE DUMMY2_SEQ;
DROP TABLE TEST_GEOM PURGE;