Optimize spatial queries

You can set a configuration parameter and environment variables in your database server environment to optimize your spatial queries.

You can add the spatial extension shared library, spatial.bld, to a PRELOAD_DLL_FILE configuration parameter setting to preload the spatial shared library when the server starts instead of loading the library the first time you run a spatial function. For example, add the following line to your onconfig file:
PRELOAD_DLL_FILE $ONEDB_HOME/extend/spatial.version/spatial.bld
The version is the specific version number for the extension. Run the SE_Release() function to find the correct version number. The version number of the spatial extension can change in any fix pack or release. After you upgrade, you must update the value of the PRELOAD_DLL_FILE configuration parameter if the version number of the spatial extension changed.

You can set the following spatial environment variables in your database server environment before you start the database server.

ST_MAXLEVELS environment variable

Running the UPDATE STATISTICS HIGH statement on a large table might require large amounts of shared memory (tens of MB). If sufficient shared memory is unavailable, the UPDATE STATISTICS statement fails. You can set the ST_MAXLEVELS environment variable to reduce the memory requirements for updating statistics on spatial tables. Spatial tables have histograms of the spatial data to determine the cost of retrieving the data. The histogram describes how the spatial data is distributed.

The range of values for the ST_MAXLEVELS environment variable is 1 - 16. The default value is 16. A smaller value reduces the amount of memory that is needed to build a histogram, but might result in a less accurate histogram. The minimum recommended value is 12.

ST_COSTMULTIPLER environment variable

To adjust the cost for each row that is computed by the database server, set the ST_COSTMULTIPLER environment variable to a floating point value that is greater than 0. The default value is 1.0 (no effect). The database server multiplies the cost estimate by the value of the ST_COSTMULTIPLER environment variable to compute the cost of spatial predicates that include the following spatial functions:

  • ST_Overlaps()
  • Equal()
  • ST_Contains()
  • ST_Within()
  • SE_EnvelopeIntersect()
  • SE_Intersects()
  • ST_Touches()
  • ST_Crosses()
  • ST_Equals()
  • ST_Disjoint()

To increase the cost of a spatial predicate, set the value of the ST_COSTMULTIPLER environment variable to greater than 1.0. To decrease the cost of a spatial predicate, set the value of the ST_COSTMULTIPLER environment variable to less than 1.0.

The cost can be used to compute the cost of a full table scan or the cost of the refinement step in an index scan.

ST_MEMMODE environment variable

To improve performance, the database server uses a pool of temporary memory buffers for processing spatial data. You can change the behavior of this memory management system, if necessary, in two ways:
  • Set the ST_MEMMODE environment variable.
  • Set the value of the MemMode parameter while the database server is running by running the SE_ParamSet() function.
The ST_MEMMODE environment variable and the MemMode parameter can have the following values:
0
Disables memory buffer reuse. Temporary buffers, which are used for processing spatial data, are allocated from the per_routine memory pool and are not reused between UDR invocations. Several memory buffers are typically allocated and freed for every row in a table that is being processed. This setting can result in slower query performance.
1
Default. Enables memory buffer reuse. Temporary buffers are allocated from the per_command memory pool. As they are freed, they are returned to a pool and are reused for subsequent memory requests. This pool is drained when the UDR sequence completes after all rows in a table are processed. This setting can result in memory fragmentation.
2
Disables memory buffer reuse, but allocates all temporary buffers from the server per_command memory pool. This mode, with the DONTDRAINPOOLS server environment variable, is similar to mode 1, but allows the server to manage the memory.
Buffers that hold UDR return values are allocated from the per_command memory pool and are reused between UDR invocations.

The value of the MemMode parameter takes precedence over the value of the ST_MEMMODE environment variable. The MemMode parameter remains set until the server is shut down. When the server is restarted, the value of the ST_MEMMODE environment variable takes effect.

To view the value of the MemMode parameter, run the SE_ParamGet() function.