Index rebuild, on a busy table, in a 24/7 system

Question

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.

Answer 1

1. Consider whether you actually need indexes. An audit or log table rarely is queried in a fashion and frequency that requires indexing.

2. Why have table_name||column_name in the index at all? A concatenated string like that requires you always query it with the same concatenation operator, and can use up to 256 (128 per object identifier) characters making it exceptionally wide. If you must index the table_name and column_name, consider creating a dimension table where all table and column names are registered with numeric surrogate keys, and place only the surrogate keys in the actual audit table. That will provide for a much smaller index (and smaller table as well). Even better would be the have composite partitioning with LIST subpartitions on your table_name so you can partition prune and not bother with indexing it at all.

3. Even with a numeric surrogate key, since you still have repeated values, prefix compression is still obviously helpful and should be applied if possible. But the ROWID itself is usually larger and that is never compressed, so typically you can expect only a 10-30% reduction in index size. You are getting much higher ratios because of the insanely wide table_name||column_name size, which shouldn't be in the index in the first place.

4. To rebuild an index this large, you need parallelism (e.g. PARALLEL (DEGREE 16) ) and I suggest also the NOLOGGING option (safe for an index, which won't cause data loss if it is unrecoverable). Because the table is busy and 24/7, you will probably also need to use the ONLINE option. This will significantly slow down the rebuild, but allows DML operations on the table during the index build process. As long as you can get a microsecond window without a transaction on the table in order to finalize the dictionary change at the very end of the rebuild, it'll work even though you application is still working on the table. So your rebuild would look something like this:

   ALTER INDEX myindex REBUILD PARALLEL (DEGREE 16) NOLOGGING ONLINE;
   

5. Also check v$pgastat where name = 'global memory bound' and ensure it says 1G (the max). If it doesn't, either raise pga_aggregate_target until the global bound reaches 1G or manually set your session sort_area_size to 1G to use maximum PGA before spilling to temp space. A very small PGA allocation can make your index build take a much longer period of time. Also since you are not partitioned you'd have to ensure you have enough temp space (probably hundreds of GB) for the operation or it might fail.

Prefix compression does not deteriorate over time so moving/rebuilding won't "fix" it like heap compression of a table segment can. Again, I suggest reviewing the need for these indexes and how they are defined before moving forward with rebuilds.

Lastly, I know you said you can't use partitioning, but maintaining a billion row audit table really can't be done properly without partitioning.