English

How To: Create a user defined index on an Adds table

Summary

For Oracle databases, user defined indexes on existing feature classes are not automatically created on the corresponding Adds table after registering the feature class as versioned. Having such an index on the Adds table aids performance when retrieving records existing only in the Adds table.

Procedure

To create user defined indexes on the Adds table, either add the user defined index after the feature class is versioned or add it manually by the following procedure.

  1. Log onto SQL*Plus as feature class owner to check index names for each column having an index:

    SQL> select table_name, column_name, index_name from user_ind_columns
    where table_name in (select table_name from sde.table_registry where owner=USER) order by 1;

  2. Generate the metadata syntax:

    SQL> select table_name, 'select dbms_metadata.get_ddl(''INDEX'','''||index_name||''') from dual;'
    from user_ind_columns
    where table_name in (select table_name from sde.table_registry where owner=USER) order by table_name, index_name;

  3. Highlight the syntax from the output in Step 2 and copy/paste into the sql prompt. This obtains the metadata.

    Example: Syntax from Step 2

    SQL> select dbms_metadata.get_ddl('INDEX','I6450PARCEL_ID') from dual;

    Example output from above SQL:

    CREATE INDEX "GISADMIN"."I6450PARCELE_ID" ON "GISADMIN"."ZAB_PARCELS"("PARCEL_ID")
    PCTREE 0 INITRANS 4 MAXTRANS 255 COMPUTE STATISTICS NO LOGGING
    STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "SDE";

  4. Obtain the registration_id of the table:

    SQL> select registration_id from sde.table_registry where owner=USER order by table_name;

  5. Modify the output from Step 3 and use the registration_id from Step 4 to create the index on the column for the Adds table. In the below example, "I6450PARCELE_ID" is replaced with "I6450_A228_PARCEL_ID" and "GISADMIN"."ZAB_PARCELS" with "GISADMIN"."A228".

    Example:

    CREATE INDEX "GISADMIN"."I6450_A228_PARCEL_ID" ON "GISADMIN"."A228" ("PARCEL_ID")
    PCTFREE 0 INITRANS 4 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
    STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "SDE";