Release 9.2
Part No. A96630-01
 Library |
 Product |
 Contents |
 Master Index |
 Index |
 Feedback |
| Oracle® Spatial User's Guide and Reference Release 9.2 Part No. A96630-01 |
|
This chapter contains descriptions of the tuning functions and procedures shown in Table 16-1.
Table 16-1 Tuning Functions and Procedures
| Function/Procedure | Description |
|---|---|
| SDO_TUNE.ANALYZE_RTREE | Analyzes an R-tree index; generates statistics about the index use, and recommends a rebuild of the index if a rebuild would improve query performance significantly. |
| SDO_TUNE.AVERAGE_MBR | Calculates the average minimum bounding rectangle for geometries in a layer. |
| SDO_TUNE.ESTIMATE_INDEX_PERFORMANCE | Estimates the spatial index selectivity. |
| SDO_TUNE.ESTIMATE_TILING_LEVEL | Determines an appropriate tiling level for creating fixed-size index tiles. |
| SDO_TUNE.ESTIMATE_TILING_TIME | Estimates the tiling time for a layer, in seconds. |
| SDO_TUNE.ESTIMATE_TOTAL_NUMTILES | Estimates the total number of spatial tiles for a layer. |
| SDO_TUNE.EXTENT_OF | Determines the minimum bounding rectangle of the data in a layer. |
| SDO_TUNE.HISTOGRAM_ANALYSIS | Calculates statistical histograms for a spatial layer. |
| SDO_TUNE.MIX_INFO | Calculates geometry type information for a spatial layer, such as the percentage of each geometry type. |
| SDO_TUNE.QUALITY_DEGRADATION | Returns the quality degradation for an R-tree index or the average quality degradation for all index tables for an R-tree index. |
| SDO_TUNE.RTREE_QUALITY | Returns the quality score for an R-tree index or the average quality score for all index tables for an R-tree index. |
Format
SDO_TUNE.ANALYZE_RTREE(
schemaname IN VARCHAR2,
indexname IN VARCHAR2);
Description
Analyzes an R-tree index; generates statistics about the index, and recommends a rebuild of the index if a rebuild would improve query performance significantly.
Parameters
Name of the schema that contains the index specified in indexname.
Name of the Spatial R-tree index to be analyzed.
Usage Notes
The procedure computes an index quality score and compares it to the quality score when the index was created or most recently rebuilt (stored as SDO_RTREE_QUALITY in the xxx_INDEX_METADATA views, described in Section 2.5.1). If the comparison of the index quality scores shows that quality has degraded by 50% or more, the procedure recommends that the index be rebuilt.
For R-tree indexes with secondary indexes (created using the ALTER INDEX statement with the add_index keyword), the statistics for each index table are printed.
This procedure should be used only in a PL/SQL procedure or from the SQL*Plus command line; it should not be used in an OCI program or with any other programming interface.
Because this procedure only prints the output to standard output, the SDO_TUNE.RTREE_QUALITY and SDO_TUNE.QUALITY_DEGRADATION functions are also provided, to return the quality score and quality degradation, respectively, for an R-tree index.
For more information about R-tree quality and its effect on query performance, see Section 1.7.1.1.
Examples
The following example analyzes the quality of the COLA_SPATIAL_IDX index.
EXECUTE SDO_TUNE.ANALYZE_RTREE('SCOTT', 'COLA_SPATIAL_IDX');
The display to standard output might be as follows:
--- Quality Statistics for Index table: MDRT_11A5$ --- Current Perf. Index : 1.000000 Previous Perf. Index: 1.000000 Index Quality: Good, No Rebuild Necessary
Related Topics
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 a geometry object column.
Parameters
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 geometry table. It calculates the average MBR by keeping track of the maximum and minimum X and Y values for all geometries in a geometry table.
AVERAGE_MBR is a procedure, not a function. (Procedures do not return values.)
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
Format
SDO_TUNE.ESTIMATE_INDEX_PERFORMANCE(
table_name IN VARCHAR2,
column_name IN VARCHAR2,
sample_ratio IN INTEGER,
tiling_level IN INTEGER,
num_tiles IN INTEGER,
window_obj IN MDSYS.SDO_GEOMETRY,
tiling_time OUT NUMBER,
filter_time OUT NUMBER,
query_time OUT NUMBER
) RETURN NUMBER;
Description
Estimates the spatial index performance such as query selectivity and window query time for a column of type SDO_GEOMETRY.
Parameters
Geometry table.
Geometry column for which the tiling time is to be estimated.
Approximate ratio between the geometries in the original layer and those in the sample layer (to be generated in order to perform the estimate). The default is 20: that is, the sample layer will contain approximately 1/20 (5 percent) of the geometries in the original layer. The larger the sample_ratio value, the faster the function will run, but the less accurate will be the result (the estimate).
Note that Spatial obtains the sample by using the SAMPLE(sample_percent) feature internally. For a description of this feature, see the sample_clause description in the SELECT statement section of the Oracle9i SQL Reference.
Spatial index level at which the layer is to be tessellated.
Number of tiles for variable or hybrid tessellation. Should be 0 for fixed tessellation. The default is 0.
Window geometry object.
Estimated tiling time in seconds.
Estimated spatial index filter time in seconds.
Estimated window query time in seconds.
Usage Notes
The function returns a number between 0.0 and 1.0 representing estimated spatial index selectivity. The larger the number, the better the selectivity.
The sample_ratio parameter lets you control the trade-off between speed and accuracy. Note that sample_ratio is not exact, but reflects an average. For example, a sample_ratio value of 20 sometimes causes fewer than 5 percent of geometry objects to be sampled and sometimes more than 5 percent, but over time an average of 5 percent will be sampled.
A return value of 0.0 indicates an error.
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';
sample_ratio INTEGER := 15;
tiling_level INTEGER := 4;
num_tiles INTEGER := 10;
window_obj MDSYS.SDO_GEOMETRY :=
MDSYS.SDO_GEOMETRY(
2003, -- 2-dimensional polygon
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon
MDSYS.SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3)
);
tiling_time NUMBER;
filter_time NUMBER;
query_time NUMBER;
ret_number NUMBER;
BEGIN
ret_number := SDO_TUNE.ESTIMATE_INDEX_PERFORMANCE(
table_name,
column_name,
sample_ratio,
tiling_level,
num_tiles,
window_obj,
tiling_time,
filter_time,
query_time
);
END;
/
Format
SDO_TUNE.ESTIMATE_TILING_LEVEL(
table_name IN VARCHAR2,
column_name IN VARCHAR2,
num_tiles IN INTEGER
[, type_of_estimate IN VARCHAR2]
) RETURN INTEGER;
Description
Estimates the appropriate SDO_LEVEL value to use when indexing with hybrid or fixed-size tiles.
Parameters
Geometry table.
Geometry column for which the tiling level is to be estimated.
Maximum number of tiles that can be used to index the rectangle defined by type_of_estimate.
Keyword to specify the type of estimate:
LAYER_EXTENT -- Uses the rectangle defined by your coordinate system.
ALL_GID_EXTENT -- Uses the minimum bounding rectangle that encompasses all the geometric objects in the column. This estimate is recommended for most applications.
AVG_GID_EXTENT (default) -- Uses a rectangle representing the average size of the individual geometric objects within the column. This option is the default and performs the most analysis of the three types, but it takes the longest time to complete.
Usage Notes
The function returns an integer representing the level to use when creating a spatial index for the specified layer. The function returns NULL if the data is inconsistent.
If type_of_estimate is ALL_GID_EXTENT, a maxtiles value of 10000 is recommended for most applications.
Examples
The following example estimates the appropriate SDO_LEVEL value to use with the SHAPE column of the COLA_MARKETS table.
SELECT SDO_TUNE.ESTIMATE_TILING_LEVEL('COLA_MARKETS', 'SHAPE',
10000, 'ALL_GID_EXTENT')
FROM DUAL;
SDO_TUNE.ESTIMATE_TILING_LEVEL('COLA_MARKETS','SHAPE',10000,'ALL_GID_EXTENT')
-----------------------------------------------------------------------------
7
Related Topics
Format
SDO_TUNE.ESTIMATE_TILING_TIME(
table_name IN VARCHAR2,
column_name IN VARCHAR2,
sample_ratio IN INTEGER,
tiling_level IN INTEGER,
num_tiles IN INTEGER
) RETURN NUMBER;
Description
Returns the estimated time (in seconds) to tessellate a column of type SDO_GEOMETRY.
Parameters
Geometry table.
Geometry column for which the tiling time is to be estimated.
Approximate ratio between the geometries in the original layer and those in the sample layer (to be generated to perform the estimate). The default is 20: that is, the sample layer will contain approximately 1/20 (5 percent) of the geometries in the original layer. As you increase the sample_ratio value, the execution time for the function decreases, but the accuracy of the result (the estimate) decreases also.
Note that Spatial obtains the sample by using the SAMPLE(sample_percent) feature internally. For a description of this feature, see the sample_clause description in the SELECT statement section of the Oracle9i SQL Reference.
Spatial index level at which the layer is to be tessellated.
Number of tiles for variable or hybrid tessellation. Should be 0 for fixed tessellation. The default is 0.
Usage Notes
A return value of 0 indicates an error.
The tiling time estimate is based on the tiling time of a small sample geometry table that is automatically generated from the original table column. (This generated table is deleted before the function completes.)
The sample_ratio parameter lets you control the trade-off between speed and accuracy. Note that sample_ratio is not exact, but reflects an average. For example, a sample_ratio value of 20 sometimes causes fewer than 5 percent of geometry objects to be sampled and sometimes more than 5 percent, but over time an average of 5 percent will be sampled.
The CREATE TABLE privilege is required for using this function.
Examples
The following example estimates the tiling time to tessellate the REGIONS column of the XYZ_MARKETS table.
DECLARE table_name VARCHAR2(32) := 'XYZ_MARKETS'; column_name VARCHAR2(32) := 'REGIONS'; sample_ratio INTEGER := 15; tiling_level INTEGER := 6; num_tiles INTEGER := 10; ret_number NUMBER; BEGIN ret_number := SDO_TUNE.ESTIMATE_TILING_TIME( table_name, column_name, sample_ratio, tiling_level, num_tiles ); END; /
Format
SDO_TUNE.ESTIMATE_TOTAL_NUMTILES(
table_name IN VARCHAR2,
column_name IN VARCHAR2,
sample_ratio IN INTEGER,
tiling_level IN INTEGER,
num_tiles IN INTEGER,
num_largetiles OUT INTEGER
) RETURN INTEGER;
Description
Estimates the total number of spatial tiles for a layer.
Parameters
Geometry table.
Geometry column for which the total number of spatial tiles is to be estimated.
Approximate ratio between the geometries in the original layer and those in the sample layer (to be generated to perform the estimate). The default is 20: that is, the sample layer will contain approximately 1/20 (5 percent) of the geometries in the original layer. The larger the sample_ratio value, the faster the function will run, but the less accurate will be the result (the estimate).
Note that Spatial obtains the sample by using the SAMPLE(sample_percent) feature internally. For a description of this feature, see the sample_clause description in the SELECT statement section of the Oracle9i SQL Reference.
Spatial index level at which the layer is to be tessellated.
Number of tiles for variable or hybrid tessellation. Should be 0 for fixed tessellation. The default is 0.
Output parameter to contain the number of spatial tiles that are of the same size as group tiles for hybrid indexing. (For fixed indexing, num_largetiles will be the same as the returned value: the total number of spatial tiles.)
Usage Notes
The estimate is based on the total number of tiles for a small sample layer that is automatically generated from the original layer. (This generated table is deleted before the function completes.)
The sample_ratio parameter lets you control the trade-off between speed and accuracy. Note that sample_ratio is not exact, but reflects an average. For example, a sample_ratio value of 20 sometimes causes fewer than 5 percent of geometry objects to be sampled and sometimes more than 5 percent, but over time an average of 5 percent will be sampled.
The CREATE TABLE privilege is required for using this function.
Examples
The following example estimates the total number of spatial tiles required to index the REGIONS column of the XYZ_MARKETS table.
DECLARE table_name VARCHAR2(32) := 'XYZ_MARKETS'; column_name VARCHAR2(32) := 'REGIONS'; sample_ratio INTEGER := 15; tiling_level INTEGER := 4; num_tiles INTEGER := 10; num_largetiles INTEGER; ret_integer INTEGER; BEGIN ret_integer := SDO_TUNE.ESTIMATE_TOTAL_NUMTILES( table_name, column_name, sample_ratio, tiling_level, num_tiles, num_largetiles ); END; /
Format
SDO_TUNE.EXTENT_OF(
table_name IN VARCHAR2,
column_name IN VARCHAR2
) RETURN MDSYS.SDO_GEOMETRY;
Description
Returns the minimum bounding rectangle of all geometries in a column of type SDO_GEOMETRY.
Parameters
Geometry table.
Geometry column for which the minimum bounding rectangle is to be returned.
Usage Notes
The function returns NULL if the data is inconsistent.
|
Note: This function is deprecated, and will not be supported in future versions of Spatial. You are instead encouraged to use the SDO_AGGR_MBR function, documented in Chapter 12, to return the MBR of geometries. The SDO_TUNE.EXTENT_OF function is limited to two-dimensional geometries, whereas the SDO_AGGR_MBR function is not. |
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 (in Chapter 12)
SDO_TUNE.ESTIMATE_TILING_LEVEL
SDO_TUNE.AVERAGE_MBR procedure
Format
SDO_TUNE.HISTOGRAM_ANALYSIS(
table_name IN VARCHAR2,
column_name IN VARCHAR2,
result_table IN VARCHAR2,
type_of_histogram IN VARCHAR2,
max_value IN NUMBER,
intervals IN INTEGER);
Description
Generates statistical histograms based on columns of type SDO_GEOMETRY.
Parameters
Geometry table.
Geometry object column for which the histogram is to be computed.
Result table to hold the histogram.
Keyword to specify the type of histogram:
TILES_VS_LEVEL (default) -- Provides the number of tiles at different spatial index levels. (Available only with hybrid indexes.) This histogram is the default, and is used to evaluate the spatial index that is already built on the geometry column.
GEOMS_VS_TILES -- Provides the number of geometries in different number-of-tiles ranges. This histogram is used to evaluate the spatial index that is already built on the geometry column.
GEOMS_VS_AREA -- Provides the number of geometries in different size ranges. The shape of this histogram could be helpful in choosing a proper index type and index level
GEOMS_VS_VERTICES -- Provides a histogram of the geometry count against the number of vertices. This histogram could help determine if spatial index selectivity is important for the layer. Because the number of vertices determines the performance of the secondary filter, selectivity of the primary filter could be crucial for layers that contain many complicated geometries.
The upper limit of the histogram. That is, the histogram runs in range (0, max_value).
Number of intervals between 0 and max_value.
Usage Notes
The procedure populates the result table with statistical histograms for a geometry table. (HISTOGRAM_ANALYSIS is a procedure, not a function. Procedures do not return values.)
Before calling this procedure, create the result table (result_table parameter) with VALUE and COUNT columns. For example:
CREATE TABLE histogram (value NUMBER, count NUMBER);
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
Provides information about each geometry type stored in a column of type SDO_GEOMETRY.
Parameters
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.
EXECUTE 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
[, indextable IN VARCHAR2]
) RETURN NUMBER;
Description
Returns the quality degradation for an R-tree index or the average quality degradation for all index tables for an R-tree index.
Parameters
Name of the schema that contains the index specified in indexname.
Name of the Spatial R-tree index.
Name of the index table associated with the index specified in indexname. (This parameter is appropriate only if multiple index tables have been created using the ALTER INDEX statement with the add_index keyword.)
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 (or index table), 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) will probably affect query performance significantly for large databases, such as those with millions of rows.
If indextable is not specified, the function returns the average quality degradation for all index tables associated with indexname if multiple index tables have been created for the R-tree index. If multiple index tables have not been created (that is, if only one index table exists for the index), the quality degradation for the index is returned.
Index names and index table names are available through the xxx_SDO_INDEX_INFO and xxx_SDO_INDEX_METADATA views, which are described in Section 2.5.1.
For more information about R-tree quality and its effect on query performance, see Section 1.7.1.1.
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
Related Topics
Format
SDO_TUNE.RTREE_QUALITY(
schemaname IN VARCHAR2,
indexname IN VARCHAR2
[, indextable IN VARCHAR2]
) RETURN NUMBER;
Description
Returns the quality score for an R-tree index table or the average quality score for all index tables for an R-tree index.
Parameters
Name of the schema that contains the index specified in indexname.
Name of the Spatial R-tree index.
Name of the index table associated with the index specified in indexname. (This parameter is appropriate only if multiple index tables have been created using the ALTER INDEX statement with the add_index keyword.)
Usage Notes
If indextable is not specified, the function returns the average quality score for all index tables associated with indexname if multiple index tables have been created for the R-tree index. If multiple index tables have not been created (that is, if only one index table exists for the index), the quality score for the index is returned.
Index names and index table names are available through the xxx_SDO_INDEX_INFO and xxx_SDO_INDEX_METADATA views, which are described in Section 2.5.1.
This function can be useful in determining the quality of an R-tree and whether or not an R-tree index should be rebuilt in order to improve query performance. You can compare the index quality score returned by the function to the quality score at the time the index was created or most recently rebuilt (stored as SDO_RTREE_QUALITY in the xxx_INDEX_METADATA views, described in Section 2.5.1).
For more information about R-tree quality and its effect on query performance, see Section 1.7.1.1.
Examples
The following example returns the current quality score for the COLA_SPATIAL_IDX index.
SELECT SDO_TUNE.RTREE_QUALITY('SCOTT', 'COLA_SPATIAL_IDX') FROM DUAL;
SDO_TUNE.RTREE_QUALITY('SCOTT','COLA_SPATIAL_IDX')
--------------------------------------------------
1
Related Topics
|
 Copyright © 1999, 2002 Oracle Corporation All rights reserved |
|