1 Reply Latest reply on Feb 21, 2014 9:55 PM by Matperry-Oracle

    Size of spatial index RDF_V$GEO_IDX

    28f0f31d-f7f8-4fcd-a737-4d97bf31ab13

      How can I get the size of the spatial index of a semantic model?

       

      With the following query:

      SELECT I.INDEX_NAME, SUM(S.BYTES)/1024 AS SIZEKB
      FROM MDSYS.SEM_DTYPE_INDEX_INFO I, DBA_SEGMENTS S
      wHERE I.INDEX_NAME = S.SEGMENT_NAME
      GROUP BY I.INDEX_NAME;
      

      I get the size of other datatype indexes (e.g., RDF_V$STR_IDX) but not the spatial index (RDF_V$GEO_IDX).

       

      The spatial index is created and I get its entry in MDSYS.SEM_DTYPE_INDEX_INFO if I query the following. But I cannot see entry about the spatial index in table DBA_SEGMENTS

      SELECT I.INDEX_NAME, I.STATUS
      FROM MDSYS.SEM_DTYPE_INDEX_INFO I;
      

       

      I am using Oracle Spatial and Graph 12c.

       

      Thank you for your consideration.

       

      Best regards,

      George

        • 1. Re: Size of spatial index RDF_V$GEO_IDX
          Matperry-Oracle

          Hi George,

           

          You need to get the SDO_INDEX_TABLE name from ALL_SDO_INDEX_INFO and then get the size of that table:

           

          SQL> SELECT SDO_INDEX_TABLE

          FROM ALL_SDO_INDEX_INFO

          WHERE TABLE_OWNER='MDSYS' AND INDEX_NAME='RDF_V$GEO_IDX';

            2    3

          SDO_INDEX_TABLE

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

          MDRT_17A52$

           

          1 row selected.

           

          SQL> SELECT SUM(BYTES)

          FROM DBA_SEGMENTS

          WHERE SEGMENT_NAME='MDRT_17A52$';

            2    3

                    SUM(BYTES)

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

                         65536

           

          1 row selected.