6
Tuning Functions and Procedures for Object-Relational Model

This chapter contains descriptions of the tuning functions and procedures shown in Table 6-1.

Table 6-1 Tuning Functions and Procedures

Function/Procedure	Description
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.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.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

• MDSYS.SDO_TUNE.EXTENT_OF

• Section A.2.2, "Understanding the Tiling Level"

• Section A.2.4, "Visualizing the Spatial Index (Drawing Tiles)"

---

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

• Prior to calling this procedure, create the result table as follows:

  CREATE TABLE histogram (value NUMBER, count NUMBER);
  

• The following types of histograms are available:

  TILES_VS_LEVEL	Provides the number of tiles at different spatial index levels. This histogram is used to evaluate the spatial index that is already built on the data set layer.
  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.

---

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.

---