CODEWITHSUNDEEP
sqldatabaseoracle-databaseindexing
Clinton
Clinton

Reputation: 23135

Oracle: Indexing a subset of rows of a table

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.

Upvotes: 9

Views: 2543

Answers (3)

Jon Heller
Jon Heller

Reputation: 36832

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;

Upvotes: 9

Dave Costa
Dave Costa

Reputation: 48121

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.

Upvotes: 12

dfb
dfb

Reputation: 13289

I don't think this is possible. There are a few options

  1. Make an index on (active, col1...) so that query time isn't affected as much by inactive elements
  2. Create two tables for active/inactive items and manage active status by moving things between the two tables
  3. Create a second table with all data you want indexed as well as a unique identifer and join to the table to get the rest of the data.

Upvotes: 1

Related Questions