Oracle Spatial DBTUNE storage parameters
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 here.
Creating new business tables
ArcSDE uses the B_STORAGE, B_INDEX_ROWID, and B_INDEX_USER storage parameters to store the business table and 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 his or her 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 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
If the configuration keyword specified during feature class creation contains the SDO_SRID parameter set to a valid coordinate reference system, the SDO_SRID value is used and written to the USER_SDO_GEOM_METADATA view even if a different coordinate reference system is provided by the client.
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 determines the name, upper and lower bound (extent), and tolerance of each dimension.
Note:Oracle Spatial also allows feature geometries of x/y/m/z; however, do not use these with ArcSDE.
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 following 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 are created in the user's default tablespace using default physical storage parameters, unless specified otherwise in the DEFAULTS parameter group. The spatial index is 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 WGS84), 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 the tablespace ORSPBIZ. The R-tree spatial index will be created in the tablespace ORSPIDX. The ArcSDE client that is loading the data decides 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 ignored for R-tree spatial indexes. It indicates the number of business table records that are processed before committing the index data. Without it, all the records of the business table are processed before committing the index data. This causes 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 when you create topologies, networks, or terrains, the default composite keywords for these are used. Since the default composite keywords don't specify GEOMETRY_STORAGE, the DEFAULTS GEOMETRY_STORAGE will be used; in this case, that is 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