Community

ArcGIS should use the new SDO_GEOMETRY spatial index type: SPATIAL_INDEX_V2

596
3
06-16-2022 09:07 AM
Status: Open
Bud
by
Notable Contributor

ArcGIS should use the new SDO_GEOMETRY spatial index type: SPATIAL_INDEX_V2.

5.1.1Using System-Managed Spatial Indexes

Effective with Release 12.2, spatial indexes can be system-managed by specifying INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 at index creation. You are strongly encouraged to use this index type for all new spatial indexes you create, regardless of whether the spatial table or the spatial index is partitioned, and you may also want to use it if you decide to re-create legacy spatial indexes.

Currently, if I create a new SDO_GEOMETRY feature class via Catalog in ArcGIS Pro 2.6.8, it uses the old index type:

CREATE INDEX "INFRASTR"."A7378_IX1" ON "INFRASTR"."PRO_SDO_FC" ("SHAPE") 
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS = 0 ST_SRID = 26917 ST_COMMIT_ROWS = 10000 PCTFREE 10 PCTUSED 90 INITRANS 4 STORAGE (FREELISTS 4 MINEXTENTS 1 PCTINCREASE 0)');
3 Comments
MichaelVolz
by
‎07-07-2022 05:15 AM

Do you know what the benefits are of using this newer spatial index?  Is there a noticeable performance gain when updating from old spatial index to new spatial index?

Bud
by
‎07-07-2022 05:29 AM

@MichaelVolz I don't have enough experience with the new index type to say one way or the other. I submitted the idea only because that's what Oracle recommends.

My uneducated guess is that there wouldn't be a performance gain in most scenarios. If there was, I think Oracle would have mentioned it in the docs. But they actual hint at the opposite, at least when it comes to creating the index. I'm not sure about performance when using the index.

5.1.1 Using System-Managed Spatial Indexes


Effective with Release 12.2, spatial indexes can be system-managed by specifying INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 at index creation. You are strongly encouraged to use this index type for all new spatial indexes you create, regardless of whether the spatial table or the spatial index is partitioned, and you may also want to use it if you decide to re-create legacy spatial indexes.

The main benefit is simplified spatial index management. This is most beneficial in cases of partitioning, because this new index type eliminates the need for most, if not all, index partitioning management operations. Full support is provided for almost all Oracle Database base table partitioning models, including:

Single-level partitioning: range, hash, list

Composite partitioning: range-range, range-hash, range-list, list-range, list-hash, list-list, hash-hash, hash-list, hash-range

Partitioning extensions: interval (but not interval-based composite partitions), reference, virtual column-based partitioning

The old INDEXTYPE=MDSYS.SPATIAL_INDEX (without the “_V2”) is still available for use, It may provide slightly better index creation performance, especially with small data sets and no partitioning involved. You might also want to use the old type if you need to drop a legacy spatial index and then want to re-create it in exactly the same form as it was before. However, in all or almost all cases you will want to specify INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 when creating any spatial index.

MichaelVolz
by
‎07-07-2022 05:37 AM

Bud:

If you do get to use the V2 index with your Oracle GIS data, please share your experience on the pros and cons of this new index as it would be great to hear from a real person if/how the new index improves their GIS environment.