St_geometry vs SDEBinary storage requirements in Oracle

3675
4
Jump to solution
07-15-2015 08:36 AM
TeresaVillena
New Contributor III

Does any body knows the st_geometry storage requeriments vs sdeBinary storage requirements in Oracle?

Regards,

Teresa V.

0 Kudos
1 Solution

Accepted Solutions
VinceAngelo
Esri Esteemed Contributor

In theory, the LONG RAW storage used with SDEBINARY should be about the same as the BLOB space used by ST_GEOMETRY, except:

  1. The storage requirements are in different tables (and, potentially, tablespaces)
  2. LOBs have the option of both inline and out-of-row storage (and the out of row storage can be targeted to a different tablespace)
  3. ST_GEOMETRY always uses HIGH precision, so it will be larger than an equivalent shape stored in BASIC (or HYBRID) precision
  4. Modern default coordinate references use a precision which significantly increases storage over data stored in earlier-generated geometries (higher precision increases the magnitude of the vertex deltas, which increases the bits needed to store vertices)
  5. In-situ migration requires significant additional storage (which is preserved by the table high-water mark), and is likely to fragment the table (increasing storage and hurting query performance.

Given the state of modern disk storage, you could probably store ten times the previous vector data in under $120 of disk (and it might have a faster seek time), so shoehorning datasets into disk shouldn't be a major concern.

Best practice would be create a new table in a new tablespace (tablespaces, if you consider indexes), taking the opportunity to spatially defragment the table(s) by using the Sort (Data Management) tool to optimize the draw performance by using a UL spatial sort option (Advanced [ArcInfo] license only) when populating  the new table (Standard and Basic licenses would need to use a spatially correlated attribute, like zipcode, or mgrs, or geohash, or an index grid id to achieve spatial defragmentation).

- V

View solution in original post

4 Replies
SamuelTompsett
Occasional Contributor II

Hi Teresa,

Could you be more specific on what you are looking for when you say "storage" requirements?

Thanks,
Sam

0 Kudos
TeresaVillena
New Contributor III

Hi Sam,

I want to know if I will need more storage  space in Oracle when I migrate from sdeBinary to st_geometry

Thanks,

Teresa

0 Kudos
VinceAngelo
Esri Esteemed Contributor

In theory, the LONG RAW storage used with SDEBINARY should be about the same as the BLOB space used by ST_GEOMETRY, except:

  1. The storage requirements are in different tables (and, potentially, tablespaces)
  2. LOBs have the option of both inline and out-of-row storage (and the out of row storage can be targeted to a different tablespace)
  3. ST_GEOMETRY always uses HIGH precision, so it will be larger than an equivalent shape stored in BASIC (or HYBRID) precision
  4. Modern default coordinate references use a precision which significantly increases storage over data stored in earlier-generated geometries (higher precision increases the magnitude of the vertex deltas, which increases the bits needed to store vertices)
  5. In-situ migration requires significant additional storage (which is preserved by the table high-water mark), and is likely to fragment the table (increasing storage and hurting query performance.

Given the state of modern disk storage, you could probably store ten times the previous vector data in under $120 of disk (and it might have a faster seek time), so shoehorning datasets into disk shouldn't be a major concern.

Best practice would be create a new table in a new tablespace (tablespaces, if you consider indexes), taking the opportunity to spatially defragment the table(s) by using the Sort (Data Management) tool to optimize the draw performance by using a UL spatial sort option (Advanced [ArcInfo] license only) when populating  the new table (Standard and Basic licenses would need to use a spatially correlated attribute, like zipcode, or mgrs, or geohash, or an index grid id to achieve spatial defragmentation).

- V

TeresaVillena
New Contributor III

Thanks a lot for your help

0 Kudos