Problem

Too much storage used by indexes

Lots of people think that with the harddrives we currently have, storage isn't an issue. Sure, they are pretty cheap, even if you need several TBs. But when working with big databases, things look a bit different. You don't go to the next store and buy a few drives, you'll probably have a big and expensive SAN and storage is suddenly more expensive.

Of course, if you have indexes you don't need, drop them! They consume storage and CPU time when adding records to your table.

But if you still have little storage left, there's another solution waiting for you!

Solution

Recipe #1 - Compress indexes to save Storage space

Oracle supports compressed indexes for a long time. While it's probably clear that you'll gain some free storage space, you often also improve the performance. This might sound a bit contradicting, but by reducing the IO's you'll also reduce the CPU load in a lot of cases. Here's an old, but still valid, article by Tom Kyte: Compressed Indexes & Tables.

The PL/SQL block below searches for indexes and compresses them on the fly. You might want to check the result of the SELECT statement before running the whole block though. I'd always advise you to run it on a test system first, just in case..

DECLARE
  CURSOR get_indexes IS
    SELECT index_name FROM dba_indexes di
    WHERE owner=SYS_CONTEXT('userenv', 'current_schema')
    AND di.compression = 'DISABLED'
    AND di.index_type  IN ('NORMAL')
    AND (di.uniqueness = 'NONUNIQUE' OR (di.uniqueness = 'UNIQUE' AND EXISTS (
      SELECT 1 FROM dba_ind_columns dic
      WHERE dic.index_owner = di.owner
      AND dic.index_name = di.index_name
      GROUP BY dic.index_owner, dic.index_name
      HAVING COUNT(1) > 1
    )));
BEGIN
  FOR rec IN  get_indexes LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name || ' REBUILD ONLINE COMPRESS';
  END LOOP;
END;