Release 8.1.7
Part Number A85337-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Spatial User's Guide and Reference Release 8.1.7 Part Number A85337-01 |
|
This chapter contains descriptions of the tuning functions and procedures shown in Table 11-1.
SDO_TUNE.AVERAGE_MBR(
Calculates the average minimum bounding rectangle (MBR) for a geometry object column.
Geometry table.
Geometry column for which the average minimum bounding rectangle is to be computed.
Width of the average minimum bounding rectangle.
Height of the average minimum bounding rectangle.
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.)
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
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;
Estimates the spatial index performance such as query selectivity and window query time for a column of type SDO_GEOMETRY.
Geometry table.
Geometry column for which the tiling time is to be estimated.
Approximate ratio between the geometries in the original layer and those in the sample layer (to be generated in order to perform the estimate). The default is 20: that is, the sample layer will contain approximately 1/20 (5 percent) of the geometries in 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).
Note that Spatial obtains the sample by using the SAMPLE(sample_percent) feature internally. For a description of this feature, see the sample_clause description in the "SELECT and Subqueries" section of the Oracle8i SQL Reference.
Spatial index level at which the layer is to be tessellated.
Number of tiles for variable or hybrid tessellation. Should be 0 for fixed tessellation. The default is 0.
Window geometry object.
Estimated tiling time in seconds.
Estimated spatial index filter time in seconds.
Estimated window query time in seconds.
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.
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(
table_name IN VARCHAR2,
column_name IN VARCHAR2,
maxtiles IN INTEGER,
type_of_estimate IN VARCHAR2 := 'AVG_GID_EXTENT'
) RETURN INTEGER;
Estimates the appropriate SDO_LEVEL value to use when indexing with hybrid or fixed-size tiles.
Geometry table.
Geometry column for which the tiling level is to be estimated.
Maximum number of tiles that can be used to index the rectangle defined by type_of_estimate.
Keyword to specify the type of estimate:
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.
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, 'ALL_GID_EXTENT') FROM DUAL; SDO_TUNE.ESTIMATE_TILING_LEVEL('COLA_MARKETS','SHAPE',10000,'ALL_GID_EXTENT') ----------------------------------------------------------------------------- 7
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;
Returns the estimated time (in seconds) to tessellate a column of type SDO_GEOMETRY.
Geometry table.
Geometry column for which the tiling time is to be estimated.
Approximate ratio between the geometries in the original layer and those in the sample layer (to be generated to perform the estimate). The default is 20: that is, the sample layer will contain approximately 1/20 (5 percent) of the geometries in 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).
Note that Spatial obtains the sample by using the SAMPLE(sample_percent) feature internally. For a description of this feature, see the sample_clause description in the "SELECT and Subqueries" section of the Oracle8i SQL Reference.
Spatial index level at which the layer is to be tessellated.
Number of tiles for variable or hybrid tessellation. Should be 0 for fixed tessellation. The default is 0.
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.
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(
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;
Estimates the total number of spatial tiles for a layer.
Geometry table.
Geometry column for which the total number of spatial tiles is to be estimated.
Approximate ratio between the geometries in the original layer and those in the sample layer (to be generated to perform the estimate). The default is 20: that is, the sample layer will contain approximately 1/20 (5 percent) of the geometries in 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).
Note that Spatial obtains the sample by using the SAMPLE(sample_percent) feature internally. For a description of this feature, see the sample_clause description in the "SELECT and Subqueries" section of the Oracle8i SQL Reference.
Spatial index level at which the layer is to be tessellated.
Number of tiles for variable or hybrid tessellation. Should be 0 for fixed tessellation. The default is 0.
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.)
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.
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(
Returns the minimum bounding rectangle of all geometries in a column of type SDO_GEOMETRY.
Geometry table.
Geometry column for which the minimum bounding rectangle is to be returned.
The function returns NULL if the data is inconsistent.
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))
SDO_TUNE.ESTIMATE_TILING_LEVEL
SDO_TUNE.AVERAGE_MBR procedure
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);
Generates statistical histograms based on columns of type SDO_GEOMETRY.
Geometry table.
Geometry object column for which the histogram is to be computed.
Result table to hold the histogram.
Keyword to specify the type of histogram:
The upper limit of the histogram. That is, the histogram runs in range (0, max_value).
Number of intervals between 0 and max_value.
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 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(
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] );
Provides information about each geometry type stored in a column of type SDO_GEOMETRY.
Geometry table.
Geometry object column for which the geometry type information is to be calculated.
Total number of geometry objects.
Number of point geometry objects.
Number of curve string geometry objects.
Number of polygon geometry objects.
Number of complex geometry objects.
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.
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%)
.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|