What are some tips for creating function-based spatial indexes?
[In hindsight, some of these points might have been misguided, such #1 and #2]
For example:
In the query that uses the FBI, make sure you have a IS NOT NULL where clause:
"The optimizer can only use an index (in place of a table) if it knows that the index entries are one-for-one with the table. Because it does not know if the expression may return a NULL (which are NOT stored in the index). It cannot make that direct swap unless you inform the optimizer."
"Hence SELECT ROOT FROM TST_AGG will not have the chance to use the index, but SELECT ROOT FROM TST_AGG WHERE ROOT IS NOT NULL should be able to."
Function Based Index not improving query performance
The function should also handle nulls. For example, if null values get passed into the function (such as a null X or Y), then the function should handle that by returning null — so that the function doesn't error-out. If that happens though, and the function does return nulls, then I think that means the query won't use the index; it'll do a full table scan instead. Unless, as mentioned in #1 above, the query's where clause has a IS NOT NULL .
In the expression in user_sdo_geom_metadata:
Spaces are not allowed: 2.8 Geometry Metadata Views.
Also, specify the name of the owner of the function (for example SCOTT for a custom function, or if not using a custom function, then explicitly specify MDSYS as the owner of the SDO_GEOMETRY constructor, etc.).
It's not necessary to create a custom function for function-based spatial indexes or FBIs. Using an OOTB function like the SDO_GEOMETRY() constructor is fine if we are certain that all rows have X&Ys populated (maybe guaranteed with NOT NULL constraints).
When inserting a record into user_sdo_geom_metadata, don't forget to commit; (a classic rookie mistake).
If you want to see the content of a view that uses a function-based spatial index on a map (using some GIS tool), you will probably also need to define metadata for the view. This is NOT needed for spatial queries, but is a common requirement for GIS tools. Example:
insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) values ( 'CUSTOMERS_VW', 'SHAPE', sdo_dim_array (sdo_dim_element('long', -180.0, 180.0, 0.5), sdo_dim_element('lat', -90.0, 90.0, 0.5)), 4326); commit;
Source: Answer from Albert Godfrind.
Any functions used in the index must be defined as deterministic.
If you change the semantics of a DETERMINISTIC function and recompile it, then you must manually rebuild any dependent function-based indexes and materialized views. Otherwise, they report results for the prior version of the function.
Disadvantages of Function-Based Indexes
As of Oracle 12.2, spatial indexes are now optional. BUT, a spatial index is still required for moderate to large tables for achieving acceptable performance.
Spatial Index No Longer Required
The docs recommend we use SPATIAL_INDEX_V2. Instead of the original SPATIAL_INDEX.
5.1.1 Using System-Managed Spatial Indexes.
A SQL hint might be an option -- to force Oracle to use the FBI:
/*+ INDEX (parcels shape_idx) */
Set a SQL hint to instruct the Oracle optimizer in choosing an execution plan. That article is based on a different spatial type, SDE.ST_GEOMETRY, not Oracle's SDO_GEOMETRY. But even still, the concept of adding a hint might be useful.
What do you think? Any corrections or additions?
Related: Tips - Function-based indexes (non-spatial)