16
Tuning Functions and Procedures for Relational Model

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.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.

This chapter refers to the relational Spatial model only.

---

SDO_TUNE.AVERAGE_MBR

Format

SDO_TUNE.AVERAGE_MBR (layername, width, height)

Description

Calculates the average minimum bounding rectangle (MBR) for all geometries in a layer.

Keywords and Parameters

layername	Specifies the name of the data set layer 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

This function returns the width and height of the average MBR for all geometries in a layer.

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 layer.

---

SDO_TUNE.ESTIMATE_INDEX_PERFORMANCE

Format

SDO_TUNE.ESTIMATE_INDEX_PERFORMANCE (layername, sample_ratio, tiling_level,
num_tiles, window_layer, window_gid, tiling_time, filter_time, query_time)

Description

Estimates the spatial index performance such as query selectivity and window query time for a layer.

Keywords and Parameters

layername	Specifies the name of the data set layer 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_layer	Specifies the name of the spatial layer in which the window geometry is stored. Data type is VARCHAR2.
window_gid	Specifies the window geometry ID. Data type is NUMBER.
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 window query time in seconds. Data type is OUT NUMBER.

Returns

This function returns a number between 0.0 and 1.0 representing estimated spatial index selectivity. It 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

Format

SDO_TUNE.ESTIMATE_TILING_LEVEL (layername, maxtiles, type_of_estimate)

Description

Estimates the appropriate tiling level to use when indexing with fixed-size tiles.

Keywords and Parameters

layername	Specifies the name of the data set layer 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 within the layer. Recommended for most applications.AVG_GID_EXTENT -- Use a rectangle representing the average size of the individual geometries within the layer. This option performs the most extensive analysis of the three types.

Returns

This function returns an integer representing the level to use when creating a spatial index for the specified layer.

Usage Notes

The SDO_ADMIN.POPULATE_INDEX and SDO_ADMIN.UPDATE_INDEX procedures are used to create or update the spatial index using fixed-size or hybrid indexing. Store the value returned by the SDO_TUNE.ESTIMATE_TILING_LEVEL function in the SDO_LEVEL column of the <layername>_SDOLAYER table before building the spatial index.

The maxtiles parameter specifies the maximum number of tiles that should be used to define a grid covering the rectangular extent of interest. This extent could be:

• Defined in the <layername>_SDODIM table, which defines the bounds of the coordinate system

• Defined by the minimum and maximum coordinates for the given data set (as returned by the SDO_TUNE.EXTENT_OF procedure)

• Defined by computing the average bounds of the objects in the
  <layername>_SDOGEOM table

The code shown in Example 16-1 generates a recommendation based on the extent of the defined coordinate system (-90 to +90 latitude and -180 to +180 longitude). This example returns a level whose tiles are not smaller than one-degree cells.

Example 16-1 Recommended Tile Level for One-Degree Latitude/Longitude Cells

set serveroutput on
declare
   tiling_level integer;
begin
   tiling_level := mdsys.sdo_tune.estimate_tiling_level('WORLD_CITIES',

           360*180, 'LAYER_EXTENT');
    dbms_output.put_line('VALUE is '|| tiling_level);
end;

For most applications, however, it is more effective to call the SDO_TUNE.ESTIMATE_TILING_LEVEL function using the ALL_GID_EXTENT estimate type with a maxtiles value of 10,000. In Example 16-2, assume the data set consists of block groups for San Francisco and that the <layername>_SDODIM table defines the extent to be one that covers all of California. Because the data set is localized to a small subregion of this extent, ALL_GID_EXTENT is the appropriate estimate type. The recommended tiling level in this case will be such that at most, 10,000 tiles will be required to completely cover the extent of San Francisco block groups.

Example 16-2 Recommended Tile Level Based on the GIDs of All Geometries

set serveroutput on
declare
   tiling_level integer;
begin
   tiling_level:=   mdsys.sdo_tune.estimate_tiling_level('SF_BLOCK_GROUPS',

         10000, 'ALL_GID_EXTENT');
   dbms_output.put_line('VALUE is' ,|| tiling_level);
end;

The third type of estimate helps determine the tiling level that should be used such that on average, the maxtiles parameter defines the number of tiles to cover the extent of a single geometry in the layer. This estimate type requires the most computation of the three because the bounding rectangle of every geometry is used in calculating the average extent. In Example 16-3, eight tiles on average are used to cover any block group in San Francisco.

Example 16-3 Recommended Tile Level Based on Average Extent of All Geometries

set serveroutput on
declare
   tiling_level integer;
begin
   tiling_level := mdsys.sdo_tune.estimate_tiling_level('SF_BLOCK_GROUPS', 8,

         'AVG_GID_EXTENT');
   dbms_output.put_line('Tiling level value is ' || tiling_level);
end;

Related Topics

• SDO_ADMIN.POPULATE_INDEX

• SDO_ADMIN.UPDATE_INDEX

• SDO_TUNE.EXTENT_OF

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

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

---

SDO_TUNE.ESTIMATE_TILING_TIME

Format

SDO_TUNE.ESTIMATE_TILING_TIME (layername, sample_ratio, tiling_level, num_tiles)

Description

Returns the estimated time to tessellate a layer.

Keywords and Parameters

layername	Specifies the name of the data set layer 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

Format

SDO_TUNE.EXTENT_OF (layername, min_X, max_X, min_Y, max_Y)

Description

Determines the extent of all geometries in a layer.

Keywords and Parameters

layername	Specifies the name of the data set layer. The layer name is used to construct the name of the geometry and spatial index tables. Data type is VARCHAR2.
min_X	Minimum X value of the bounding rectangle. Data type is NUMBER.
max_X	Maximum X value of the bounding rectangle. Data type is NUMBER.
min_Y	Minimum Y value of the bounding rectangle. Data type is NUMBER.
max_Y	Maximum Y value of the bounding rectangle. Data type is NUMBER.

Returns

This function returns the coordinates of the minimum bounding rectangle for all geometric data in a layer.

Data type is NUMBER for the four return values.

Usage Notes

None.

Related Topics

• SDO_TUNE.ESTIMATE_TILING_LEVEL function

---

SDO_TUNE.HISTOGRAM_ANALYSIS

Format

SDO_TUNE.HISTOGRAM_ANALYSIS (layername, result_table, type_of_histogram,
max_value, intervals)

Description

Generates statistical histograms based on a layer.

Keywords and Parameters

layername	Specifies the name of the spatial data set layer 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 the following types of histograms:TILES_VS_LEVEL (default)GEOMS_VS_AREAGEOMS_VS_TILESGEOMS_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

This procedure populates the result table with statistical histograms for a spatial layer.

Usage Notes

You must create the result table before calling this procedure. The table has the following format:

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. (Available only with hybrid indexes.) This histogram is used to evaluate the spatial index that is already built on the 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_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_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

Format

SDO_TUNE.MIX_INFO (layername)

Description

Provides the number of geometries of each type stored in the layer.

Keywords and Parameters

layername

Specifies the name of the spatial data set layer to examine. Data type is VARCHAR2.

Returns

This function calculates geometry type information for the layer. It returns the number of geometries of different type, as well as the percentages of points, line strings, polygons, and complex geometries.

Usage Notes

None.