hello, I have an oracle procedure which uses SDE ST Functions to get desired result.
In SQLPLUS, we can directly refer ST functions without Package name but in PLSQL we need to specify package name or else it errors as explained in "Oracle Error PLS-00707 trying to compile a store procedure using a call to sde.st_intersects. "
In SQLPLUS, When I use "sde.st_intersects" and INDEX hint for Domain Index, it is used by Optimizer
BUT for PLSQL when I specify "sde.st_relation_operators.st_intersects_f " and INDEX hint, it is ignored by optimizer.
below is my statement I am executing in PLSQL procedure.
SELECT /*+ INDEX (a D_IX1) */ *
FROM TABLE a
WHERE
sde.st_relation_operators.st_intersects_f (a.shape,(SELECT sde.st_geometry_operators.st_centroid_f(b.shape)
FROM TABLE b
WHERE condition) = 1;
I would appreciate if anyone can help me with what changes can be done on above statement for INDEX to be used by optimizer in PLSQL.
Thank you!