Skip Headers

Oracle® Spatial User's Guide and Reference
Release 9.2
Part No. A96630-01
Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents

Master Index
Go To Index
Index

Feedback

Previous Next

16
Tuning Functions and Procedures

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.

SDO_TUNE.ANALYZE_RTREE

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

schemaname

Name of the schema that contains the index specified in indexname.

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

SDO_TUNE.QUALITY_DEGRADATION

SDO_TUNE.RTREE_QUALITY


SDO_TUNE.AVERAGE_MBR

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

table_name

Geometry table.

column_name

Geometry column for which the average minimum bounding rectangle is to be computed.

width

Width of the average minimum bounding rectangle.

height

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

SDO_TUNE.EXTENT_OF


SDO_TUNE.ESTIMATE_INDEX_PERFORMANCE

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

table_name

Geometry table.

column_name

Geometry column for which the tiling time is to be estimated.

sample_ratio

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.

tiling_level

Spatial index level at which the layer is to be tessellated.

num_tiles

Number of tiles for variable or hybrid tessellation. Should be 0 for fixed tessellation. The default is 0.

window_obj

Window geometry object.

tiling_time

Estimated tiling time in seconds.

filter_time

Estimated spatial index filter time in seconds.

query_time

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;
/

SDO_TUNE.ESTIMATE_TILING_LEVEL

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

table_name

Geometry table.

column_name

Geometry column for which the tiling level is to be estimated.

num_tiles

Maximum number of tiles that can be used to index the rectangle defined by type_of_estimate.

type_of_estimate

Keyword to specify the type of estimate:

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


SDO_TUNE.ESTIMATE_TILING_TIME

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

table_name

Geometry table.

column_name

Geometry column for which the tiling time is to be estimated.

sample_ratio

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.

tiling_level

Spatial index level at which the layer is to be tessellated.

num_tiles

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;
/

SDO_TUNE.ESTIMATE_TOTAL_NUMTILES

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

table_name

Geometry table.

column_name

Geometry column for which the total number of spatial tiles is to be estimated.

sample_ratio

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.

tiling_level

Spatial index level at which the layer is to be tessellated.

num_tiles

Number of tiles for variable or hybrid tessellation. Should be 0 for fixed tessellation. The default is 0.

num_largetiles

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;
/

SDO_TUNE.EXTENT_OF

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

table_name

Geometry table.

column_name

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


SDO_TUNE.HISTOGRAM_ANALYSIS

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

table_name

Geometry table.

column_name

Geometry object column for which the histogram is to be computed.

result_table

Result table to hold the histogram.

type_of_histogram

Keyword to specify the type of histogram:

max_value

The upper limit of the histogram. That is, the histogram runs in range (0, max_value).

intervals

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);

SDO_TUNE.MIX_INFO

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

table_name

Geometry table.

column_name

Geometry object column for which the geometry type information is to be calculated.

total_geom

Total number of geometry objects.

point_geom

Number of point geometry objects.

curve_geom

Number of curve string geometry objects.

poly_geom

Number of polygon geometry objects.

complex_geom

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%)  

SDO_TUNE.QUALITY_DEGRADATION

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

schemaname

Name of the schema that contains the index specified in indexname.

indexname

Name of the Spatial R-tree index.

indextable

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

SDO_TUNE.ANALYZE_RTREE

SDO_TUNE.RTREE_QUALITY


SDO_TUNE.RTREE_QUALITY

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

schemaname

Name of the schema that contains the index specified in indexname.

indexname

Name of the Spatial R-tree index.

indextable

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

SDO_TUNE.ANALYZE_RTREE

SDO_TUNE.QUALITY_DEGRADATION






Previous Next
Oracle Logo
Copyright © 1999, 2002 Oracle Corporation

All rights reserved
Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents

Master Index
Go To Index
Index

Feedback