Oracle: Indexing a subset of rows of a table

Question

I have a table which has active an inactive entries, active = 1 for active and active = 0 for inactive.

I have a variety of indexes on this table, but I only need the indexes maintained for active entries, as the application only queries against active data. Inactive data needs to be kept because it can become active again, but this is generally only done with bulk updates, which wouldn't use an index anyway.

I'm noticing indexing the inactive entries (of there are increasingly more than active entries) takes quite a bit of space.

Is there a way in Oracle (10g) to do something like this:

create index an_idx on tab (active, col1, col2, ... , coln) where active = 1?

Previous attempt:

I tried using a function based index to set the first column to null when active = 0 like so:

create index an_idx on tab (decode(active, 1, 1, null), col1, col2, ... , coln)

But Oracle still seems to index the inactive columns in this case.

Answer 1

Your basic idea is right, but you need to apply the decode to all of the columns. Only when all indexed expressions are NULL will the row not be indexed.

create index an_idx on tab (
  decode(active, 1, col1, null),
  ...
  decode(active, 1, coln, null)
)

Of course, if you then want a query to use this index, it has to use the same expressions in the WHERE clause.

Note I do not think you want to include the expression decode(active, 1, 1, null) in the index, since it would be constant for all indexed rows.