Optimize spatial queries
You can set a configuration parameter and environment variables in your database server environment to optimize your spatial queries.
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
- 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.
- 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.
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.