Oracle optimizer ignores Index hint in PLSQL

101
1
09-29-2020 12:37 AM
HiteshJilka
New Contributor III

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!

0 Kudos
1 Reply
George_Thompson
Esri Frequent Contributor

Geodatabase

--- George T.
0 Kudos