Oracle8i Spatial User's Guide and ReferenceRelease 8.1.5A67295-01

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

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

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

None.

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

### Returns

This function returns the estimated tiling time in seconds. A return of 0 indicates an error.

Data type is NUMBER.

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)

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

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
)

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

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

None.