Does anyone have any SQL optimization tips they can share? Either for spatial queries or non-spatial queries?
I work with SDE.ST_GEOMETRY and SDO_GEOMETRY in Oracle 18c. But info about optimization in other DBs/datatypes is fine too.
For example, I learned recently that the cost of ST_GEOMETRY functions doesn’t get included in the explain plan cost. I had a query that used a bunch of nested ST_GEOMETRY functions (in the SELECT clause) that was really slow, yet the explain plan cost was surprisingly low. It took me a while to realize that Oracle doesn’t usually include the cost of user-defined functions in the explain plan. Especially not for SDE.ST_GEOMETRY functions, since they’re stored in the EXTPROC.DLL file, not in the Oracle kernel. So Oracle has no way to know how expensive they are.
https://stackoverflow.com/questions/66671812/are-functions-considered-in-execution-plan
Any others come to mind?
Cheers.
