Oracle: indexing a subset of table rows

Question

I have a table in which inactive records are active, active = 1 for active and active = 0 for inactive.

I have many indexes in this table, but I only need indexes supported for active records, since the application only requests active data. Inactive data must be saved, since it can be activated again, but usually this is done only with bulk updates, which in any case will not use the index.

I notice that indexing inactive records (of which more than active records) takes up a lot 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 looks like this:

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

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

Answer 1

Partition the ACTIVE table, create local indexes, and create indexes for inactive UNUSABLE partitions. This will eliminate the time taken to index 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 disadvantages to this approach:

• Not all types of indexes may be unsuitable.
• It is not normal to have unused objects in the database. People are likely to complain about it or consider it a mistake and rebuild it.
• Some operations, such as truncate, will automatically use indexes 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; 

Answer 2

Your basic idea is correct, but you need to apply decoding to all columns. Only when all indexed expressions are NULL will the string not be indexed.

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

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

Note. I don’t think you want to include the expression decode(active, 1, 1, null) in the index, as it will be constant for all indexed rows.

Answer 3

I do not think that's possible. There are several options

• Create an index (active, col1 ...) so that the query time does not affect as many inactive elements
• Create two tables for active / inactive items and manage your active status by moving things between two tables.
• Create a second table with all the data you want to index, as well as a unique identifier and join the table to get the rest of the information.