Create oralce spatial indexes

Before creating a spatial index, you must register a spatial table in user_sdo_geom_metadata. The registration space TABLE statement is as follows:

Insert into user_sdo_geom_metadata
(Table_name, column_name, SRID, diminfo)
Values
(
'Customer', -- table name
'Location', -- geometry Field

8307, -- SRID

Sdo_dim_array -- diminfo attribute range and tolerance of spatial data in a bucket table
(
Sdo_dim_element
(
'X', -- name of the first metakey
-180, -- Minimum x
180, -- maximum x
0.5 -- tolerance 0.5 meters ),
Sdo_dim_element
(
'Y', -- the second metakey name
-90, -- Minimum y

90, -- maximum y
0.5 -- tolerance 0.5 meters)
)
);

Before creating a spatial index, note that the SRID in the spatial table must be consistent with the SRID registered in user_sdo_geom_metadata, because when creating a spatial index, oracle automatically checks whether the SRID in the space table is in the user_sdo_geom_metadata

The SRID is consistent. If they are inconsistent, A ORA-13365 error is reported, but Oracle does not cancel the creation of the spatial index because

You need to manually delete the created index and re-create the index after resolving the conflict.

Definition of spatial index creation:
Create index <indexname> On <tablename> (<columnname>)
Indextype is MDSYS. spatial_index
Parameters ('parameter _ string ')

You can do not set parameters, but setting parameters can improve the data of space operations to a certain extent. The parameters string settings are as follows:

 

Tablespace

Define the tablespace stored in the spatial index. Next and initial are variables used to expand tablespace.

Example: parameters ('tablespace = tbs_3 next = 5 K initial = 10k ')

 

Work_tablespace

Defines the bucket of the worksheet used by the spatial index during creation. The entire record set is sorted by sorting the index's R-tree index. Therefore, many worksheets are created during index creation, after the index is created, these worksheets are deleted, which generates many fragments in the tablespace. Because these tables are not temporary tables, Oracle's temporary tablespace cannot be used. By default, the same tablespace as the index is used to prevent this situation, you can set another tablespace for these temporary worksheets created during index creation.

Example: parameters ('work _ tablespace = sysaux ')

Layer_gtype

Defines the set type of field storage for index creation. It helps improve data check and improve the space query speed to a certain extent. For example, if layer_gtype = point is set in the index parameter, the geometry field can only store point-type data. If you try to store line-type geometric data, an error is returned.

Point -- point

Line -- Line

Polygon -- Polygon

Curve -- Curve

Multipoint -- Point Set

Multicurve-Multi-Curve

Multipolygon -- polygon set

Collection -- Compound
Example: parameters ('layer _ gtype = point ');

 
Sdo_indx_dims

Defines the dimension of a spatial index. The default value is two-dimensional. R-TREE can create spatial indexes for three-dimensional or four-dimensional spatial data,
SQL> Create index customers_sidx on MERs (location)
Indextype is MDSYS. spatial_index
Example: parameters ('sdo _ indx_dims = 2 ');

 

Sdo_dml_batch_size

In addition, deletion, modification, and other operations, tables that contain spatial indexes do not directly Delete table data, but are processed in batches in the buffer size specified by sdo_dml_batch_size, this deletion specifies the buffer size for adding, deleting, modifying, and other transaction operations. The default value is 1000. If the volume of data for adding, deleting, modifying, and other transaction operations is large, you can set sdo_dml_batch_size to 5000 or 10000, but note that when sdo_dml_batch_size is set to large, the usage of memory and other system resources will also increase. Therefore, it depends on the actual situation.

Example: parameters ('sdo _ dml_batch_size = 100 ');

Sdo_level
Oracle default index is R-TREE index, by defining sdo_level in index parameters you can create a four-tree index, Oracle

Currently, only two-dimensional spatial data is supported.
Example: parameters ('sdo _ Level = 8 ');

 

I have a superficial understanding of this. please correct me if you have any shortcomings.