SQL query optimization tips

255
0
05-23-2022 10:27 PM
Bud
by
Notable Contributor

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.

0 Kudos
0 Replies