Oracle: Indexing a subset of rows of a table

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.

Partition the table by ACTIVE, create local indexes, and make the indexes for the inactive partitions UNUSABLE. This will eliminate the time spent on indexing inactive data.

create table tab(active number, col1 number, col2 number, col3 number)
    partition by list(active) 
    (partition tab_active values(1), partition tab_inactive values(0));

create index tab_index1 on tab(col1) local;

alter index tab_index1 modify partition tab_inactive unusable;

But there are some potential downsides to this approach:

• Not all types of indexes can be unusable.
• It's not normal to have unusable objects in the database. People will likely complain about it or assume it's a bug and rebuild it.
• Some operations, such as truncate, will automatically make the indexes usable again.

---

In Oracle 12c you can accomplish this using partial indexes:

create table tab(active number, col1 number, col2 number, col3 number)
    partition by list(active) 
    (partition tab_active   values(1) indexing on,
     partition tab_inactive values(0) indexing off);

create index tab_index1 on tab(col1) local indexing partial;