SDO_TUNE.ESTIMATE_RTREE_INDEX

Format

SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE(
     schemaname IN VARCHAR2, 
     tabname    IN VARCHAR2, 
     colname    IN VARCHAR2, 
     partname   IN VARCHAR2 DEFAULT NULL 
     ) RETURN NUMBER;

or

SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE(
     number_of_geoms IN INTEGER, 
     db_block_size   IN INTEGER, 
     sdo_rtr_pctfree IN INTEGER DEFAULT 10, 
     num_dimensions  IN INTEGER DEFAULT 2, 
     is_geodetic     IN INTEGER DEFAULT 0 
     ) RETURN NUMBER;

Description

Estimates the maximum number of megabytes needed for an R-tree spatial index table.

Parameters

schemaname: Schema that owns the spatial geometry table.
tabname: Spatial geometry table name.
colname: Geometry column name.
partname: Name of a partition containing geometries from colname. If you specify this parameter, the value returned by the function is the estimated size for an R-tree index table on geometries in that partition. If you do not specify this parameter, the value is the estimated size for an R-tree index table on all geometries in colname.
number_of_geoms: Approximate number of geometries in the spatial geometry table.
db_block_size: Database block size (in bytes).
sdo_rtr_pctfree: Minimum percentage of slots in each index tree node to be left empty when the index is created. Slots that are left empty can be filled later when new data is inserted into the table. The value can range from 0 to 50. The default value (10) is best for most applications; however, a value of 0 is recommended if no updates will be performed to the geometry column.
num_dimensions: Number of dimensions to be indexed. The default value is 2. If you plan to specify the sdo_indx_dims parameter in the CREATE INDEX statement, the num_dimensions value should match the sdo_indx_dims value.
is_geodetic: A value indicating whether or not the spatial index will be a geodetic index: 1 for a geodetic index, or 0 (the default) for a non-geodetic index.

Usage Notes

The function returns the estimated maximum number of megabytes needed for the spatial index table (described in Spatial Index Table Definition) for an R-tree spatial index to be created. The value returned is the maximum number of megabytes needed after index creation. During index creation, approximately three times this value of megabytes will be needed in the tablespace, to ensure that there is enough space for temporary tables while the index is being created.

This function has two formats:

Use the format with character string parameters (schemaname, tabname, colname, and optionally partname) in most cases when the spatial geometry table already exists, you do not plan to add substantially more geometries to it before creating the index, and you plan to use the default R-tree indexing parameters.
Use the format with integer parameters (number_of_geoms, db_block_size, sdo_rtr_pctfree, num_dimensions, is_geodetic) in any of the following cases: the spatial geometry table does not exist; the spatial geometry table exists but you plan to add substantially more geometries to it before creating the index; or the num_dimensions value is not 2 for non-geodetic data or 3 for geodetic data, and a nondefault value will be specified using the sdo_indx_dims parameter in the CREATE INDEX statement.

Examples

The following example estimates the maximum number of megabytes needed for a spatial index table for an index given the following information: number_of_geoms = 1000000 (one million), db_block_size = 2048, sdo_rtr_pctfree = 10, num_dimensions = 2, is_geodetic = 0.

SELECT SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE(1000000, 2048, 10, 2, 0) FROM DUAL;

SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE(1000000,2048,10,2,0)
-----------------------------------------------------
                                                   82

The following example estimates the maximum number of megabytes needed for a spatial index table for an index on the SHAPE column in the COLA_MARKETS table in the SCOTT schema. The estimate is based on the geometries that are currently in the table.

SELECT SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE('SCOTT', 'COLA_MARKETS', 'SHAPE') FROM DUAL;

SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE('SCOTT','COLA_MARKETS','SHAPE')
---------------------------------------------------------------------
                                                                    1