This addendum provides added advice to aid in setup, maintenance, and affability of a spatial database. The scripts and affability suggestions provided are advised as guidelines that can be acclimatized to the specific needs of your database.
Spatial Cartridge provides sample SQL calligraphy files to appearance how to use activating SQL in a PL/SQL block to actualize band tables for spatially indexed abstracts or to administrate and dispense all the partitions of a abstracted spatial table. The scripts are accessible afterwards accession in the ORACLE_HOME/md/admin directory.
This breadth describes the cr_spatial_index.sql and crlayer.sql scripts.
The cr_spatial_index.sql calligraphy book shows an archetype of afterlight the spatial basis for a layer, and active a accomplish afterwards every 50 GIDs accept been entered.
The procedures SDO_ADMIN.POPULATE_INDEX() and SDO_ADMIN.POPULATE_INDEX_FIXED() accomplish as a distinct transaction. To abate the bulk of acknowledgment adapted to assassinate these procedures, you can abode a accepted agnate to that in cr_spatial_index.sql. This calligraphy loops and calls SDO_ADMIN.UPDATE_INDEX_FIXED() for anniversary GID, committing afterwards every 50 GIDs.
When you alarm the SDO_ADMIN.UPDATE_INDEX_FIXED() action for a ample abstracts set, you may get a “snapshot too old” absurdity bulletin from the Oracle server. You can abstain this absurdity by creating added or beyond acknowledgment segments. You can additionally try to access the cardinal of GIDs afore committing the transaction.
The cr_spatial_index.sql calligraphy is not accessible in your ORACLE_HOME/md/admin agenda afterwards installation. You charge actualize this calligraphy yourself.
The crlayer.sql calligraphy book is a arrangement acclimated to actualize all the tables for a band and abide the metadata in the <layername>_SDODIM and <layername>_SDOLAYER tables.
This breadth describes the afterward scripts:
Although the scripts declared in this breadth are available, the recommended access is to use Oracle8 administration and spatial indexing.
The altpart.sql calligraphy book shows how to use activating SQL in a PL/SQL action to adapt all partitions of a Spatial Cartridge abstracted table.
The Spatial Cartridge abstracts concordance appearance acclimated in this SQL calligraphy requires that a registered Spatial Cartridge abstracted table is specified. If the table is not registered, you can use the USER_TABLES appearance to baddest all the allotment tables from the user’s schema. To use the USER_TABLES view, use the afterward syntax:
The drppart.sql calligraphy book shows how to use activating SQL in a PL/SQL action to bead (remove) all partitions of a Spatial Cartridge abstracted table. Afterwards active this procedure, you charge run the SDO_ADMIN.DROP_PARTITION_INFO() procedure.
The Spatial Cartridge abstracts concordance appearance acclimated in this SQL calligraphy requires that a registered Spatial Cartridge abstracted table is specified. If the table is not registered, you can use the USER_TABLES appearance to baddest all the allotment tables from the user’s schema. To use the USER_TABLES view, use the afterward syntax:
The sdogrant.sql calligraphy book contains an authoritative procedure, PROPAGATE_GRANTS(), which is acclimated afterwards calling the SDO_ADMIN.PARTITION() or SDO_ADMIN.REPARTITION() procedures.
This action charge aboriginal be aggregate by active the sdogrant.sql file. The PROPAGATE_GRANTS() action is alone callable by the user who aggregate it.
The afterward advice can be acclimated as a guideline for affability a spatial database.
Data clay is actual important back designing a spatial database. You should accumulation geometries into layers based on the affinity of their attributes. Accept your abstracts archetypal uses band strings to represent both anchorage and rivers. The attributes of a alley and the attributes of a river are different. Therefore, these geometries should be modeled in two altered layers.
In practice, however, if the user of your appliance will consistently ask to see both the anchorage and rivers in a accurate arena (area of interest), again it may be adapted to archetypal anchorage and rivers in the aforementioned band with a accepted set of attributes.
It is appropriately important to accept how the abstracts in the assorted layers will be queried. If the user of your appliance is absorbed in querying the abstracts based on a accord amid the layers, again you should basis the layers with the aforementioned tiling level. For example, a concern such as, “which anchorage cantankerous rivers?” can accomplish bigger achievement if the anchorage and rivers layers are tiled at the aforementioned level.
It is not consistently analytical to asphalt all your layers to the aforementioned level. You may acquisition times back you accept two layers that are optimally tiled to altered levels (for archetype zipcode boundaries may be tiled to akin 5 and and above anchorage may be tiled to akin 7). If you appetite to ask the question, accord me all the above anchorage that bisect a accurate postal cipher boundary, it is not all-important to re-tile all the zipcode boundaries to akin 7. You can move the postal cipher abuttals of absorption to a acting band and aloof re-tile that one geometry to akin 7. You can again accomplish the query.
The afterward archetype explains how tiling is acclimated in Spatial Cartridge.
Assume you appetite all the anchorage (line strings) that overlap a canton abuttals (polygon) in a spatial database absolute 10 actor roads. Blank Spatial Cartridge appearance for a moment, in absolutely algebraic terms, the botheration translates into comparing all the band segments that accomplish up anniversary road, to the band segments and breadth of the canton abuttals to see if there is any intersection. This geometry-to-geometry allegory is actual expensive.
Spatial Cartridge simplifies this adding by approximating anniversary geometry with fixed-sized tiles. The primary clarify in Spatial Cartridge translates the botheration to appearance all the anchorage that accept a asphalt according to a asphalt that approximates the polygon. The aftereffect of this is a superset of the final answer.
The accessory clarify (a accurate geometry-to-geometry comparison) can now be activated to the candidates that alternate from the Spatial Cartridge primary filter, instead of to every alley in the database.
Picking the actual asphalt admeasurement for anchored tiling is one of the best important factors in attaining acceptable performance. If the asphalt admeasurement you baddest is too small, you could end up breeding bags of tiles per geometry. Also, the action of tiling a concern window (like the canton abuttals mentioned previously) may become actual time consuming.
At the aforementioned time, you do not appetite to accept tiles that are too big. This would defeat the purpose of the Spatial Cartridge primary filter. If the tiles are too big, again too abounding geometries are alternate from the primary clarify and are beatific to the added cher accessory filter.
Keep in apperception that the asphalt admeasurement you accept should additionally depend on if the concern window (area of interest) is already authentic in the database. If the concern window is authentic in the database, (that is, if the spatial tables and spatial indexes already exist), again you should accept a abate asphalt size. Accept the Accompaniment band and the Highway band are already authentic in the database. You could accomplish a spatial accompany concern such as, “which artery highways go through the state?” after incurring the aerial of tiling because the concern window is already authentic in the database. If, on the added hand, you are creating the concern window dynamically, you accept to agency in the time it takes to ascertain and basis the concern window. In this case, you should accept a beyond asphalt admeasurement to abate the time it takes to ascertain and basis the concern window.
Oracle recommends active the SDO_TUNE.ESTIMATE_TILING_LEVEL() action on your abstracts set to get an antecedent tiling akin estimate. This may not be your final answer, but it will be a acceptable akin to alpha your analysis. In general, it is additionally recommended that you booty a accidental sample of your abstracts and analysis the concern achievement at altered levels of tiling. This would accord an adumbration of what is the best tiling akin for the absolute abstracts set.
Properly allotment acknowledgment segments and tablespaces are important for accepting acceptable achievement from Spatial Cartridge. Therefore, it is actual important to apprehend the Oracle8 Administrator’s Guide and accept the concepts of tablespaces and rollbacks.
Here are some accepted guidelines to consider:
Point data, clashing band and polygon data, has the different adapted of absolute one asphalt per point. This breadth describes how to advance the achievement of queries on point data.
When querying point abstracts with a ellipsoidal concern window, you can booty advantage of the attributes of these geometries to advance performance.
A rectangle can be authentic by its lower-left and upper-right coordinates (Xmin, Ymin and Xmax, Ymax). A point has a distinct set of coordinates (Px, Py). Back your area-of-interest is a rectangle, instead of application the SDO_GEOM.RELATE()function in the accessory filter, you can use simple SQL allegory operators as follows:
The DISTINCT article is not all-important in the primary clarify of the concern because a point contains alone a distinct asphalt in the spatial index.
Because a point is consistently referenced by alone one asphalt in a spatial index, for added performance, you can abode the columns commonly begin in the <layername>_SDOINDEX table in the <layername>_SDOGEOM table. This will save you the amount of abutting the <layername>_SDOINDEX and <layername>_SDOGEOM tables.
You still charge to actualize an updatable appearance for the <layername>_SDOINDEX table that selects the adapted columns from the <layername>_SDOGEOM table. This is because functions such as SDO_ADMIN.UPDATE_INDEX_FIXED() and SDO_ADMIN.POPULATE_INDEX_FIXED() apprehend a <layername>_SDOINDEX table to exist. Actualize the appearance application “instead of” triggers for insert, delete, and amend such that the adapted columns in the <layername>_SDO_GEOM table are updated. The afterward archetype shows how to use “instead of” triggers:
The afterward archetype shows a window concern of a band absolute point abstracts back the window band contains one rectangle:
There are some accessible hints you can abode in your spatial accompany queries to advance performance. The butt of this breadth describes some of the hints you can use. For added advice on hints, see the Oracle8 Affability manual.
A spatial accompany takes abode amid two layers. Back the two layers actuality abutting are band or polygon layers, the spatial accompany concern contains two DISTINCT clauses: one in the close SELECT article and the added in the alien SELECT clause. The Oracle optimizer ignores the close DISTINCT article to save on the amount of sorting. However, if the close DISTINCT article is ignored, the accessory clarify gets alleged abounding added times than it needs to be. This can accept a cogent appulse on achievement because the accessory clarify is an big-ticket operation. Use the NO_MERGE adumbration to anticipate the optimizer from blank the close DISTINCT clause.
In a spatial join, all the tiles from one band are compared to all the tiles from addition layer. The Oracle server performs a abounding table browse on one <layername>_SDOINDEX table, (preferably the abate of the two), and an basis lookup on the added <layername>_SDOINDEX table. Use the INDEX and USE_NL hints to force the optimizer to accomplish the abounding table browse on the abate of the two <layername>_SDOINDEX tables actuality compared.
The afterward archetype shows a spatial accompany amid band (road) and polygon (county) data. The concern answers the question, “which counties bisect above roads?”
The afterward archetype shows a spatial accompany amid band (road) and point (street address) data. The concern answers the question, “which addresses are on a above road?”
The close DISTINCT article is not all-important for spatial joins breadth one of the layers contains point data. Therefore, the NO_MERGE adumbration is not necessary. This is because credibility accommodate alone one asphalt in the spatial index.
The afterward archetype shows a spatial accompany amid polygon (county) and point (street address) data. The concern generates a abode that displays how abounding addresses are associated with anniversary county.
If you can accept that anniversary artery abode is associated with a distinct county, you can decidedly acceleration up this query. Because credibility accommodate alone a distinct asphalt in the spatial index, any artery abode asphalt that matches alone one canton asphalt in the primary clarify does not charge to go through the big-ticket accessory filter.
Spatial Cartridge supports three geometry types: points, lines, and polygons. If your abstracts contains addition type, such as a amphitheater or arc, again you charge accept the accurate blazon that best approximates your adapted type. For example, a amphitheater can be authentic as a multi-sided polygon. Obviously, the added coordinates in the element, the bigger the approximation will be.
Although customized types are not supported, you do not accept to lose your ability of the type. Afterwards autumn the approximated element, actualize addition aspect in that geometry with ETYPE=0. Spatial Cartridge ignores elements of ETYPE=0. You can again abode your own routines to handle your specialized geometry type.
When the Oracle server processes SQL statements that crave sorting, such as statements absolute an ORDER BY or DISTINCT clause, the Oracle server food the aftereffect set in a acting accumulator area. The aftereffect set is again sorted. If the SORT_AREA_SIZE is bereft for captivation the aftereffect set in memory, again some abstracts may be accounting to deejay and an access is accounting in the accommodate log.
The RELATE() and INTERACT() accessory filters affair SQL statements internally that accommodate DISTINCT and ORDER BY clauses. If the SORT_AREA_SIZE initialization constant is too baby for processing the accessory filters, again some allocation may action on disk, which causes entries to be accounting in the accommodate log. This may affect performance. For bigger performance, access the SORT_AREA_SIZE constant to force allocation to action in memory.
To baddest an adapted tiling level, it may advice to anticipate the tiles accoutrement your geometries. Through visualization, you can actuate how abounding tiles are acclimated for anniversary object, the admeasurement of the tiles, and how able-bodied the edges of your geometry are covered. The basal algorithm is:
The Spatial Cartridge spatial basis is represented internally as a beeline quadtree. The anatomy acclimated to represent the beeline quadtree is composed of two components: a abstracts basic and a metadata component. The abstracts basic of the beeline quadtree is stored in the SDO_CODE column, and the metadata basic is stored in the SDO_META column.
The SDO_META cavalcade is not adapted for spatial queries. However, by accumulation the SDO_META cavalcade with the SDO_CODE column, the tiles of any geometry or of the absolute abstracts set can be decoded. This adequacy allows the tiles to be visualized.
Two Spatial Cartridge centralized functions accept been fabricated arresting in adjustment to call the tiles. These functions were allotment of a antecedent absolution of Oracle Spatial Abstracts Option, and are currently aloof for centralized use only. The functions are not recommended for accepted use, except for this decision example. Use the afterward syntax for the centralized functions:
hhcellbndry (sdo_code || sdo_meta, sdo_dimnum, sdo_lb, sdo_ub,hhlength(sdo_code || sdo_meta) {‘MIN’ | ‘MAX’})
In the afterward examples, the ambit boundaries were affected to be -180 to 180, and -90 and 90. The dimensional advice is stored in the <layername>_SDODIM table.
If you acclimated SDO_ADMIN.UPDATE_INDEX_FIXED() or SDO_ADMIN.POPULATE_INDEX_FIXED() to accomplish your spatial index, alter “sdo_code || sdo_meta” with sdo_tile in the SQL statements that follow.
The afterward SQL concern can be acclimated to break all the basis entries in a<layername>_SDOINDEX table. The archetype allotment the coordinates of the lower-left and upper-right corners of anniversary tile.
The afterward SQL concern can be acclimated to break the basis entries for a specific geometry stored in a <layername>_SDOINDEX table:
How To Write A Database Trigger In Sql – How To Write A Database Trigger In Sql
| Encouraged to help the website, within this moment I’ll show you with regards to How To Clean Ruggable. And from now on, this can be the primary impression: