After more digging into the sources and queries, I propose to add an index like this: CREATE INDEX "RHQ_RES_I_STAT_IDX" ON "RHQ_RESOURCE" ("INVENTORY_STATUS") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING LOCAL While it may not help a lot on this specific query, there is a large number of queries in the system that uses the inventory_status column. ----- In the longer run, it may make sense to turn the column into something integer based.
Given the low cardinality of the field would this index really be helpful or would the index just add overhead to insert/deletes on resources?
Heiko, Are there particular queries you know are slow because of no index here? We need more justification for adding this.
I don't recall. Original idea came from observations in OracleEM.