Release 9.0.1
Part Number A88805-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle Spatial User's Guide and Reference Release 9.0.1 Part Number A88805-01 |
|
This chapter contains descriptions of the tuning functions and procedures shown in Table 17-1.
SDO_TUNE.ANALYZE_RTREE(
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.
Name of the schema that contains the index specified in indexname.
Name of the Spatial R-tree index to be analyzed.
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.
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.
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
SDO_TUNE.AVERAGE_MBR(
Calculates the average minimum bounding rectangle (MBR) for a geometry object column.
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.
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.)
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
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;
Estimates the spatial index performance such as query selectivity and window query time for a column of type SDO_GEOMETRY.
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.
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.
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; /
SDO_TUNE.ESTIMATE_TILING_LEVEL(
table_name IN VARCHAR2,
column_name IN VARCHAR2,
num_tiles IN INTEGER
[, type_of_estimate IN VARCHAR2]
) RETURN INTEGER;
Estimates the appropriate SDO_LEVEL value to use when indexing with hybrid or fixed-size tiles.
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:
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.
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
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;
Returns the estimated time (in seconds) to tessellate a column of type SDO_GEOMETRY.
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. 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.
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.
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; /
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;
Estimates the total number of spatial tiles for a layer.
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.)
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.
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; /
SDO_TUNE.EXTENT_OF(
Returns the minimum bounding rectangle of all geometries in a column of type SDO_GEOMETRY.
Geometry table.
Geometry column for which the minimum bounding rectangle is to be returned.
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 13, to return the MBR of geometries. The SDO_TUNE.EXTENT_OF function is limited to 2-dimensional geometries, whereas the SDO_AGGR_MBR function is not. |
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))
SDO_AGGR_MBR (in Chapter 13)
SDO_TUNE.ESTIMATE_TILING_LEVEL
SDO_TUNE.AVERAGE_MBR procedure
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);
Generates statistical histograms based on columns of type SDO_GEOMETRY.
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:
The upper limit of the histogram. That is, the histogram runs in range (0, max_value).
Number of intervals between 0 and max_value.
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);
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] );
Provides information about each geometry type stored in a column of type SDO_GEOMETRY.
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.
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.
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%)
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.
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.)
The quality degradation is a number indicating approximately how much longer it will take to execute any given query with the current index (or index table) compared to executing the same query when the index was created or most recently rebuilt. For example, if a typical query will probably take twice as much time as when the index was created or rebuilt, the quality degradation is 2.
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.
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, 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
SDO_TUNE.RTREE_QUALITY(
Returns the quality score for an R-tree index table or the average quality score for all index tables for an R-tree index.
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.)
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.
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
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|