Tuesday Jul 29, 2014

How much space do your SDO_GEOMETRY tables consume?

 By Karin Patenge, Principle Sales Consultant, Oracle Germany

I would like to briefly offer a simple function to calculate the memory allocated for SDO_GEOMETRY tables. This takes into account only tables, LOB and index segments (this is not applicable for partitioned tables).


create or replace function alloc_space_in_mbytes (tablename in varchar2)
return number 
is
table size number;
begin
  select 
    sum (bytes) into table size
  from (
    select 
      segment_name, 
      bytes  
    from 
      USER_SEGMENTS - table segments             
    where  
      segment_name = tablename
    union all
    select 
      s.segment_name segment_name, 
      s.bytes bytes
    from 
      user_lobs l, - Lobsegmente
      USER_SEGMENTS s
    where 
      l.table_name = tablename and
      s.segment_name = l.segment_name
    union all
    select 
      s.segment_name segment_name, 
      s.bytes bytes
    from 
      USER_INDEXES i, - index segments
      USER_SEGMENTS s
    where 
      i.table_name = tablename and
      s.segment_name = i.index_name);

  table size: = tablesize/1024/1024; - Conversion in MB
  return table size;
end;
/
The call to the function can then look like this:
select
  alloc_space_in_mbytes ('GEOM_TABLE_UNTRIMMED') untrimmed, 
  alloc_space_in_mbytes ('GEOM_TABLE_TRIMMED') trimmed
from dual
/
A test table with 218 237 polygons, a total of 60,754,462 bases and existing spatial index result in the following values:

SDO_ORDINATE_ARRAY values with predominantly 12 to 13 decimal places: 1643.5 MB
SDO_ORDINATE_ARRAY values truncated to 5 decimal places: 1033.5 MB

About

The official blog for the spatial features of Oracle Spatial and Graph, an option of Oracle Database - brought to you by the product managers and developers. Get technical tips, product information, and the latest news here. Visit our official product website at http://www.oracle.com/technetwork/database/options/spatialandgraph/overview/spatialfeatures-1902020.html

Search

Categories
Archives
« July 2014 »
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
30
31
  
       
Today