English

Bug: 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.

  • Option 1 - Create the index manually:

    Step 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';


    Step 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.

  • Option 2 - View all missing IOT SP_ID indexes and recreate them with a script:

    Step 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;


    Step 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


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


    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;


    Step 4:

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

    Example:

    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.


    Step 5 (optional):

    Query the view created in Step 1 to make sure the missing indexes are no longer shown.

    Example:

    SELECT * FROM st_geom_missing_index_v;

    no rows selected


Related Information