Show Navigation | Hide Navigation

About Oracle Spatial DBTUNE storage parameters
Release 9.3 Last modified December 3, 2010

Related Topics

Note: This topic was updated for 9.3.1.

NOTE: Geodatabases in an Oracle Spatial database management system (DBMS) created with an ArcGIS Server Enterprise license only

Because storage parameters for Oracle Spatial are different than other Oracle databases used with ArcSDE, descriptions and examples of how the storage parameters are used with Oracle Spatial are given below.

Creating new business tables

ArcSDE uses the B_STORAGE, B_INDEX_ROWID, and B_INDEX_USER storage parameters to store the business table and the nonspatial indexes on the business table.

Creating Oracle Spatial metadata for new feature classes

The database view USER_SDO_GEOM_METADATA is part of Oracle Spatial, not ArcSDE. It contains metadata about SDO_GEOMETRY columns in existing tables owned by the user. Each user has its own USER_SDO_GEOM_METADATA view. To be indexed and queried, the owner of the table must record metadata for each SDO_GEOMETRY column in USER_SDO_GEOM_METADATA. The ArcSDE clients that create a feature class will choose the metadata for the feature class. Often, these clients accept a configuration keyword corresponding to a parameter group in the DBTUNE table.

The storage parameters that control the metadata for new Oracle Spatial feature classes are as follows:

SDO_DIMNAME_<n>
SDO_LB_<n>
SDO_UB_<n>
SDO_TOLERANCE_<n>	
SDO_SRID

NOTE: If a coordinate reference system is provided during the creation of a feature class, the SDO_SRID parameter is ignored and not written to the USER_SDO_GEOM_METADATA view.

Oracle Spatial permits feature geometries of two, three, or four dimensions in the combinations x/y, x/y/z, x/y/m (measure), or x/y/z/m. Through these storage parameters, ArcSDE allows you to specify metadata for each dimension. The <n> in some parameter names should be replaced by one of the digits 1, 2, 3, or 4, corresponding to the dimension number. If you do not supply these storage parameters, the ArcSDE client application that creates the feature class will determine the name, upper and lower bound (extent), and tolerance of each dimension.

Creating a spatial index

The DBTUNE parameter SDO_INDEX_SHAPE determines how Oracle Spatial creates the spatial index. ArcSDE appends the contents of this parameter (the configuration string) to the CREATE INDEX statement before submitting the statement to Oracle. The configuration string is inserted into the SQL statement after the PARAMETERS keyword. For example:

CREATE INDEX MY_SP_INDEX ON MY_SP_TABLE(SHAPE)
INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ( <configuration string is inserted here> );

The configuration string is a quoted string containing a list of parameter = value elements. There are many parameters that you can specify in the configuration string. To understand the Oracle Spatial index parameters and how they interact, read the applicable sections of the Oracle Spatial User's Guide and Reference.

Notice the differences between the physical storage parameters in the spatial index configuration string and in a business table configuration string (as specified in B_STORAGE). One difference is due to the way Oracle expects these parameters to appear in SQL statements. The Oracle statements are formatted differently, so the configuration strings are formatted differently. Also, not every physical storage parameter used for creating tables is available for creating spatial indexes.

B_STORAGE	"TABLESPACE ORSPBIZ PCTFREE 10 INITRANS 4 STORAGE(INITIAL 512000)"
SDO_INDEX_SHAPE	"tablespace=ORSPIDX initial=512000"

Sample DBTUNE parameter groups for Oracle Spatial feature classes

This section presents DBTUNE parameter groups that apply to several common scenarios. (Remember, parameters are grouped by configuration keywords.) These samples emphasize the storage parameters for Oracle Spatial feature classes.

Oracle and ESRI recommend using R-tree spatial indexes with SDO_GEOMETRY storage. In some of the next set of examples, you will see the parameter sdo_indx_dims=2, which specifies how many dimensions should be indexed with an R-tree spatial index. With Oracle 9.2, the default value is 2, meaning the first two dimensions (x and y). For previous versions of Oracle, the default value was the number of dimensions recorded in USER_SDO_GEOM_METADATA. There were various problems creating R-tree spatial indexes on dimensions other than x and y. For example, Oracle does not support R-tree indexes on the measure dimension. If you are creating R-tree spatial indexes and are using a version of Oracle prior to 9.2, it is recommended that you always include this parameter.

If you are not using Oracle Spatial by default, you can create a simple parameter group to create Oracle Spatial feature classes with mostly default settings. The tables and indexes will be created in the user's default tablespace using default physical storage parameters, unless specified otherwise in the DEFAULTS parameter group. The spatial index will be a two-dimensional R-tree.

