Select performance problems with conditional index vs non-conditional on large volume table

We are trying to change some indexes on a large-volume table to be conditional indexes (function-based indexes) to reduce how much space is being used for indexes against this table. When testing the performance insert and update times are very minorly affected and easily acceptable. Reads where most of the filtering down is done on the columns in the index show almost no change from non-conditional to conditional index.

However, reads where filtering by the columns in the index still leaves a large number of remaining records and a significant amount of filtering after that takes place using columns not in the index see a significant difference. After adding some data, deleting and re-creating the indexes, and gathering statistics the first read showed similar times between our baseline (index is not conditional) and the conditional index. Subsequent reads using the same query improved greatly for the baseline and did not improve with the conditional index use. I suspect the baseline queries are taking advantage of caching that the conditional index is not.

Has anyone encountered similar performance problems using conditional indexes on Oracle? Any advice how we can determine the exact cause and hopefully avoid this slower performance with the conditional indexes?

We are using Oracle 11g, running the queries in SQL plus or from an application using OleDB doesn't make a difference. We have verified the explain plan for both cases shows the same query plan, only the costs change.

I'll be changing the table and field names here but the structure is the same:

Baseline Index:

CREATE INDEX IEMP_CMPHIRDEP ON EMPLOYEE (COMPANY, HIRE_DATE, DEPT_ID, EMP_ID);

Function-based version (In this case a new column "ARCHIVED" is added to the EMPLOYEE table and the function-based index is made conditional on this column being 'N'. If it is any other value the row will not be included in the index.):

```
CREATE INDEX IEMP_CMPHIRDEP ON EMPLOYEE (
CASE WHEN

All the comments above are appropriate.

If you provide the details on the data types and any not null constraints of the columns in question, then we can better determine whether your function-based approach is appropriate. I would also gather statistics on the indexes and look at the unique values, etc. You can compress your baseline index too, if say you have very few unique values for company and hire date, you can compress by two levels. In fact, you can do the following to see if index compression will help:

analyze index IEMP_CMPHIRDEP validate structure;

Then query the view index_stats:

SYS@instance> select OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             3               28

In the above example, compressing the index at level 3 would save 28% space. So if it is a good amount, you would

alter index IEMP_CMPHIRDEP rebuild compress 3;

StackExchange Database Administrators Q#119237, answer score: 3