NIM084235: St_Geometry spatial indexes do not create S###$_IX2 indexes on the IOT SP_ID column

Description

The S<geomid>$_IX2 index that facilitates deleting rows from an SDE.ST_Geometry spatial table is not created in geodatabases in Oracle.

Due to the missing index, performance problems may be seen during editing, especially deletes.

This issue is fixed in the 10.1 SP1 Parcel Editing Performance Patch.

Cause

The index is missing.

select table_name, index_name from user_indexes where table_name='S1_IDX$' and index_name like '%IX2';

no rows selected

If the index is present the query should return one record:

select table_name, index_name from user_indexes where table_name='S1_IDX$' and index_name like '%IX2';

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
S1_IDX$                        S1$_IX2

Workaround

The index must be created.

Below are two options to create the needed index, Option 1 - Create the index manually or Option 2 - Use the script provided within this article to determine the missing indexes and create them.

1. Select the geom_id from the sde.st_geometry_columns table for the st_geometry table that is missing the index.

Example:

select geom_id from sde.st_geometry_columns where table_name='RIVERS' and owner='AKESAR';

2. Create the index:

Example:

CREATE INDEX AKESAR."S###$_IX2" ON AKESAR.S###_IDX$ ("SP_ID") PCTFREE 0 INITRANS 8 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS;

Where "###" is the geom_id from the sde.st_geometry_columns table found in Step 1.

1. (optional): Create a view that can be used to see all the tables that are missing the index.

Note:
User must have these privileges:
   SELECT ON dba_indexes

Example:
This view shows which layers have S tables that are missing the
sp_id column index.

CREATE OR REPLACE VIEW st_geom_missing_index_v AS
  SELECT
            stgi.owner,
            stgi.table_name layer_name,
            stgi.index_name,
            stgi.index_id index_id,
            'S' || stgi.index_id || '_IDX$' s_table_name,
            'S' || stgi.index_id || '$_IX2' missing_index_name
    FROM
            sde.st_geometry_index stgi
            LEFT JOIN
                dba_indexes dbaix
                ON 
                stgi.owner = dbaix.owner
                AND
                ('S' || stgi.index_id || '_IDX$') = dbaix.table_name
                AND
                ('S' || stgi.index_id || '$_IX2') = dbaix.index_name
  WHERE
            dbaix.index_name IS NULL
 ORDER BY
            1,2,3;

2. (optional): Select all the tables that are missing the index by querying the view created in Step 1.

Example:

SELECT * FROM st_geom_missing_index_v;

OWNER  LAYER_NAME INDEX_NAME INDEX_ID S_TABLE_NAME MISSING_INDEX_NAME
------ ---------- ---------- -------- ------------ ------------------
AKESAR RIVERS     S1$_IX1    1        S1_IDX$      S1$_IX2

Note:
User must have these privileges:
   SELECT ANY TABLE
   SELECT ON dba_indexes
   CREATE ANY INDEX
   CREATE PROCEDURE

3. Create a stored procedure that creates the indexes.

 CREATE or REPLACE PROCEDURE fix_missing_gdb_sp_id_indexes
IS
  v_sql VARCHAR2(512);
  
  /*
   Identify missing indexes by joining these two tables:
   SDE.ST_GEOMETRY_INDEX
   DBA_INDEXES
   Each Layer's Spatial Index table name = 
                                  'S' || st_geometry_index.index_id || '_IDX$'
   Each SP_ID column index name =
                                  'S' || st_geometry_index.index_id || '$_IX2'
  */
  CURSOR c_missing_index_query IS
      SELECT
            stgi.owner,
            stgi.table_name layer_name,
            stgi.index_name,
            stgi.index_id index_id,
            'S' || stgi.index_id || '_IDX$' s_table_name,
            'S' || stgi.index_id || '$_IX2' missing_index_name
       FROM
            sde.st_geometry_index stgi
            LEFT JOIN
                dba_indexes dbaix
                ON 
                stgi.owner = dbaix.owner
                AND
                ('S' || stgi.index_id || '_IDX$') = dbaix.table_name
                AND
                ('S' || stgi.index_id || '$_IX2') = dbaix.index_name
      WHERE
            dbaix.index_name IS NULL
   ORDER BY
            1,2,3;
    
BEGIN

    -- For each layer with an S table missing its SP_ID index, create one.
    FOR rec IN c_missing_index_query
    
      LOOP
        v_sql := 'CREATE INDEX ' 
                 || rec.owner || '.' || rec.missing_index_name
                 || ' ON ' || rec.owner || '.' || rec.s_table_name || '(sp_id)';
        
        BEGIN
          dbms_output.put_line(v_sql);
          EXECUTE IMMEDIATE v_sql;
          EXCEPTION
            WHEN OTHERS THEN
              RAISE;
        END;
        
      END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      RAISE;

END;

4. Run the stored procedure created in Step 3 to create the indexes.

set serveroutput on

exec fix_missing_gdb_sp_id_indexes;

CREATE INDEX AKESAR.S1$_IX2 ON AKESAR.S1_IDX$(sp_id)

PL/SQL procedure successfully completed.

5. (optional): Query the view created in Step 1 to make sure the missing indexes are no longer shown.

SELECT * FROM st_geom_missing_index_v;

no rows selected