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.

  • I'll preface this by saying I'm not an Oracle expert, but I'd be highly surprised if rebuilding indexes on your audit table actually made any differences performance-wise, for the following reasons: 1. B-Tree indexes work tremendously well regardless of how fragmented they become. It is usually recommended to not rebuild indexes in other mainstream database systems such as Microsoft SQL Server. Ergo, I'd find this to be a product deficiency in Oracle, if the opposite was true - hard to believe. 2. An audit table is assumptively insert-only, and should have minimal to none fragmentation anyway.
    – J.D.
    Commented Jul 24, 2023 at 12:22
  • It's not the rebuild itself (As you say, it's normally fairly pointless). It's the rebuild with key compression, which drastically reduces the size of the index. So saves a bit of storage - which normally I'd say wasn't a big deal but we have a bit of an issue with a none expandable device. But also makes index scans more efficient. So should be worthwhile.
    – Carlovski
    Commented Jul 24, 2023 at 13:22
  • Ah gotcha, you're looking at this from a space savings perspective. Again not an expert on Oracle, so forgive me if the answer is obvious, but is compression not the default on indexes in Oracle? I'm assuming you'd only have to do this rebuild once to change this setting, and it wouldn't be a recurring thing, right?
    – J.D.
    Commented Jul 24, 2023 at 16:18
  • There is no default compression for indexes. You have to manually set the prefix level. You can either determine the proper prefix level by examining the data, or by doing an old-school analyze index (not dbms_stats) and then looking at the opt_cmpr_count column in the index_stats view to get the optimal value. But once you create the index, it doesn't deteriorate over time like table compression does.
    – Paul W
    Commented Jul 25, 2023 at 13:17

1 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:

  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.

  • Thanks for the reply. Few more details 1 - I suspect at least 1 of them might be redundant. But they do use this table quite a lot for support purposes.. 2- It's not really concatenated, that was just my lazy way of saying it was a compound index. I realise it was a bit confusing! The use of the dimension table is a good one though. 4 - Yeah, I'd use parallel here. I'll have to be conservative on the degree, as system is still used, and we don't have that many cores active. I may have been getting too worried about the dictionary locks, that sounds doable.
    – Carlovski
    Commented Jul 25, 2023 at 14:34
  • I will discuss/review the indexes with the dev team before doing anything. But in my testing, rebuilding with prefix compression did definitely work, and reduced the size of the index by over a factor of 10 (I suspect I didn't do the optimal compression either, will do the analyze first next time). And AFAIK, Partitioning is still a additional paid for feature on EE.
    – Carlovski
    Commented Jul 25, 2023 at 14:38
  • You're right about the alter - this capability must have been added in a recent version. Older versions I know required dropping/recreating. Edited that out of my answer. You're also right about EE... thx for the tip. Sounds like your company may need to consider going for it though, it's pretty indispensable at those data volumes.
    – Paul W
    Commented Jul 25, 2023 at 14:42
  • Yeah, I don't fancy my chances of getting the spend authorised! There aren't actually many places it would be worthwhile, and here it only impacts support/back office function, not end users. Thanks for the answers.
    – Carlovski
    Commented Jul 25, 2023 at 14:52
  • You could architect a workaround using separate dated tables and have a scheduled proc that moves synonyms from one to the next... poor man's partitioning. A drop table is a lot easier than a massive delete.
    – Paul W
    Commented Jul 25, 2023 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.