We have got an audit table, that has got pretty big (> 1 billion rows). It's got a number of indexes that are actually bigger than the table itself, that are the ideal case for index key compression (they are basically have Table name||Column name as the leading edge). I've been getting over 10:1 ratios in a test database.
The issue is doing the rebuild. We are on 19c enterprise edition so can do it online. But not partitioning so that's not an option. But the system is 24/7, and a number of the applications that use this audit are used all the time (though it is less busy overnight).
Apart from the arguments around not rolling your own audit system (It's a bit baked into existing processes now, and pre-dates me!) and doing some housekeeping (Apparently we need all of it), any ideas for best way to minimise the impact here? Was considering building as a brand new index instead and dropping the old one, but not sure if that even helps. Can you even create two indexes with the same columns? I can check/test that I guess but not the impact so easily.