In a previous article I showed how too many digits of precision for the ordinates of an sdo_geometry object – stored in its sdo_ordinate_array – has an effect on storage volume.
In this article I will revisit the question of storage size while examining the effect that storage size has on performance (search and retrieval).
Approach – MultiPoint Sdo_Geometry Objects
The simplest way to create an experimental framework where one wants to examine sdo_ordinate_array ordinate precision on storage and performance effects, is to base one’s experiments on the generation and use of MultiPoint geometry objects as it is easy to generate valid sdo_geometry object of a specific size.
The generation of the multipoint objects is in two steps:
The cells of a standard sized rectangular grid is created in memory
For each cell in the grid a single multipoint geometry is created containing a variable number of randomly generated ordinates.
After creation of the multipoints, the following occurs:
Space usage of table and LOBs is computed
SDO_GEOM Metadata and an RTree Index is created
Performance statistics are generated using a function called RandomSearchByExtent
Next the tests are repeated but against an ordinate rounded version of the MultiPoint geometry data just created via:
CREATETABLE &MPTableName.R (
ID NUMBER(10,0),
GEOM MDSYS.SDO_GEOMETRY
) NOLOGGING;
-- Create points by rounding the ordinates of the first table
--
INSERT/*+append*/INTO &MPTableName.R (ID,GEOM)
SELECT ID,CODESYS.ROUNDORDINATES(p.GEOM,2)
FROM &MPTableName. p;
COMMIT;
ALTERTABLE &MPTableName.R LOGGING;
All these steps have been put into a single SQL file called MultiPoint.sql.
Running the Tests
A set of tests are created which test multipoint geometries with a variety of sizes. Each test calls the single SQL file described above.
@multipoint MP_250_O 250
@multipoint MP_500_O 500
@multipoint MP_1000_O 1000
@multipoint MP_2500_O 2500
@multipoint MP_5000_O 5000
@multipoint MP_7500_O 7500
@multipoint MP_10000_O 10000
@multipoint MP_50000_O 50000
@multipoint MP_100000_O 100000
After running the tests, the output CSV data was loaded into a spreadsheet and processed to create two graphs.
Graph One: Storage before and After Rounding
As can be seen, there is the potential for large savings in storage.
Graph Two: Percentage Performance Improvement After Rounding
Except for a few “outlying” results (250, 2500 and 5000), the results are encouraging. The effect on performance of ordinate rounding is muted for sdo_geometry objects with small vertex counts, but there appears to be a solid effect for larger objects that indicates that ordinate rounding could be considered “best practice” in spatial data management.
SpatialDB Advisor
Home
Comment