##SDO_GEOMETRY
GEOMETRY_STORAGE		"SDO_GEOMETRY"
SDO_INDEX_SHAPE	"sdo_indx_dims=2"
UI_TEXT	"Oracle Spatial: default settings"
END

With Oracle Spatial, if data is often loaded using a specific spatial reference identifier (SRID), such as the geodetic SRID 8307 (latitude/longitude WGS 84), you can create an expanded version of the previous parameter group. You don't have to specify the upper and lower bounds and tolerance, but you can if you want all your feature classes to have the same metadata for the X and Y dimensions. This is useful if you want to use the feature classes in the same feature dataset. Also, this case specifies that any polygon boundaries with reversed rotation will be reordered before sending them to ArcSDE clients.

NOTE: With Oracle9i geodetic data, the extents are specified in decimal degrees and the tolerances are specified in meters.

##SDO_GEOMETRY_8307
GEOMETRY_STORAGE		"SDO_GEOMETRY"
SDO_INDEX_SHAPE	"sdo_indx_dims=2"
SDO_SRID	8307
SDO_DIMNAME_1	"Lon"
SDO_LB_1	-180.000000
SDO_UB_1	180.000000
SDO_TOLERANCE_1	0.05
SDO_DIMNAME_2	"Lat"
SDO_LB_2	-90.000000
SDO_UB_2	90.000000
SDO_TOLERANCE_2	0.05
UI_TEXT	"Oracle Spatial: WGS84"
END

The following example can be used to load a feature class with an R-tree spatial index into a tablespace called ORSPBIZ. The R-tree spatial index will be created in the tablespace ORSPIDX. The ArcSDE client that is loading the data will decide the values for the metadata.

##SDO_GEOMETRY_ORSPBIZ
GEOMETRY_STORAGE		"SDO_GEOMETRY"
B_STORAGE	"TABLESPACE ORSPBIZ"
SDO_INDEX_SHAPE	"tablespace=ORSPIDX sdo_indx_dims=2"
UI_TEXT	"Tablespace ORSPBIZ / ORSPIDX"
END

The following example can be used to load a feature class with a quadtree spatial index having a fixed-size tiling level (tessellation level) of 6. The spatial index will be created in the tablespace ORSPIDX.

Commit interval is important for quadtree indexes but is ignored for R-tree spatial indexes. It indicates the number of business table records that will be processed before committing the index data. Without it, all the records of the business table are processed before committing the index data. This will cause problems when indexing tables with many records.

NOTE: The parameter sdo_commit_interval is so important that ArcSDE automatically includes it in SQL indexing statements for Oracle Spatial tables, even if you do not specify it as part of the SDO_INDEX_SHAPE parameter. It is set to 1,000.

##SDO_GEOMETRY_QT_6
GEOMETRY_STORAGE		"SDO_GEOMETRY"
SDO_INDEX_SHAPE	"tablespace=ORSPIDX sdo_level=6 sdo_commit_interval=1000"
END

When designing your own parameter groups, you may need to add parameters to support other geodatabase constructs such as geometric networks or terrains. You could also satisfy these requirements by setting parameters in the DEFAULTS parameter group.

For example, if the GEOMETRY_STORAGE parameter of the DEFAULTS keyword is set to SDO_GEOMETRY, then when you create topologies, networks, or terrains, the default composite keywords for these will be used. Since the default composite keywords don't specify GEOMETRY_STORAGE, the DEFAULTS GEOMETRY_STORAGE will be used; in this case, that would be SDO_GEOMETRY.

If instead your DEFAULTS GEOMETRY_STORAGE keyword is set to something other than SDO_GEOMETRY, but you want to create, for example, a terrain that uses SDO_GEOMETRY storage, you need to create a new set of terrain keywords designed to store terrains with SDO_GEOMETRY storage. The following is an example of this as it would look in the dbtune.sde file:

##TERRAIN_SDO
UI_TERRAIN_TEXT    "The terrain default configuration"
GEOMETRY_STORAGE  "SDO_GEOMETRY"
B_STORAGE           "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc"

B_INDEX_ROWID       "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
B_INDEX_SHAPE       "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
B_INDEX_USER        "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
END
##TERRAIN_SDO::EMBEDDED
GEOMETRY_STORAGE  "SDO_GEOMETRY"
B_STORAGE           "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc"
B_INDEX_ROWID       "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
B_INDEX_SHAPE       "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
B_INDEX_USER        "PCTFREE 0 INITRANS 4 TABLESPACE ter_tblspc NOLOGGING"
END

About Oracle Spatial DBTUNE storage parameters