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;
Comments