6
Tuning Functions and Procedures for Object-Relational Model
This chapter contains descriptions of the tuning functions and procedures shown in Table 6-1.
SDO_TUNE.AVERAGE_MBR
Purpose
This function calculates the average minimum bounding rectangle (MBR) for all geometries in a column of type SDO_GEOMETRY.
Syntax
SDO_TUNE.AVERAGE_MBR (table_name, column_name, width, height)
Keywords and Parameters
table_name |
Specifies the name of the geometry table to examine. Data type is VARCHAR2. |
column_name |
Specifies the name of the geometry object column to examine. Data type is VARCHAR2. |
width |
Returns the width of the average MBR. Data type is OUT NUMBER. |
height |
Returns the height of the average MBR. Data type is OUT NUMBER. |
Returns
The function returns the width and height of the average MBR for all geometries in a geometry table.
Data types for height and width are NUMBER.
Usage Notes
This function calculates the average MBR by keeping track of the maximum and
minimum X and Y values for all geometries in a geometry table.
SDO_TUNE.ESTIMATE_INDEX_PERFORMANCE
Purpose
This function estimates the spatial index performance such as query selectivity and window query time for a column of type SDO_GEOMETRY.
Syntax
SDO_TUNE.ESTIMATE_INDEX_PERFORMANCE (table_name, column_name, sample_ratio,
tiling_level, num_tiles, window_object, tiling_time, filter_time, query_time)
Keywords and Parameters
table_name |
Specifies the name of the geometry table to examine. Data type is VARCHAR2. |
column_name |
Specifies the name of the geometry object column to examine. Data type is VARCHAR2. |
sample_ratio |
Specifies the size ratio between the original layer and the sample layer to be generated. Data type is INTEGER. Default is 20. |
tiling_level |
Specifies the spatial index level at which the layer is to be tessellated. Data type is INTEGER. |
num_tiles |
Specifies the number of tiles for variable or hybrid tessellation. Data type is INTEGER. |
window_object |
Specifies the name of the spatial layer in which the query window is stored. Data type is VARCHAR2. |
tiling_time |
Returns the estimated tiling time in seconds. Data type is OUT NUMBER. |
filter_time |
Returns the estimated spatial index filter time in seconds. Data type is OUT NUMBER. |
query_time |
Returns the estimated query window time in seconds. Data type is OUT NUMBER. |
Returns
The function returns a number between 0.0 and 1.0 representing estimated spatial index selectivity. Data type is NUMBER.
The function also returns the estimated tiling time, filter time, and query time. Data type for these variables is NUMBER.
Usage Notes
- A larger selectivity number indicates better selectivity. A selectivity of 0.0 indicates an error.
- A larger sample_ratio means faster but less accurate estimation.
SDO_TUNE.ESTIMATE_TILING_LEVEL
Purpose
This function estimates the appropriate sdo_level to use when indexing with hybrid or fixed-size tiles.
Syntax
MDSYS.SDO_TUNE.ESTIMATE_TILING_LEVEL (table_name, column_name, maxtiles,
type_of_estimate)
Keywords and Parameters
table_name |
Specifies the name of the geometry table. Data type is VARCHAR2. |
column_name |
Specifies the name of the geometry column to examine. Data type is VARCHAR2. |
maxtiles |
Specifies the maximum number of tiles that can be used to index the rectangle defined by the type_of_estimate parameter. Data type is INTEGER. |
type_of_estimate |
Indicates by keyword one of three different models. Specify the type of estimate with one of the following keywords:LAYER_EXTENT -- Use the rectangle defined by your coordinate system. ALL_GID_EXTENT -- Use the minimum bounding rectangle that encompasses all the geometric objects in the column. This estimate is recommended for most applications with a maxtiles of 10,000.AVG_GID_EXTENT -- Use a rectangle representing the average size of the individual geometric objects within the column. This option performs the most analysis of the three types.
Data type is VARCHAR2. |
Returns
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.
Usage Notes
None.
Related Topics
SDO_TUNE.ESTIMATE_TILING_TIME
Purpose
This function provides the estimated time to tessellate a column of type
SDO_GEOMETRY.
Syntax
SDO_TUNE.ESTIMATE_TILING_TIME (table_name, column_name, sample_ratio, tiling_level,
num_tiles)
Keywords and Parameters
table_name |
Specifies the name of the geometry table to examine. Data type is VARCHAR2. |
column_name |
Specifies the name of the geometry object column to examine. Data type is VARCHAR2. |
sample_ratio |
Specifies the size ratio between the original layer and the sample layer to be generated. Data type is INTEGER. Default is 20. |
tiling_level |
Specifies the spatial index level at which the layer is to be tessellated. Data type is INTEGER. |
num_tiles |
Specifies the number of tiles for variable or hybrid tessellation. Data type is INTEGER. |
Returns
This function returns the estimated tiling time in seconds. A return of 0 indicates an error.
Data type is NUMBER.
Usage Notes
None.
SDO_TUNE.EXTENT_OF
Purpose
This function determines the extent of all geometries in a column of type
SDO_GEOMETRY.
Syntax
SDO_TUNE.EXTENT_OF (table_name, column_name)
Keywords and Parameters
table_name |
Specifies the name of the geometry table. Data type is VARCHAR2. |
column_name |
Specifies the name of the geometry column to examine. Data type is VARCHAR2. |
Returns
This function returns a geometry object representing the minimum bounding rectangle for all geometric data in a column. The function returns NULL if the data is inconsistent.
Usage Notes
None.
Related Topics
SDO_TUNE.ESTIMATE_TILING_LEVEL() function
SDO_TUNE.HISTOGRAM_ANALYSIS
Purpose
This procedure generates statistical histograms based on columns of type
SDO_GEOMETRY.
Syntax
SDO_TUNE.HISTOGRAM_ANALYSIS (table_name, column_name, result_table, type_of_histogram,
max_value, intervals)
Keywords and Parameters
table_name |
Specifies the name of the geometry table to examine. Data type is VARCHAR2. |
column_name |
Specifies the name of the geometry column to examine. Data type is VARCHAR2. |
result_table |
Specifies the name of the result table where the histogram will be stored. Data type is VARCHAR2. |
type_of_histogram |
Specifies one of three types of histograms:TILES_VS_LEVEL (default)GEOMS_VS_AREAGEOMS_VS_VERTICES
Data type is VARCHAR2. |
max_value |
Specifies the upper limit of the histogram. Data type is NUMBER. |
intervals |
Specifies the number of intervals between 0 and max_value. Data type is INTEGER. |
Returns
The procedure populates the result table with statistical histograms for a geometry table.
Usage Notes
SDO_TUNE.MIX_INFO
Purpose
This procedure provides the number of geometries of each type stored in a column of type SDO_GEOMETRY.
Syntax
SDO_TUNE.MIX_INFO (table_name, column_name)
Keywords and Parameters
table_name |
Specifies the name of the geometry table to examine. Data type is VARCHAR2. |
column_name |
Specifies the name of the geometry column to examine. Data type is VARCHAR2. |
Returns
The procedure calculates geometry type information for the table. It calculates the number of geometries of different types, as well as the percentages of points, line strings, polygons, and complex geometries.
Usage Notes
None.