How To: Set Storage and Configuration Parameters When Creating a Spatial Index in Oracle w

Summary

Instructions provided describe how to set storage and configuration parameters when creating an ST_SPATIAL_INDEX index with the PARAMETERS clause of the CREATE INDEX statement.

The following list is an example of parameters used in the CREATE INDEX statement:

- tablespace: Specifies the tablespace in which the index is created. Same as TABLESPACE in the STORAGE clause of a CREATE TABLE statement.

- initial: Represents the initial allocated storage for the index being created. It is the same parameter as INITIAL in the STORAGE clause of a CREATE TABLE statement.

- next: Represents the next extents storage allocation when the index requires additional storage space. It is the same as NEXT in the STORAGE clause of a CREATE TABLE statement.

- minextents: Represents the minimum number of storage extents when the index is created. It is the same as MINEXTENTS in the STORAGE clause of a CREATE TABLE statement.

- maxextents: Represents the maximum number of storage extents the index may consume. It is the same as MAXEXTENTS in the STORAGE clause of a CREATE TABLE statement.

- pctincrease: Represents the percentage increase for the next extent when the index requires additional storage space. It is the same as PCTINCREASE in the STORAGE clause of a CREATE TABLE statement.

- NOPARALLEL | PARALLEL [ integer ]: Controls whether serial (NOPARALLEL) execution or parallel (PARALLEL) execution is used for the creation of the index and for subsequent queries and DML operations that use the index.

For parallel execution, specify an integer value of degree of parallelism. Default = NOPARALLEL. If PARALLEL is specified without an integer value, Oracle calculates the optimum degree of parallelism.

Procedure

The first example demonstrates just setting the index parameters for creating an ST_SPATIAL_INDEX. The second example demonstrates how to set additional storage parameters.

Note:
The index parameters ST_GRIDS and ST_SRID are required when creating an ST_SPATIAL_INDEX. All storage parameters are optional in a CREATE INDEX statement.

Example 1

Code:
SQL> CREATE INDEX cities_sidx ON us_cities (shape)
  2  INDEXTYPE IS sde.st_spatial_index
  3  PARAMETERS
  4  ('st_grids=1,0,0 st_srid=1');

Note:
When setting the values for st_grids, it is not required to set each additional level when the values are 0.

Example 2

Code:
SQL> CREATE INDEX cities_sidx ON us_cities(shape)
  2  INDEXTYPE IS sde.st_spatial_index
  3  PARAMETERS
  4  ('st_grids=1,0,0 st_srid=1
  5  TABLESPACE spatial_idx storage (INITIAL 64K
  6  NEXT 64K MINEXTENTS 1
  7  MAXEXTENTS 256 PCTINCREASE 0)');

Article ID:000009162

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Set storage and configuration parameters when creating a spatial index in Oracle with SQL

Summary

Procedure

Discover more on this topic