Too much storage used by indexes

Lots of people think that with the hard drives 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!

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 IOs you'll also reduce the CPU load in a lot of cases.

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. Always 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;