Oracle optimizer ignores Index hint in PLSQL

HiteshJilka — Tue, 29 Sep 2020 07:37:50 GMT

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!

Re: Oracle optimizer ignores Index hint in PLSQL

George_Thompson — Tue, 29 Sep 2020 12:12:40 GMT

Geodatabase‌

topic Re: Oracle optimizer ignores Index hint in PLSQL in ArcGIS Enterprise Questions

Oracle optimizer ignores Index hint in PLSQL

Re: Oracle optimizer ignores Index hint in PLSQL