Disassociate the st_domain_operators from the st_domain_stats type in Oracle

Summary

The following instructions provide the Oracle command to disassociate the st_domain_operators package from the st_domain_stats statistics type. The st_domain_operators package contains the st_envintersects function, which is bound to the spatial index used with the st_geometry attribute.

The st_domain_stats statistics type is used by the Oracle optimizer to derive an iocost and selectivity for the st_envintersects function with its predicate value (the envelope of the query). The values returned by the st_domain_stats type are then compared with the costs of performing a full table scan. If the cost is lower than the cost of a full table scan, the optimizer will choose to use the spatial index as the access path.

By disassociating the package with the statistics type, the Oracle optimizer will always cost the st_envintersects function with an iocost of 2 and a selectivity of 10%. With such a low iocost, the optimizer chooses to use the spatial index.

Disassociate the package with the type if issues are encountered in Oracle where a SQL statement with the st_envintersects function is resulting in a full table scan and not leveraging the spatial index. This may occur when a layer is first queried/displayed in ArcGIS at full extent (the Oracle execution plan in the shared pool will use a full table scan as the access path) and then when zooming into a small area performance is impacted by the full table scan verses the use of the spatial index.

Procedure

Code:
SQL> DISASSOCIATE STATISTICS FROM PACKAGES st_domain_operators;

Statistics disassociated.

Code:
SQL> ASSOCIATE STATISTICS WITH PACKAGES st_domain_operators USING st_domain_stats;

Statistics associated.