English

How To: Improve selection performance in ArcSDE for Oracle

Summary

If more than 100 features are selected in ArcSDE, they are written to log file tables. If a second selection is made from the original selection set, the actual selection is done from the log file.

Instructions provided describe how to improve performance of such selections against existing log file selections by dropping the first index on a user's log file data table and replacing it with a composite index.

Procedure

The following illustrates how to drop the existing index and create a composite index on a user’s sde_logfile_data table:

Code:
sqlplus ken/ken

SQL> desc sde_logfile_data
Name Null? Type
----------------------------------------- -------- ----------------------------
LOGFILE_DATA_ID NOT NULL NUMBER(38)
SDE_ROW_ID NOT NULL NUMBER(38)

SQL> select index_name, table_name from user_indexes where table_name = 'SDE_LOGFILE_DATA';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SDE_LOGFILE_DATA_IDX1 SDE_LOGFILE_DATA
SDE_LOGFILE_DATA_IDX2 SDE_LOGFILE_DATA

SQL> drop index sde_logfile_data_idx1;

Index dropped.

SQL> create index sde_logfile_data_idx1 on sde_logfile_data (logfile_data_id, sde_row_id);

Index created.

Once the index is recreated, it is necessary to update the statistics on the log file table.

Code:
SQL> analyze table sde_logfile_data compute statistics;

Table analyzed.