Create oralce spatial indexes

Source: Internet
Author: User
Tags sdo

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)
'Customer', -- table name
'Location', -- geometry Field

8307, -- SRID

Sdo_dim_array -- diminfo attribute range and tolerance of spatial data in a bucket table
'X', -- name of the first metakey
-180, -- Minimum x
180, -- maximum x
0.5 -- tolerance 0.5 meters ),
'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:



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 ')



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 ')


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


Multipolygon -- polygon set

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


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 ');



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 ');

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.