| Oracle® Spatial Developer's Guide 11g Release 1 (11.1) Part Number B28400-01 |
|
|
View PDF |
| Oracle® Spatial Developer's Guide 11g Release 1 (11.1) Part Number B28400-01 |
|
|
View PDF |
This chapter contains descriptions of the tuning subprograms shown in Table 31-1.
Table 31-1 Tuning Subprograms
| Subprogram | Description |
|---|---|
|
|
Calculates the average minimum bounding rectangle for geometries in a layer. |
|
SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE |
Estimates the maximum number of megabytes needed for an R-tree spatial index table. |
|
|
Returns the minimum bounding rectangle of the data in a layer. |
|
|
Calculates geometry type information for a spatial layer, such as the percentage of each geometry type. |
|
|
Returns the quality degradation for an index or the average quality degradation for all index tables for an index, or returns the quality degradation for a specified index table. |
Format
SDO_TUNE.AVERAGE_MBR(
table_name IN VARCHAR2,
column_name IN VARCHAR2,
width OUT NUMBER,
height OUT NUMBER);
Description
Calculates the average minimum bounding rectangle (MBR) for geometries in a layer.
Parameters
Spatial geometry table.
Geometry column for which the average minimum bounding rectangle is to be computed.
Width of the average minimum bounding rectangle.
Height of the average minimum bounding rectangle.
Usage Notes
This procedure computes and stores the width and height of the average minimum bounding rectangle for all geometries in a spatial geometry table. It calculates the average MBR by keeping track of the maximum and minimum X and Y values for all geometries in a spatial geometry table.
Examples
The following example calculates the minimum bounding rectangle for the SHAPE column of the COLA_MARKETS table.
DECLARE
table_name VARCHAR2(32) := 'COLA_MARKETS';
column_name VARCHAR2(32) := 'SHAPE';
width NUMBER;
height NUMBER;
BEGIN
SDO_TUNE.AVERAGE_MBR(
table_name,
column_name,
width,
height);
DBMS_OUTPUT.PUT_LINE('Width = ' || width);
DBMS_OUTPUT.PUT_LINE('Height = ' || height);
END;
/
Width = 3.5
Height = 4.5
Related Topics
SDO_AGGR_MBR spatial aggregate function (in Chapter 20)
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
Schema that owns the spatial geometry table.
Spatial geometry table name.
Geometry column name.
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.
Approximate number of geometries in the spatial geometry table.
Database block size (in bytes).
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.
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.
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 Section 2.9.2) 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
Format
SDO_TUNE.EXTENT_OF(
table_name IN VARCHAR2,
column_name IN VARCHAR2
) RETURN SDO_GEOMETRY;
Description
Returns the minimum bounding rectangle (MBR) of all geometries in a layer.
Parameters
Spatial geometry table.
Geometry column for which the minimum bounding rectangle is to be returned.
Usage Notes
The SDO_AGGR_MBR function, documented in Chapter 20, also returns the MBR of geometries. The SDO_TUNE.EXTENT_OF function has better performance than the SDO_AGGR_MBR function if the data is non-geodetic and if a spatial index is defined on the geometry column; however, the SDO_TUNE.EXTENT_OF function is limited to two-dimensional geometries, whereas the SDO_AGGR_MBR function is not. In addition, the SDO_TUNE.EXTENT_OF function computes the extent for all geometries in a table; by contrast, the SDO_AGGR_MBR function can operate on subsets of rows.
The SDO_TUNE.EXTENT_OF function returns NULL if the data is inconsistent.
If a non-geodetic spatial index is used, this function may return an approximate MBR that encloses the largest extent of data stored in the index, even if data was subsequently deleted. This can occur because the function extracts MBR information from a non-geodetic spatial index, if one exists.
Examples
The following example calculates the minimum bounding rectangle for the objects in the SHAPE column of the COLA_MARKETS table.
SELECT SDO_TUNE.EXTENT_OF('COLA_MARKETS', 'SHAPE')
FROM DUAL;
SDO_TUNE.EXTENT_OF('COLA_MARKETS','SHAPE')(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y,
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(1, 1, 10, 11))
Related Topics
SDO_AGGR_MBR aggregate function (in Chapter 20)
SDO_TUNE.AVERAGE_MBR procedure
Format
SDO_TUNE.MIX_INFO(
table_name IN VARCHAR2,
column_name IN VARCHAR2
[, total_geom OUT INTEGER,
point_geom OUT INTEGER,
curve_geom OUT INTEGER,
poly_geom OUT INTEGER,
complex_geom OUT INTEGER] );
Description
Calculates geometry type information for a spatial layer, such as the percentage of each geometry type.
Parameters
Spatial geometry table.
Geometry object column for which the geometry type information is to be calculated.
Total number of geometry objects.
Number of point geometry objects.
Number of curve string geometry objects.
Number of polygon geometry objects.
Number of complex geometry objects.
Usage Notes
This procedure calculates geometry type information for the table. It calculates the total number of geometries, as well as the number of point, curve string, polygon, and complex geometries.
Examples
The following example displays information about the mix of geometry objects in the SHAPE column of the COLA_MARKETS table.
CALL SDO_TUNE.MIX_INFO('COLA_MARKETS', 'SHAPE');
Total number of geometries: 4
Point geometries: 0 (0%)
Curvestring geometries: 0 (0%)
Polygon geometries: 4 (100%)
Complex geometries: 0 (0%)
Format
SDO_TUNE.QUALITY_DEGRADATION(
schemaname IN VARCHAR2,
indexname IN VARCHAR2
) RETURN NUMBER;
or
SDO_TUNE.QUALITY_DEGRADATION(
schemaname IN VARCHAR2,
indexname IN VARCHAR2,
indextable IN VARCHAR2
) RETURN NUMBER;
Description
Returns the quality degradation for an index or the average quality degradation for all index tables for an index, or returns the quality degradation for a specified index table.
Parameters
Name of the schema that contains the index specified in indexname.
Name of the spatial R-tree index.
Name of an index table associated with the spatial R-tree index specified in indexname.
Usage Notes
The quality degradation is a number indicating approximately how much longer it will take to execute the I/O operations of the index portion of any given query with the current index, compared to executing the I/O operations of the index portion of the same query when the index was created or most recently rebuilt. For example, if the I/O operations of the index portion of a typical query will probably take twice as much time as when the index was created or rebuilt, the quality degradation is 2. The exact degradation in overall query time is impossible to predict; however, a substantial quality degradation (2 or 3 or higher) can affect query performance significantly for large databases, such as those with millions of rows.
For local partitioned indexes, you must use the format that includes the indextable parameter. That is, you must compute the quality degradation for each partition in which you are interested.
Index names are available through the xxx_SDO_INDEX_INFO and xxx_SDO_INDEX_METADATA views, which are described in Section 2.9.1.
For more information and guidelines relating to R-tree quality and its possible effect on query performance, see Section 1.7.2.
Examples
The following example returns the quality degradation for the COLA_SPATIAL_IDX index. In this example, the quality has not degraded at all, and therefore the degradation is 1; that is, the I/O operations of the index portion of queries will typically take the same time using the current index as using the original or previous index.
SELECT SDO_TUNE.QUALITY_DEGRADATION('SCOTT', 'COLA_SPATIAL_IDX') FROM DUAL;
SDO_TUNE.QUALITY_DEGRADATION('SCOTT','COLA_SPATIAL_IDX')
--------------------------------------------------------
1
