7 Replies Latest reply: Jul 15, 2014 8:45 AM by Stefan Jager RSS

    How to get Size of Spatial Index

    885201

      Hi,

       

      I know, when you google how to get the size of an index you get a lot of results, but nothing is working for me.

      Can someone please tell me how I can get the size of a Spatial Index ?

       

      Ciao, Andre

        • 1. Re: How to get Size of Spatial Index
          Paul Dziemiela

          Hi Andre,

           

          For the vast majority of humanity, disk is cheap and thus I have not seen any particular effort to expose such minutia by Oracle.  So just pointing out before you go down this rabbit hole that it's not unreasonable to push back on the folks asking you for such trifles as the effort to produce it may cost more than just buying another disk.  But we can indeed pick apart a domain index and measure each part.  That is what I do in order to provide a monthly breakdown of the storage costs of each data set I manage.

           

          So you can use my code below to obtain reasonable numbers.  You need to have select privileges on dba_segments and of course select privileges on the index to measure.  This was written in Python probably five years ago and then ported to PLSQL with much of the good stuff left behind.  The original Python code had the ability to show in detail each sub object being measured.  This on the other hand just plops out a number.   But I think a number is what you what.

           

          So just try

           

          SELECT dz_dba_sizer.get_domain_index_size(

              p_domain_index_owner => 'FOOOWNER'

             ,p_domain_index_name  => 'FOOINDEX'  

          )

          FROM dual;

           

          and you should get your results in bytes.  This should work for ESRI's SDE.ST_SPATIAL_INDEX and full text domain indexes as well though I have not tested those in a while.  Note that the owner of a domain index type may change the internal structures, table naming conventions, metadata tables, etc at any time so code like this is always on the edge of breaking.  I was most concerned at the time with the total cost in storage for a given feature class as represented by a table.  So this meant measuring the table itself, all it's lobs and lob indexes holding spatial data, the regular indexes and then domain indexes (parts of which have their own indexes).  There are all kinds of got-chas that I may well be missing, send them along if you see them.   

           

          Cheers,

          Paul

           

          CREATE OR REPLACE PACKAGE dz_dba_sizer

          AUTHID CURRENT_USER

          AS

           

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION get_table_size(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2     

             ) RETURN NUMBER;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION get_object_size(

                 p_segment_owner      IN  VARCHAR2 DEFAULT NULL

                ,p_segment_name       IN  VARCHAR2     

             ) RETURN NUMBER;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             PROCEDURE get_table_indexes(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2

                ,p_domain_flag        IN  BOOLEAN DEFAULT NULL

                ,p_index_owners       OUT MDSYS.SDO_STRING2_ARRAY

                ,p_index_names        OUT MDSYS.SDO_STRING2_ARRAY  

             );

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION get_domain_index_size(

                 p_domain_index_owner IN VARCHAR2 DEFAULT NULL

                ,p_domain_index_name  IN  VARCHAR2     

             ) RETURN NUMBER;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION get_table_lob_size(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2     

             ) RETURN NUMBER;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION is_complex_object_table(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2     

             ) RETURN VARCHAR2;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             PROCEDURE get_sdo_georaster_tables(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2

                ,p_table_owners       OUT MDSYS.SDO_STRING2_ARRAY

                ,p_table_names        OUT MDSYS.SDO_STRING2_ARRAY  

             );

           

          END dz_dba_sizer;

          /

           

          CREATE OR REPLACE PACKAGE BODY dz_dba_sizer

          AS

           

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION get_table_size(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2     

             ) RETURN NUMBER

             AS

                str_table_owner  VARCHAR2(30) := p_table_owner;

                num_table_size   NUMBER := 0;

                num_check        NUMBER := 0;

                ary_owners       MDSYS.SDO_STRING2_ARRAY := MDSYS.SDO_STRING2_ARRAY();

                ary_names        MDSYS.SDO_STRING2_ARRAY := MDSYS.SDO_STRING2_ARRAY();

                     

             BEGIN

            

                IF str_table_owner IS NULL

                THEN

                   str_table_owner := USER;

               

                END IF;

               

                SELECT

                COUNT(*)

                INTO

                num_check

                FROM

                all_tables a

                WHERE

                    a.owner = str_table_owner

                AND a.table_name = p_table_name;

               

                IF num_check <> 1

                THEN

                    RAISE_APPLICATION_ERROR(-20001,'table not found');

                   

                END IF;

               

                -- First get table size alone

                num_table_size := num_table_size + get_object_size(

                    p_segment_owner => str_table_owner

                   ,p_segment_name  => p_table_name    

                );

               

                -- Second, get the table LOBs

                num_table_size := num_table_size + get_table_lob_size(

                    p_table_owner => str_table_owner

                   ,p_table_name  => p_table_name    

                );

               

                -- Third, get size of all nondomain indexes

                get_table_indexes(

                    p_table_owner   => str_table_owner

                   ,p_table_name    => p_table_name

                   ,p_domain_flag   => FALSE

                   ,p_index_owners  => ary_owners

                   ,p_index_names   => ary_names  

                );

               

                FOR i IN 1 .. ary_names.COUNT

                LOOP

                   num_table_size := num_table_size + get_object_size(

                       p_segment_owner => ary_owners(i)

                      ,p_segment_name  => ary_names(i)   

                   );

                  

                END LOOP;

               

                -- Fourth, get size of all domain indexes

                get_table_indexes(

                    p_table_owner   => str_table_owner

                   ,p_table_name    => p_table_name

                   ,p_domain_flag   => TRUE

                   ,p_index_owners  => ary_owners

                   ,p_index_names   => ary_names  

                );

               

                FOR i IN 1 .. ary_names.COUNT

                LOOP

                   num_table_size := num_table_size + get_domain_index_size(

                       p_domain_index_owner => ary_owners(i)

                      ,p_domain_index_name  => ary_names(i)   

                   );

                  

                END LOOP;

               

                -- Fifth, I once had code to determine SDELOB size, now removed

               

                -- Sixth, check if table is complex object table, SDO_GEORASTER is the only type for now

                IF is_complex_object_table(

                    p_table_owner  => str_table_owner

                   ,p_table_name   => p_table_name     

                ) = 'SDO_GEORASTER'

                THEN

                   get_sdo_georaster_tables(

                       p_table_owner  => str_table_owner

                      ,p_table_name   => p_table_name

                      ,p_table_owners => ary_owners

                      ,p_table_names  => ary_names 

                   );

                  

                   FOR i IN 1 .. ary_names.COUNT

                   LOOP

                      num_table_size := num_table_size + get_table_size(

                          p_table_owner => ary_owners(i)

                         ,p_table_name  => ary_names(i)   

                      );

                     

                   END LOOP;

                  

                END IF;

               

                RETURN num_table_size;

            

             END get_table_size;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION get_object_size(

                 p_segment_owner      IN  VARCHAR2 DEFAULT NULL

                ,p_segment_name       IN  VARCHAR2     

             ) RETURN NUMBER

             AS

                str_segment_owner   VARCHAR2(30) := p_segment_owner;

                str_iot_type        VARCHAR2(255);

                str_owner           VARCHAR2(30);

                str_index_name      VARCHAR2(30);

                num_bytes           NUMBER;

                str_tablespace_name VARCHAR2(30);

                str_segment_type    VARCHAR2(255);

                num_rows            NUMBER;

               

             BEGIN

            

                IF str_segment_owner IS NULL

                THEN

                   str_segment_owner := USER;

                  

                END IF;

               

                BEGIN

                   SELECT

                    a.iot_type

                   ,b.owner

                   ,b.index_name

                   INTO

                    str_iot_type

                   ,str_owner

                   ,str_index_name

                   FROM

                   all_tables a

                   JOIN (

                      SELECT

                       bb.owner

                      ,bb.index_name

                      ,bb.table_name

                      ,bb.table_owner

                      FROM

                      all_indexes bb

                      WHERE

                      bb.index_type = 'IOT - TOP'

                   ) b

                   ON

                       a.owner = b.table_owner

                   AND a.table_name = b.table_name

                   WHERE

                       a.owner      = str_segment_owner

                   AND a.table_name = p_segment_name;

               

                EXCEPTION

                   WHEN NO_DATA_FOUND

                   THEN

                      str_iot_type := NULL;

                     

                   WHEN OTHERS

                   THEN

                      RAISE;

                     

                END;

               

                IF str_iot_type = 'IOT'

                THEN

                   RETURN 0;

                  

                END IF;

               

                BEGIN

                   SELECT

                    a.bytes

                   ,a.tablespace_name

                   ,a.segment_type

                   ,b.num_rows

                   INTO

                    num_bytes

                   ,str_tablespace_name

                   ,str_segment_type

                   ,num_rows

                   FROM

                   dba_segments a

                   LEFT JOIN

                   all_tables b

                   ON

                       a.segment_name = b.table_name

                   AND a.owner = b.owner

                   WHERE

                       a.owner = str_segment_owner

                   AND a.segment_name = p_segment_name;

                  

                   RETURN num_bytes;

                  

                EXCEPTION

                   -- Post 11g delayed segment creation just means size 0

                   WHEN NO_DATA_FOUND

                   THEN

                      RETURN 0;

                     

                   WHEN OTHERS

                   THEN

                      RAISE;

               

                END;

               

             END get_object_size;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             PROCEDURE get_table_indexes(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2

                ,p_domain_flag        IN  BOOLEAN DEFAULT NULL

                ,p_index_owners       OUT MDSYS.SDO_STRING2_ARRAY

                ,p_index_names        OUT MDSYS.SDO_STRING2_ARRAY  

             )

             AS

                str_table_owner VARCHAR2(30) := p_table_owner;

               

             BEGIN

            

                IF str_table_owner IS NULL

                THEN

                   str_table_owner := USER;

                  

                END IF;

               

                IF p_domain_flag IS NULL

                THEN

                   SELECT

                    a.owner

                   ,a.index_name

                   BULK COLLECT INTO

                    p_index_owners

                   ,p_index_names

                   FROM

                   all_indexes a

                   WHERE

                       a.table_owner = str_table_owner

                   AND a.table_name = p_table_name;

                   

                ELSIF p_domain_flag = TRUE

                THEN

                   SELECT

                    a.owner

                   ,a.index_name

                   BULK COLLECT INTO

                    p_index_owners

                   ,p_index_names

                   FROM

                   all_indexes a

                   WHERE

                       a.table_owner = str_table_owner

                   AND a.table_name = p_table_name

                   AND a.index_type = 'DOMAIN';

               

                ELSIF p_domain_flag = FALSE

                THEN

                   SELECT

                    a.owner

                   ,a.index_name

                   BULK COLLECT INTO

                    p_index_owners

                   ,p_index_names

                   FROM

                   all_indexes a

                   WHERE

                       a.table_owner = str_table_owner

                   AND a.table_name = p_table_name

                   AND a.index_type <> 'DOMAIN';

               

                ELSE

                   RAISE_APPLICATION_ERROR(-20001,'error');

                  

                END IF;

                  

             END get_table_indexes;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION get_domain_index_size(

                 p_domain_index_owner IN VARCHAR2 DEFAULT NULL

                ,p_domain_index_name  IN  VARCHAR2     

             ) RETURN NUMBER

             AS

                str_sql         VARCHAR2(4000);

                str_domain_index_owner VARCHAR2(30) := p_domain_index_owner;

                str_owner       VARCHAR2(30);

                str_index_name  VARCHAR2(30);

                str_ityp_owner  VARCHAR2(30);

                str_ityp_name   VARCHAR2(30);

                str_table_owner VARCHAR2(30);

                str_table_name  VARCHAR2(30);

                str_spidx_owner VARCHAR2(30);

                str_spidx_name  VARCHAR2(30);

                str_ctx_name    VARCHAR2(30);

                num_sde_geom_id NUMBER;

               

             BEGIN

               

                IF str_domain_index_owner IS NULL

                THEN

                   str_domain_index_owner := USER;

                  

                END IF;

               

                BEGIN

                   SELECT

                    a.owner

                   ,a.index_name

                   ,a.ityp_owner

                   ,a.ityp_name

                   ,a.table_owner

                   ,a.table_name

                   INTO

                    str_owner

                   ,str_index_name

                   ,str_ityp_owner

                   ,str_ityp_name

                   ,str_table_owner

                   ,str_table_name

                   FROM

                   all_indexes a

                   WHERE

                       a.owner = str_domain_index_owner

                   AND a.index_name = p_domain_index_name

                   AND a.index_type = 'DOMAIN';

                  

                EXCEPTION

                   WHEN NO_DATA_FOUND

                   THEN

                      RAISE_APPLICATION_ERROR(

                          -20001

                         ,'could not find a domain index named ' || p_domain_index_name

                      );

                     

                   WHEN OTHERS

                   THEN

                      RAISE;

                     

                END;

               

                IF str_ityp_owner = 'MDSYS'

                AND str_ityp_name = 'SPATIAL_INDEX'

                THEN

                   str_sql := 'SELECT '

                           || 'a.sdo_index_owner, '

                           || 'a.sdo_index_table '

                           || 'FROM '

                           || 'mdsys.sdo_index_metadata_table a '

                           || 'WHERE '

                           || '    a.sdo_index_owner = :p01 '

                           || 'AND a.sdo_index_name = :p02 ';

                          

                   EXECUTE IMMEDIATE str_sql

                   INTO

                    str_spidx_owner

                   ,str_spidx_name

                   USING

                    str_owner

                   ,str_index_name;

                  

                   RETURN get_table_size(

                       p_table_owner => str_spidx_owner

                      ,p_table_name  => str_spidx_name

                   );        

                          

                ELSIF str_ityp_owner = 'SDE'

                AND str_ityp_name = 'ST_SPATIAL_INDEX'

                THEN

                   str_sql := 'SELECT '

                           || 'a.geom_id '

                           || 'FROM '

                           || 'sde.st_geometry_columns a '

                           || 'WHERE '

                           || '    a.owner = :p01 '

                           || 'AND a.table_name = :p02 ';

                          

                   EXECUTE IMMEDIATE str_sql

                   INTO

                   num_sde_geom_id

                   USING

                    str_table_owner

                   ,str_table_name;

                  

                   RETURN get_table_size(

                       p_table_owner => str_table_owner

                      ,p_table_name  => 'S' || TO_CHAR(num_sde_geom_id) || '$_IX1'

                   ) + get_table_size(

                       p_table_owner => str_table_owner

                      ,p_table_name  => 'S' || TO_CHAR(num_sde_geom_id) || '$_IX2'

                   );

               

                ELSIF str_ityp_owner = 'CTXSYS'

                AND str_ityp_name = 'CONTEXT'

                THEN

                   str_sql := 'SELECT '

                           || 'a.idx_name '

                           || 'FROM '

                           || 'ctxsys.ctx_indexes a '

                           || 'WHERE '

                           || '    a.idx_table_owner = :p01 '

                           || 'AND a.idx_table= :p02 ';

                          

                   EXECUTE IMMEDIATE str_sql

                   INTO

                   str_ctx_name

                   USING

                    str_table_owner

                   ,str_table_name;

                  

                   RETURN get_table_size(

                       p_table_owner => str_table_owner

                      ,p_table_name  => 'DR$' || str_ctx_name || '$I'

                   ) + get_table_size(

                       p_table_owner => str_table_owner

                      ,p_table_name  => 'DR$' || str_ctx_name || '$R'

                   );

                  

                ELSE

                   RAISE_APPLICATION_ERROR(-20001,'unhandled domain index type');

               

                END IF;

               

             END get_domain_index_size;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION get_table_lob_size(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2     

             ) RETURN NUMBER

             AS

                num_lob_size     NUMBER := 0;

                ary_lob_segments MDSYS.SDO_STRING2_ARRAY;

                ary_lob_indexes  MDSYS.SDO_STRING2_ARRAY;

               

             BEGIN

               

                SELECT

                 a.segment_name

                ,a.index_name

                BULK COLLECT INTO

                 ary_lob_segments

                ,ary_lob_indexes

                FROM

                all_lobs a

                WHERE

                    a.owner = p_table_owner

                AND a.table_name = p_table_name;

               

                IF ary_lob_segments IS NULL

                OR ary_lob_segments.COUNT = 0

                THEN

                   RETURN 0;

                  

                END IF;

               

                FOR i IN 1 .. ary_lob_segments.COUNT

                LOOP

                   num_lob_size := num_lob_size + get_object_size(

                       p_segment_owner => p_table_owner

                      ,p_segment_name  => ary_lob_segments(i)

                   );

                  

                   num_lob_size := num_lob_size + get_object_size(

                       p_segment_owner => p_table_owner

                      ,p_segment_name  => ary_lob_indexes(i)

                   );

                        

                END LOOP;

               

                RETURN num_lob_size;

               

             END get_table_lob_size;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             FUNCTION is_complex_object_table(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2     

             ) RETURN VARCHAR2

             AS

                str_table_owner VARCHAR2(30) := p_table_owner;

                str_data_type   VARCHAR2(255);

               

             BEGIN

            

                IF str_table_owner IS NULL

                THEN

                   str_table_owner := USER;

                  

                END IF;

            

                SELECT

                b.data_type

                INTO

                str_data_type

                FROM

                all_tables a

                JOIN (

                   SELECT DISTINCT

                    bb.owner

                   ,bb.table_name

                   ,bb.data_type

                   FROM

                   all_tab_columns bb

                   WHERE

                       bb.owner = str_table_owner

                   AND bb.table_name = p_table_name

                   AND bb.data_type IN ('SDO_GEORASTER')

                ) b

                ON

                    a.owner = b.owner

                AND a.table_name = b.table_name;

               

                RETURN str_data_type;

               

             EXCEPTION

                WHEN NO_DATA_FOUND

                THEN

                   RETURN NULL;

                  

                WHEN OTHERS

                THEN

                   RAISE;

            

             END is_complex_object_table;

            

             -----------------------------------------------------------------------------

             -----------------------------------------------------------------------------

             PROCEDURE get_sdo_georaster_tables(

                 p_table_owner        IN  VARCHAR2 DEFAULT NULL

                ,p_table_name         IN  VARCHAR2

                ,p_table_owners       OUT MDSYS.SDO_STRING2_ARRAY

                ,p_table_names        OUT MDSYS.SDO_STRING2_ARRAY  

             )

             AS

                str_sql         VARCHAR2(4000);

                str_table_owner VARCHAR2(30) := p_table_owner;

                str_column_name VARCHAR2(30);

               

             BEGIN

            

                IF str_table_owner IS NULL

                THEN

                   str_table_owner := USER;

                  

                END IF;

            

                -- First collect the SDO_GEORASTER column, I guess we can assume there is only one?

                BEGIN

                   SELECT

                   b.column_name

                   INTO str_column_name

                   FROM

                   all_tables a

                   JOIN (

                      SELECT DISTINCT

                       bb.owner

                      ,bb.table_name

                      ,bb.data_type

                      ,bb.column_name

                      FROM

                      all_tab_columns bb

                      WHERE

                          bb.owner = str_table_owner

                      AND bb.table_name = p_table_name

                      AND bb.data_type IN ('SDO_GEORASTER')

                   ) b

                   ON

                       a.owner = b.owner

                   AND a.table_name = b.table_name;

                  

                EXCEPTION

                   WHEN NO_DATA_FOUND

                   THEN

                       RAISE_APPLICATION_ERROR(

                           -20001

                          ,'cannot find SDO_GEORASTER column in ' || p_table_name

                       );

                      

                   WHEN OTHERS

                   THEN

                      RAISE;

                      

                END;

                

                -- Second get the child table names out of the objects

                str_sql := 'SELECT '

                        || ' ''' || str_table_owner || ''''

                        || ',a.' || str_column_name || '.RASTERDATATABLE a '

                        || 'FROM '

                        || str_table_owner || '.' || p_table_name || ' a ';

                      

                EXECUTE IMMEDIATE str_sql

                BULK COLLECT INTO

                 p_table_owners

                ,p_table_names;

            

             END get_sdo_georaster_tables;

            

          END dz_dba_sizer;

          /

          • 2. Re: How to get Size of Spatial Index
            Stefan Jager

            Wow. That's an awful lot of trouble Paul, for something that Oracle actually does: SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE

             

            This function has been around for quite a while, and as far as I can tell does a pretty decent job.

             

            And to the OP: what have you found that did not work for you? And how do you know it didn't work?

             

            Cheers,

            Stefan

            • 3. Re: How to get Size of Spatial Index
              Paul Dziemiela

              Hi Stefan,

               

              True I agree with you on the awful trouble side of things, but I always considered SDO_TUNE an estimate.  I suppose it depends on the goal, if you are just estimating a rough size or if you need a byte-by-byte reconciliation of what using what storage in the database for auditing purposes.  And clearly I cannot report back the size of an index that has not yet created, so the estimation part of things is key there.  If Andre is looking to just generally estimate how much disk he needs for a spatial index (that may or may not exist) then definitely the SDO_TUNE route is the way to go and I am overly complicating things. 

               

              Hmm, you know it occurs to me I could just run a test and see how they compare.

               

              I have national layer of 2,993,465 linestring rivers and streams:

               

              • SDO_TUNE says 286 meg estimated

              • My script says 246 meg actually used

               

              I have a national layer of 2,675,841 small watershed polygons:

               

              • SDO_TUNE says 255 meg estimated
                 
              • My script says 221 meg actually used

               

              Yeah, so I suppose it depends if that that 40 meg matters to you.  Note again that my script is really about measuring the total size of given "dataset" - depending on how we define that.  So in that the domain index measure is just a part.   

               

              Cheers,

               

              Paul

               

              p.s. anyone else notice that ESTIMATE_RTREE_INDEX_SIZE function only works as the table owner (on 11gR2)?  When I run it as SYS or a different user it just returns null.  Is that a bug or a feature?

              • 4. Re: How to get Size of Spatial Index
                Pleiadian

                Personally, I use user_segments and user_sdo_index_info:

                 

                select table_name

                ,      column_name

                ,      sdo_index_table

                ,      tablespace_name

                ,      bytes

                ,      blocks

                ,      extents

                from   mdsys.user_sdo_index_info i

                ,      user_segments             s

                where  s.segment_name = i.sdo_index_table

                 

                Of course, this counts the extents, so there is some error if your blocksize is large.

                • 5. Re: How to get Size of Spatial Index
                  Stefan Jager

                  Hi Paul,

                   

                  Interesting comparison, actually. But if I look at my own situation (both personal and professional), my datasets are either measurable in Megabytes (private projects, test-sets, that sort of stuff), or they are measurable in 1-100 Gigabytes. In either case 40 Megabytes is not going to make a difference in my opinion, unless I am running out of diskspace. But if it got that far (running out of diskspace on 40Mb, especially in Production) you have far bigger problems on your hands.

                   

                  So yes, SDO_TUNE returns an estimate, but it is so close that I can't see why you would need the accuracy. Most datasets would be counted in Gigabytes these days, so 40Mb is just rounding. But that is my opinion, and I tend to be very pragmatic about these sorts of things

                   

                  Regards,

                  Stefan

                  • 6. Re: How to get Size of Spatial Index
                    Paul Dziemiela

                    Hi Stefan,

                     

                    Point well taken, I agree that worrying about 40 meg in 2014 is silly.  Andre should probably answer as to why he needs these numbers.  I have a monthly spreadsheet report that has the total bytes in a set of schemas and an item by item reconciliation of those bytes.  This is quite pedantic and I do question its value overall.

                     

                    Hey Pledian! 

                     

                    Now you are being pedantic with me!  Welcome to the rabbit hole.  So you sir have forgotten to measure the lob holding the INFO blob and its index! 

                    In doing so you may have missed 131,072 bytes!! (depends)  The sky will surely fall on you for that.  Anyhow, yes yes to everyone this is darn silly.  Plebian did get me thinking as to why I demand access to dba_segments rather than all_segments.  This is that old issue of the permissions on the geometry table not matching the permissions on the spatial index table (makes using SDO_JOIN across schemas so awful).  It's just easily to go around it and get the info from the dba_segments but perhaps the script should just use all_segments and just crab at you to grant select on the MDRT table.  Anyone else handle this more gracefully?

                     

                    So only use my script if you need that kind of accountability - and question carefully anyone who asks you for that kind of accountability.  I think that is how I started my first response. 

                     

                    Cheers and Happy Monday,

                     

                    Paul

                    • 7. Re: How to get Size of Spatial Index
                      Stefan Jager

                      Paul Dziemiela wrote:

                       

                      This is that old issue of the permissions on the geometry table not matching the permissions on the spatial index table (makes using SDO_JOIN across schemas so awful).  It's just easily to go around it and get the info from the dba_segments but perhaps the script should just use all_segments and just crab at you to grant select on the MDRT table.  Anyone else handle this more gracefully?

                      Well, I don't know if it's more graceful, but I always hand out permissions via scripts. So in those scripts I check for SDO_GEOMETRY columns, and if I find them I tend to include the MDRT-table and -sequence in my permissions. They're easy enough to find using the sdo_index views. And when handing out permissions you usually have the proper rights to see those views for other users. I prefer scripting this because that way it's easier to restore without forgetting a user or grant or two

                       

                      Happy Tuesday ,

                      Stefan