Forum Stats

  • 3,852,085 Users
  • 2,264,064 Discussions


Scalar Subquery Caching

User_1871 Member Posts: 244 Red Ribbon

Does anyone have an example of using Scalar Subquery Caching with SDO_GEOMETRY to improve query/function performance?

Scalar Subquery Caching

Rewriting the function call into a scalar subquery allows Oracle to use scalar subquery caching to optimize the number of function calls needed to complete the query. Oracle builds an in-memory hash table to cache the results of scalar subqueries.

SELECT (SELECT slow_function(id) FROM dual) FROM  func_test;

I've been reading about Scalar Subquery Caching, but I'm not sure how it might apply to spatial queries.



  • Rick Anderson-Oracle
    Rick Anderson-Oracle Member Posts: 154 Employee


    I have never used the above construct - when defining functions, it is usually best to make sure they return deterministic results and mark them DETERMINISTIC appropriately (also PARALLEL_ENABLE where appropriate). This usually solves most performance problems.

    You can also use the /*+ RESULT_CACHE */ query hint, but be careful with this.

  • Rick Anderson-Oracle
    Rick Anderson-Oracle Member Posts: 154 Employee

    One thing I have found is that using a function that takes a geometry as a parameter should be declared DETERMINISTIC (and possibly PARALLEL_ENABLE). This allows the query optimizer to cache the function results for a given geometry. This is useful for subquery caching.

    There is a "best practices" slide presentation that provides lots of excellent query examples.