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

Purpose

Calculates the average minimum bounding rectangle (MBR) for a geometry object column.

Format

SDO_TUNE.AVERAGE_MBR(

table_name IN VARCHAR2,

column_name IN VARCHAR2,

width OUT NUMBER,

height OUT NUMBER);

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

Example

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

Purpose

Estimates the spatial index performance such as query selectivity and window query time for a column of type SDO_GEOMETRY.

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;

Parameters

table_name

Geometry table.

column_name

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

sample_ratio

Approximate size ratio between the original layer and the sample layer (to be generated in order to perform the estimate). The default is 20: that is, the sample layer will be approximately 1/20 (0.05) the size of 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).

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.

Example

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

Purpose

Estimates the appropriate SDO_LEVEL value to use when indexing with hybrid or fixed-size tiles.

Format

SDO_TUNE.ESTIMATE_TILING_LEVEL(

table_name IN VARCHAR2,

column_name IN VARCHAR2,

maxtiles IN INTEGER,

type_of_estimate IN VARCHAR2 := 'AVG_GID_EXTENT'

) RETURN INTEGER;

Parameters

table_name

Geometry table.

column_name

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

maxtiles

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:

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

• AVG_GID_EXTENT -- Use a rectangle representing the average size of the individual geometric objects within the column. This option is the default and performs the most analysis of the three types, but takes the longest time to complete.

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.

Example

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, 'AVG_GID_EXTENT') 
  FROM DUAL;

SDO_TUNE.ESTIMATE_TILING_LEVEL('COLA_MARKETS','SHAPE',10000,'AVG_GID_EXTENT')   
-----------------------------------------------------------------------------   
                                                                            8  

Related Topics

• 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

Purpose

Returns the estimated time (in seconds) to tessellate a column of type SDO_GEOMETRY.

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;

Parameters

table_name

Geometry table.

column_name

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

sample_ratio

Approximate size ratio between the original layer and the sample layer (to be generated in order to perform the estimate). The default is 20: that is, the sample layer will be approximately 1/20 (0.05) the size of 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).

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.

Example

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

Purpose

Estimates the total number of spatial tiles for a layer.

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;

Parameters

table_name

Geometry table.

column_name

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

sample_ratio

Approximate size ratio between the original layer and the sample layer (to be generated in order to perform the estimate). The default is 20: that is, the sample layer will be approximately 1/20 (0.05) the size of 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).

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.

Example

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

Purpose

Returns the minimum bounding rectangle of all geometries in a column of type
SDO_GEOMETRY.

Format

SDO_TUNE.EXTENT_OF(

table_name IN VARCHAR2,

column_name IN VARCHAR2

) RETURN MDSYS.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.

Example

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

SDO_TUNE.AVERAGE_MBR procedure

---

SDO_TUNE.HISTOGRAM_ANALYSIS

Purpose

Generates statistical histograms based on columns of type SDO_GEOMETRY.

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

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:

• TILES_VS_LEVEL -- Provides the number of tiles at different spatial index levels. (Available only with hybrid indexes.) This histogram is the default, and is used to evaluate the spatial index that is already built on the geometry column.

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

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

Purpose

Provides information about each geometry type stored in a column of type SDO_GEOMETRY.

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

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.

Example

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

.