1 PACKAGE sdo_tune AUTHID current_user AS
2
3 ------------------------------------------------------------------------------
4 -- Purpose
5 -- This function can be used to determine which level to use when indexing
6 -- a layer using the fixed tile method. The level you choose to tile a
7 -- layer is a major factor in query performance. If you choose to use a
8 -- very small tile size then many tiles are generated for a large
9 -- area-of-interest. If you choose a very large tile size then the primary
10 -- filter will return too many candidate rows, and is less effective.
11 --
12 -- Syntax
13 -- ESTIMATE_TILING_LEVEL (layer, maxtiles, type_of_estimate)
14 --
15 -- Keywords and Parameters
16 -- layer is the name of the spatial layer for which the tile
17 -- level is to be determined. Datatype is VARCHAR2.
18 -- maxtiles is the maximum number of valid tiles that can be used
19 -- to index the type_of_estimate object defined below.
20 -- Datatype is INTEGER.
21 -- type_of_estimate There are three different type_of_estimate objects
22 -- that can be defined:
23 -- LAYER_EXTENT defines the type_of_estimate object to be the rectangle
24 -- defined by the users coordinate system.
25 -- ALL_GID_EXTNET defines the type_of_estimate object to be the rectangle
26 -- that defines the bounds of all the SDO_GID's of a layer.
27 -- AVG_GID_EXTENT defines the type_of_estimate object to be the rectangle
28 -- that represents the average individual geometry bounding
29 -- box. The average bounding box is determined by sampling
30 -- every geometry in the specified layer. Datatype is
31 -- VARCHAR2.
32 --
33 -- Returns
34 -- This function returns an integer representing the level used to index the
35 -- specified layer.
36 --
37 -- 0 is returned if the data is inconsistent:
38 -- e.g. _sdodim does not match _sdogeom table.
39 -- This needs to be fixed by adding more error checking in future versions.
40 --
41 -- Usage Notes
42 -- Using the AVG_GID_EXTENT performs the most analysis of the three types.
43 --
44 /* DEPRECATED
45 FUNCTION estimate_tiling_level (layer VARCHAR2,
46 num_tiles INTEGER,
47 type_of_estimate VARCHAR2 := 'ALL_GID_EXTENT')
48 RETURN INTEGER;
49 */
50
51
52 ------------------------------------------------------------------------------
53 -- Purpose
54 -- This procedure caclulates the extents of a layer by keeping track of the
55 -- max and min x and y values for all geometries in the layer. These numbers
56 -- also gives the MBR (minimal bounding rectangle) of all the geometries.
57 --
58 -- Syntax
59 -- EXTENT_OF (layer, min_x, max_x, min_y, max_y)
60 --
61 -- Keywords and Paramters
62 -- layer is the name of the spatial layer for which the extent is to
63 -- be determined. Datatype is VARCHAR2.
64 -- min_x smallest x value for all geometries. Datatype is out number.
65 -- max_x largest x value for all geometries. Datatype is out number.
66 -- min_y smallest y value for all geometries. Datatype is out number.
67 -- max_y largest y value for all geometries. Datatype is out number.
68 --
69
70 PROCEDURE extent_of (layer VARCHAR2,
71 min_x OUT NUMBER,
72 max_x OUT NUMBER,
73 min_y OUT NUMBER,
74 max_y OUT NUMBER);
75
76
77 ------------------------------------------------------------------------------
78 -- Purpose
79 -- This procedure caclulates the average MBR (minimal bounding rectangle)
80 -- for a layer by keeping track of the max and min x and y values for all
81 -- geometries in the layer.
82 --
83 -- Syntax
84 -- AVERAGE_MBR (layer, width, height)
85 --
86 -- Keywords and Paramters
87 -- layer is the name of the spatial layer for which the average MBR is to
88 -- be determined. Datatype is VARCHAR2.
89 -- width width of the average MBR. Datatype is out number.
90 -- height height of the average MBR. Datatype is out number.
91 --
92
93 PROCEDURE average_mbr (layer VARCHAR2,
94 width OUT NUMBER,
95 height OUT NUMBER);
96
97
98 ------------------------------------------------------------------------------
99 -- Purpose
100 -- This procedure caclulates statistical histograms for a spatial layer.
101 -- Information from such histograms could be helpful to determine proper
102 -- spatial index type (fixed, variable, or hybrid) and index parameters
103 -- for the layer.
104 --
105 -- Syntax
106 -- HISTOGRAM_ANALYSIS (layer, result_table, type_of_histogram,
107 -- max_value, intervals)
108 --
109 -- Keywords and Paramters
110 -- layer the name of the spatial layer for which the histogram
111 -- is to be computed. Datatype is VARCHAR2.
112 -- result_table result table name where the histogram is stored. User
113 -- must create this table before this procedure is
114 -- called. This table contains two columnsof type
115 -- NUMBER: 'value' and 'count'. For an example,
116 -- CREATE TABLE histogram (value NUMBER, count NUMBER);
117 -- type_of_histogram There are three types of histograms:
118 -- TILES_VS_LEVEL Number of tiles at different spatial index level.
119 -- This histogram is used to evaluate the spatial index
120 -- that is already built on the layer.
121 -- GEOMS_VS_TILES Number of geometries in different #tiles range.
122 -- This histogram is used to evaluate the spatial index
123 -- that is already built on the layer.
124 -- GEOMS_VS_AREA Number of geometries in different size range. The
125 -- shape of this histogram could be hepful for users
126 -- to choose a proper index type and index level.
127 -- GEOMS_VS_VERTICES A histogram of geometry count vs the number of
128 -- vertices (complexity) could tell users if spatial
129 -- index selectivity is important for the layer. Since
130 -- the number of vertices determine the performance of
131 -- secondary filter (RELATE), selectivity of the primary
132 -- filter could be crucial for layer that contains many
133 -- complicated geometries.
134 -- max_value The upper limit of the histogram. i.e. The histogram
135 -- runs in range (0, max_value).
136 -- intervals Number of intervals between 0 and max_value.
137 --
138 /* DEPRECATED
139 PROCEDURE histogram_analysis (layer VARCHAR2,
140 result_table VARCHAR2,
141 type_of_histogram VARCHAR2 := 'TILES_VS_LEVEL',
142 max_value NUMBER,
143 intervals INTEGER);
144 */
145
146
147 ------------------------------------------------------------------------------
148 -- Purpose
149 -- This procedure caclulates geometry type information for the layer. It
150 -- outputs the number of geometries of different type, as well as the
151 -- percentages of points, linestrings, polygons, and complex geometries.
152 --
153 -- Syntax
154 -- MIX_INFO (layer, total_geom, point_geom, line_geom, poly_geom,
155 -- complex_geom)
156 --
157 -- Keywords and Paramters
158 -- layer the name of the spatial layer for which the geometry type
159 -- information is to be calculated. Datatype is VARCHAR2.
160 -- total_geom the total number of geometries. Datatype is OUT NUMBER.
161 -- point_geom the number of point geometries. Datatype is OUT NUMBER.
162 -- line_geom the number of linestring geometries. Datatype is OUT NUMBER.
163 -- poly_geom the number of polygon geometries. Datatype is OUT NUMBER.
164 -- complex_geom the number of complex geometries. Datatype is OUT NUMBER.
165 --
166
167 PROCEDURE mix_info (layer VARCHAR2,
168 total_geom OUT INTEGER,
169 point_geom OUT INTEGER,
170 line_geom OUT INTEGER,
171 poly_geom OUT INTEGER,
172 complex_geom OUT INTEGER);
173
174
175 ------------------------------------------------------------------------------
176 -- Purpose
177 -- This procedure caclulates geometry type information for the layer. It
178 -- prints out the number of geometries of different type, as well as the
179 -- percentages of points, linestrings, polygons, and complex geometries.
180 --
181 -- Syntax
182 -- MIX_INFO (layer)
183 --
184 -- Keywords and Paramters
185 -- layer the name of the spatial layer for which the geometry type
186 -- information is to be calculated. Datatype is VARCHAR2.
187 --
188
189 PROCEDURE mix_info (layer VARCHAR2);
190
191
192 ------------------------------------------------------------------------------
193 -- Purpose
194 -- This function estimates tiling time for a layer. The estimate is based
195 -- on the tiling time of a small sample layer that is automatically
196 -- generated from the original layer. User must have create-table privilege
197 -- to call this function. The sample layer will be cleaned up before this
198 -- funtion exits.
199 --
200 -- Syntax
201 -- ESTIMATE_TILING_TIME (layer, sample_ratio, tiling_level, num_tiles)
202 --
203 -- Keywords and Paramters
204 -- layer the name of the spatial layer for which the tiling time is
205 -- to be estimated. Datatype is VARCHAR2.
206 -- sample_ratio The size ratio between the original layer and the sample
207 -- layer (to be generated). Larger ratio means faster but
208 -- less accurate estimation. Datatype is INTEGER.
209 -- tiling_level The spatial index level at which the layer is to be
210 -- tessellated. Datatype is INTEGER.
211 -- num_tiles The number of tiles for variable or hybrid tessellation.
212 -- Should be NULL for fixed tessellation. Datatype is INTEGER.
213 --
214 -- Returns
215 -- This function returns a number representing estimated tiling time in
216 -- seconds, or 0 is returned on error.
217 --
218 /* DEPRECATED
219 FUNCTION estimate_tiling_time (layer VARCHAR2,
220 sample_ratio INTEGER := 20,
221 tiling_level INTEGER,
222 num_tiles INTEGER := NULL)
223 RETURN NUMBER;
224 */
225
226
227 ------------------------------------------------------------------------------
228 -- Purpose
229 -- This function estimates the total number of spatial tiles for a layer.
230 -- The estimate is based on the total number of tiles for a small sample
231 -- layer that is automatically generated from the original layer. User
232 -- must have create-table privilege to call this function. The sample
233 -- layer will be cleaned up before this funtion exits.
234 --
235 -- Syntax
236 -- ESTIMATE_TOTAL_NUMTILES (layer, sample_ratio, tiling_level, num_tiles,
237 -- num_largetiles)
238 --
239 -- Keywords and Paramters
240 -- layer the name of the spatial layer for which the tiling time
241 -- is to be estimated. Datatype is VARCHAR2.
242 -- sample_ratio The size ratio between the original layer and the sample
243 -- layer (to be generated). Larger ratio means faster but
244 -- less accurate estimation. Datatype is INTEGER.
245 -- tiling_level The spatial index level at which the layer is to be
246 -- tessellated. Datatype is INTEGER.
247 -- num_tiles The number of tiles for variable or hybrid tessellation.
248 -- Should be NULL for fixed tessellation. Datatype is
249 -- INTEGER.
250 -- num_largetiles An output parameter indicating how many spatial tiles
251 -- are of the same size as group tiles for hybrid indexing.
252 -- For fixed indexing, this value should always be the same
253 -- as the returned value (total number of spatial tiles).
254 -- Datatype is OUT INTEGER.
255 --
256 -- Returns
257 -- This function returns an integer representing estimated total number of
258 -- spatial tiles, or 0 is returned on error.
259 --
260 /* DEPRECATED
261 FUNCTION estimate_total_numtiles (layer VARCHAR2,
262 sample_ratio INTEGER := 20,
263 tiling_level INTEGER,
264 num_tiles INTEGER := NULL,
265 num_largetiles OUT INTEGER)
266 RETURN INTEGER;
267 */
268
269
270 ------------------------------------------------------------------------------
271 -- Purpose
272 -- This function estimates spatial index performance such as query
273 -- selectivity and window query time for a layer. The estimate is based
274 -- on the window query time of a small sample layer that is automatically
275 -- generated from the original layer. User must have create-table privilege
276 -- to call this function. The sample layer will be cleaned up before this
277 -- funtion exits.
278 --
279 -- Syntax
280 -- ESTIMATE_INDEX_PERFORMANCE (layer, sample_ratio, tiling_level, num_tiles,
281 -- window_layer, window_gid, tiling_time,
282 -- filter_time, query_time)
283 --
284 -- Keywords and Paramters
285 -- layer the name of the spatial layer for which the tiling time is
286 -- to be estimated. Datatype is VARCHAR2.
287 -- sample_ratio The size ratio between the original layer and the sample
288 -- layer (to be generated). Larger ratio means faster but
289 -- less accurate estimation. Datatype is INTEGER.
290 -- tiling_level The spatial index level at which the layer is to be
291 -- tessellated. Datatype is INTEGER.
292 -- num_tiles The number of tiles for variable or hybrid tessellation.
293 -- Should be NULL for fixed tessellation. Datatype is INTEGER.
294 -- window_layer The name of the spatial layer in which window geometry is
295 -- stored. Datatype is VARCHAR2.
296 -- window_gid The window geometry ID. Datatype is NUMBER.
297 -- tiling_time The estimated tiling time in seconds. Datatype is OUT
298 -- NUMBER.
299 -- filter_time The estimated spatial index filter time in seconds.
300 -- Datatype is OUT NUMBER.
301 -- query_time The estimated window query time in seconds. Datatype is
302 -- OUT NUMBER.
303 --
304 -- Returns
305 -- This function returns a number between 0.0 and 1.0 representing estimated
306 -- spatial index selectivity (large number means better selectivity, and 0.0
307 -- implies an error).
308 --
309 /* DEPRECATED
310 FUNCTION estimate_index_performance (layer VARCHAR2,
311 sample_ratio INTEGER := 20,
312 tiling_level INTEGER,
313 num_tiles INTEGER := NULL,
314 window_layer VARCHAR2,
315 window_gid NUMBER,
316 tiling_time OUT NUMBER,
317 filter_time OUT NUMBER,
318 query_time OUT NUMBER)
319 RETURN NUMBER;
320 */
321
322
323 --==========================================================================--
324 -- For object schema:
325 --==========================================================================--
326
327 ------------------------------------------------------------------------------
328 -- Purpose
332 -- Syntax
329 -- This procedure estimates which level to use when indexing geometry
330 -- objects using the fixed tile method.
331 --
333 -- ESTIMATE_TILING_LEVEL (table_name, column_name, maxtiles,
334 -- type_of_estimate)
335 --
336 -- Keywords and Parameters
337 -- table_name is the name of the geometry object table. Datatype is
338 -- VARCHAR2.
339 -- column_name is the name of the geometry object column for which the
340 -- tile level is to be determined. Datatype is VARCHAR2.
341 -- maxtiles is the maximum number of valid tiles that can be used
342 -- to index the type_of_estimate object defined below.
343 -- Datatype is INTEGER.
344 -- type_of_estimate There are three different type_of_estimate objects
345 -- that can be defined:
346 -- LAYER_EXTENT defines the type_of_estimate object to be the rectangle
347 -- defined by the users coordinate system.
348 -- ALL_GID_EXTNET defines the type_of_estimate object to be the rectangle
349 -- that defines the bounds of all the SDO_GID's of a layer.
350 -- AVG_GID_EXTENT defines the type_of_estimate object to be the rectangle
351 -- that represents the average individual geometry bounding
352 -- box. The average bounding box is determined by sampling
353 -- every geometry in the specified layer. Datatype is
354 -- VARCHAR2.
355 --
356 -- Returns
357 -- This function returns an integer representing the level used to index the
358 -- specified layer.
359 --
360 -- 0 is returned if the data is inconsistent:
361 -- e.g. invalid combination of table name and column name.
362 -- This needs to be fixed by adding more error checking in future versions.
363 -- Usage Notes
364 -- Using the AVG_GID_EXTENT performs the most analysis of the three types.
365 --
366 /* DEPRECATED
367 FUNCTION estimate_tiling_level(table_name VARCHAR2,
368 column_name VARCHAR2,
369 num_tiles INTEGER,
370 type_of_estimate VARCHAR2 := 'AVG_GID_EXTENT')
371 RETURN INTEGER;
372 */
373
374
375 ------------------------------------------------------------------------------
376 -- Purpose
377 -- This procedure caclulates the extents (known as Minimal Bounding
378 -- Rectangle, or MBR) of all geometry objects in the specified table column.
379 --
380 -- Syntax
381 -- EXTENT_OF (table_name, column_name)
382 --
383 -- Keywords and Paramters
384 -- table_name is the name of the geometry object table. Datatype is
385 -- VARCHAR2.
386 -- column_name is the name of the geometry object column for which the
387 -- extent is to be determined. Datatype is VARCHAR2.
388 --
389 -- Returns
390 -- This function returns a geometry object representing extent (MBR) of the
391 -- specified geometry object column.
392 --
393 -- NULL is returned if the data is inconsistent:
394 -- e.g. invalid combination of table name and column name.
395 -- This needs to be fixed by adding more error checking in future versions.
396 --
397
398 FUNCTION extent_of(table_name VARCHAR2,
399 column_name VARCHAR2,
400 use_index VARCHAR2 default 'TRUE')
401 RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC PARALLEL_ENABLE;
402
403
404 ------------------------------------------------------------------------------
405 -- Purpose
406 -- This procedure caclulates the average MBR (minimal bounding rectangle)
407 -- for a geometry object column by keeping track of the max and min x and y
408 -- values for all geometry objects in the column.
409 --
410 -- Syntax
411 -- AVERAGE_MBR (table_name, column_name, width, height)
412 --
413 -- Keywords and Paramters
414 -- table_name the name of the geometry object table. Datatype is VARCHAR2.
415 -- column_name the name of the geometry object column for which the
416 -- average MBR is to be calculated. Datatype is VARCHAR2.
417 -- width width of the average MBR. Datatype is OUT NUMBER.
418 -- height height of the average MBR. Datatype is OUT NUMBER.
419 --
420
421 PROCEDURE average_mbr (table_name VARCHAR2,
422 column_name VARCHAR2,
423 width OUT NUMBER,
424 height OUT NUMBER);
425
426
427 ------------------------------------------------------------------------------
428 -- Purpose
429 -- This procedure caclulates statistical histograms for a geometry object
430 -- column. Information from such histograms could be helpful to determine
431 -- proper spatial index type (fixed, variable, or hybrid) and index
432 -- parameters for the geometry column.
433 --
434 -- Syntax
435 -- HISTOGRAM_ANALYSIS (table_name, column_name, result_table,
436 -- type_of_histogram, max_value, intervals)
437 --
438 -- Keywords and Paramters
439 -- table_name the name of the geometry object table.
440 -- column_name the name of the geometry object column for which the
441 -- histogram is to be computed.
442 -- result_table result table name where the histogram is stored. User
446 -- CREATE TABLE histogram (value NUMBER, count NUMBER);
443 -- must create this table before this procedure is
444 -- called. This table contains two columnsof type
445 -- NUMBER: 'value' and 'count'. For an example,
447 -- type_of_histogram There are three types of histograms:
448 -- TILES_VS_LEVEL Number of tiles at different spatial index level.
449 -- This histogram is used to evaluate the spatial index
450 -- that is already built on the geometry column.
451 -- GEOMS_VS_TILES Number of geometries in different #tiles range.
452 -- This histogram is used to evaluate the spatial index
453 -- that is already built on the geometry column.
454 -- GEOMS_VS_AREA Number of geometries in different size range. The
455 -- shape of this histogram could be hepful for users
456 -- to choose a proper index type and index level.
457 -- GEOMS_VS_VERTICES A histogram of geometry count vs the number of
458 -- vertices (complexity) could tell users if spatial
459 -- index selectivity is important for the column. Since
460 -- the number of vertices determine the performance of
461 -- secondary filter (RELATE), selectivity of the primary
462 -- filter could be crucial for geometry column that
463 -- contains many complicated geometries.
464 -- max_value The upper limit of the histogram. i.e. The histogram
465 -- runs in range (0, max_value).
466 -- intervals Number of intervals between 0 and max_value.
467 --
468 /* DEPRECATED
469 PROCEDURE histogram_analysis (table_name VARCHAR2,
470 column_name VARCHAR2,
471 result_table VARCHAR2,
472 type_of_histogram VARCHAR2 := 'TILES_VS_LEVEL',
473 max_value NUMBER,
474 intervals INTEGER);
475 */
476
477
478 ------------------------------------------------------------------------------
479 -- Purpose
480 -- This procedure caclulates geometry type information for the geometry
481 -- object column. It outputs the number of geometries of different type,
482 -- as well as the percentages for various geometry types.
483 --
484 -- Syntax
485 -- MIX_INFO (table_name, column_name, total_geom, point_geom, curve_geom,
486 -- poly_geom, complex_geom)
487 --
488 -- Keywords and Paramters
489 -- table_name the name of the geometry object table. Datatype is VARCHAR2.
490 -- column_name the name of the geometry object column for which the
491 -- geometry type infomation is to be calculated. Datatype is
492 -- VARCHAR2.
493 -- total_geom the total number of geometries. Datatype is OUT NUMBER.
494 -- point_geom the number of point geometries. Datatype is OUT NUMBER.
495 -- curve_geom the number of curvestring geometries. Datatype is OUT NUMBER.
496 -- poly_geom the number of polygon geometries. Datatype is OUT NUMBER.
497 -- complex_geom the number of complex geometries. Datatype is OUT NUMBER.
498 --
499
500 PROCEDURE mix_info (table_name VARCHAR2,
501 column_name VARCHAR2,
502 total_geom OUT INTEGER,
503 point_geom OUT INTEGER,
504 curve_geom OUT INTEGER,
505 poly_geom OUT INTEGER,
506 complex_geom OUT INTEGER);
507
508
509 ------------------------------------------------------------------------------
510 -- Purpose
511 -- This procedure caclulates geometry type information for the geometry
512 -- object column. It prints out the number of geometries of different type,
513 -- as well as the percentages for various geometry types.
514 --
515 -- Syntax
516 -- MIX_INFO (table_name, column_name)
517 --
518 -- Keywords and Paramters
519 -- table_name the name of the geometry object table. Datatype is VARCHAR2.
520 -- column_name the name of the geometry object column for which the
521 -- geometry type infomation is to be calculated. Datatype is
522 -- VARCHAR2.
523 --
524
525 PROCEDURE mix_info (table_name VARCHAR2,
526 column_name VARCHAR2);
527
528
529 ------------------------------------------------------------------------------
530 -- Purpose
531 -- This function estimates tiling time for a geometry column. The estimate
532 -- is based on the tiling time of a small sample geometry table that is
533 -- automatically generated from the original table column. User must have
534 -- create-table privilege to call this function. The sample table will be
535 -- cleaned up before this funtion exits.
536 --
537 -- Syntax
538 -- ESTIMATE_TILING_TIME (table_name, column_name, sample_ratio,
539 -- tiling_level, num_tiles)
540 --
541 -- Keywords and Paramters
542 -- table_name The name of the geometry object table. Datatype is VARCHAR2.
543 -- column_name The name of the geometry object column for which the
544 -- tiling time is to be estimated. Datatype is VARCHAR2.
545 -- sample_ratio The size ratio between the original table and the sample
546 -- table (to be generated). Larger ratio means faster but
550 -- num_tiles The number of tiles for variable or hybrid tessellation.
547 -- less accurate estimation. Datatype is INTEGER.
548 -- tiling_level The spatial index level at which the geometry column is to
549 -- be tessellated. Datatype is INTEGER.
551 -- Should be 0 for fixed tessellation. Datatype is INTEGER.
552 --
553 -- Returns
554 -- This function returns a number representing estimated tiling time in
555 -- seconds, or 0 is returned on error.
556 --
557 /* DEPRECATING
558 FUNCTION estimate_tiling_time (table_name VARCHAR2,
559 column_name VARCHAR2,
560 sample_ratio INTEGER := 20,
561 tiling_level INTEGER,
562 num_tiles INTEGER := 0)
563 RETURN NUMBER;
564 */
565
566
567 ------------------------------------------------------------------------------
568 -- Purpose
569 -- This function estimates the total number of spatial tiles for a geometry
570 -- column. The estimate is based on the total number of tiles for a small
571 -- sample geometry table that is automatically generated from the original
572 -- table column. User must have create-table privilege to call this
573 -- function. The sample table will be cleaned up before this funtion exits.
574 --
575 -- Syntax
576 -- ESTIMATE_TOTAL_NUMTILES (table_name, column_name, sample_ratio,
577 -- tiling_level, num_tiles, num_largetiles)
578 --
579 -- Keywords and Paramters
580 -- table_name The name of the geometry object table. Datatype is
581 -- VARCHAR2.
582 -- column_name The name of the geometry object column for which the
583 -- tiling time is to be estimated. Datatype is VARCHAR2.
584 -- sample_ratio The size ratio between the original layer and the sample
585 -- layer (to be generated). Larger ratio means faster but
586 -- less accurate estimation. Datatype is INTEGER.
587 -- tiling_level The spatial index level at which the layer is to be
588 -- tessellated. Datatype is INTEGER.
589 -- num_tiles The number of tiles for variable or hybrid tessellation.
590 -- Should be 0 for fixed tessellation. Datatype is INTEGER.
591 -- num_largetiles An output parameter indicating how many spatial tiles
592 -- are of the same size as group tiles for hybrid indexing.
593 -- For fixed indexing, this value should always be the same
594 -- as the returned value (total number of spatial tiles).
595 -- Datatype is OUT INTEGER.
596 --
597 -- Returns
598 -- This function returns an integer representing estimated total number of
599 -- spatial tiles, or 0 is returned on error.
600 --
601 /* DEPRECATED
602 FUNCTION estimate_total_numtiles (table_name VARCHAR2,
603 column_name VARCHAR2,
604 sample_ratio INTEGER := 20,
605 tiling_level INTEGER,
606 num_tiles INTEGER := 0,
607 num_largetiles OUT INTEGER)
608 RETURN INTEGER;
609 */
610
611 ------------------------------------------------------------------------------
612 -- Purpose
613 -- This function estimates spatial index performance such as query
614 -- selectivity and window query time for a geometry column. The estimate
615 -- is based on the window query time of a small sample geometry table that
616 -- is automatically generated from the original table column. User must
617 -- have create-table privilege to call this function. The sample table will
618 -- be cleaned up before this funtion exits.
619 --
620 -- Syntax
621 -- ESTIMATE_INDEX_PERFORMANCE (table_name, column_name, sample_ratio,
622 -- tiling_level, num_tiles, window_obj,
623 -- tiling_time, filter_time, query_time)
624 --
625 -- Keywords and Paramters
626 -- table_name The name of the geometry object table. Datatype is VARCHAR2.
627 -- column_name The name of the geometry object column for which the
628 -- tiling time is to be estimated. Datatype is VARCHAR2.
629 -- sample_ratio The size ratio between the original table and the sample
630 -- table (to be generated). Larger ratio means faster but
631 -- less accurate estimation. Datatype is INTEGER.
632 -- tiling_level The spatial index level at which the geometry column is to
633 -- be tessellated. Datatype is INTEGER.
634 -- num_tiles The number of tiles for variable or hybrid tessellation.
635 -- Should be 0 for fixed tessellation. Datatype is INTEGER.
636 -- window_obj The window geometry object. Datatype is MDSYS.SDO_GEOMETRY.
637 -- tiling_time The estimated tiling time in seconds. Datatype is OUT
638 -- NUMBER.
639 -- filter_time The estimated spatial index filter time in seconds.
640 -- Datatype is OUT NUMBER.
641 -- query_time The estimated window query time in seconds. Datatype is
642 -- OUT NUMBER.
643 --
644 -- Returns
645 -- This function returns a number between 0.0 and 1.0 representing estimated
646 -- spatial index selectivity (large number means better selectivity, and 0.0
647 -- implies an error).
648 --
649 /* DEPRECATED
653 tiling_level INTEGER,
650 FUNCTION estimate_index_performance (table_name VARCHAR2,
651 column_name VARCHAR2,
652 sample_ratio INTEGER := 20,
654 num_tiles INTEGER := 0,
655 window_obj MDSYS.SDO_GEOMETRY,
656 tiling_time OUT NUMBER,
657 filter_time OUT NUMBER,
658 query_time OUT NUMBER)
659 RETURN NUMBER;
660 */
661
662 ------------------------------------------------------------------------------
663 -- Purpose
664 -- This function estimates the quality_degradation of an R-tree index.
665 -- This computes the ratio of the current quality and the quality
666 -- at the time when the R-tree is last built/rebuilt.
667 -- This number gives an estimate of how much the query performance
668 -- degrades (i.e., how slow queries become) in comparison to
669 -- queries at index creation time. The quality could have
670 -- degraded due to updates between tree-creation time and
671 -- current time.
672 --
673 -- The quality of an R-tree is estimated as the ratio of the
674 -- sum of the areas of all index_level mbrs to the ((sum of the area
675 -- of all mbrs at leaf level, i.e., data mbrs) times the height
676 -- of the tree.
677 --
678 -- Syntax
679 -- QUALITY_DEGRADATAION(schema_name, index_name, index_table_name)
680 --
681 -- Keywords and Paramters
682 -- schema_name The name of the user's schema. Datatype is VARCHAR2.
683 -- index_name The name of the index to be analyzed. Datatype is VARCHAR2.
684 -- index_table The name of the R-tree index table. The R-tree can
685 -- be either a primary or a secondary index for
686 -- the specified index_name. Datatype is VARCHAR2.
687 --
688 -- Returns
689 -- This function returns a number that represents the degradation
690 -- factor (small number close to 1 means index is very efficient;
691 -- large numbers may mean index-quality has degraded; rebuilding
692 -- the index may improve the quality
693
694 FUNCTION quality_degradation(schemaname varchar2,
695 indexname varchar2,
696 indextable varchar2)
697 RETURN number;
698
699 ------------------------------------------------------------------------------
700 -- Purpose
701 -- Same as above but indextable is not specified. Whole index is analyzed.
702
703 FUNCTION quality_degradation(schemaname varchar2,
704 indexname varchar2)
705 RETURN number;
706
707
708 ------------------------------------------------------------------------------
709 -- Purpose
710 -- This procedure prints the quality for a specified R-tree index
711 --
712 -- Syntax
713 -- RTREE_QUALITY(schema_name, index_name, index_table_name)
714 --
715 -- Keywords and Paramters
716 -- schema_name The name of the user's schema.
717 -- Datatype is VARCHAR2.
718 --
719 -- index_name The name of the index to be analyzed.
720 -- Datatype is VARCHAR2.
721 --
722 -- index_table The specific R-tree index for the index.
723 -- Datatype is VARCHAR2.
724 --
725
726
727 PROCEDURE analyze_rtree(schemaname varchar2,
728 indexname varchar2);
729
730 ------------------------------------------------------------------------------
731 -- Purpose
732 -- This procedure prints just the quality for a specified R-tree index
733 -- (does not compare with the previous quality in the metadata)
734 --
735 -- Syntax
736 -- RTREE_QUALITY(schema_name, index_name, index_table_name)
737 --
738 -- Keywords and Paramters
739 -- schema_name The name of the user's schema.
740 -- Datatype is VARCHAR2.
741 --
742 -- index_name The name of the index to be analyzed.
743 -- Datatype is VARCHAR2.
744 --
745 -- index_table The specific R-tree index for the index.
746 -- Datatype is VARCHAR2.
747 --
748
749 FUNCTION rtree_quality(schemaname varchar2,
750 indexname varchar2,
751 indextable varchar2)
752 RETURN number;
753
754 ------------------------------------------------------------------------------
755 -- Purpose
756 -- Same as above but indextable is not specified. Whole index is analyzed.
757
758 FUNCTION rtree_quality(schemaname varchar2,
759 indexname varchar2)
760 RETURN number;
761
762
763 ---------------------------------------------------------------------------
764 -- Purpose
765 --
766 -- Function to estimate size of an R-tree index (table) in MB.
767 -- (tablespace needed at creation times is 3 times this size)
768 --
769 -- Syntax
770 --
771 -- estimate_rtree_index_size(number_of_geoms, db_block_size,
772 -- sdo_rtr_pctfree, num_dimensions, is_geodetic)
773 --
774 -- Keywords and Parameters:
775 --
776 -- number_of_geoms - number of non-null geometries to be indexed
777 -- db_block_size - block size of the underlying tablespace/database
778 -- sdo_rtr_pctfree - the percentage of space to be left empty in each node.
779 -- This empty space can be consumed by subsequent inserts.
780 -- If unspecified, the default in Oracle Spatial is 10
781 -- num_dimensions - the number of dimensions in the
782 -- data - NOTE: if geodetic, use 3
783 --
784 -- Returns: Size in MB.
785
786 FUNCTION estimate_rtree_index_size (number_of_geoms integer,
787 db_block_size integer,
788 sdo_rtr_pctfree integer default 10,
789 num_dimensions integer default 2,
790 is_geodetic number default 0)
791 RETURN number;
792
793 ---------------------------------------------------------------------------
794 -- Purpose
795 --
796 -- Function to approximately compute R-tree index size (in MB)
797 --
798 -- Syntax
799 --
800 -- estimate_rtree_index_size(schema, tabname, colname, partname)
801 --
802 -- Keywords and Parameters:
803 --
804 -- schema - user's schema
805 -- tabname - table on which R-tree index needs to be constructed
806 -- colname - column on which R-tree index needs to be constructed
807 -- partname - name of partition for which to build the index
808 --
809 -- Returns: Size in MB
810
811 FUNCTION estimate_rtree_index_size (schema varchar2,
812 tabname varchar2,
813 colname varchar2,
814 partname varchar2 default null)
815 RETURN number;
816
817 END sdo_tune;
[Home] [Help]