SQL query optimization tips

Bud — Wed, 25 May 2022 15:51:54 GMT

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.

topic SQL query optimization tips in ArcGIS Enterprise Questions

SQL query optimization tips