I have noticed that Oracle text related objects, particularily the $I tables are some of the largest objects in our database. I have been actively pursuing utlizing Oracle advanced compression in our databases for OLTP table compression and LOB object compression. I have been unable to find any documentation or notes on if it is advisable to implement either table OLTP or LOB compression for Oracle text objects. Does anyone have any advice on this?
I wouldn't expect a lot of value in Advanced Compression but I've never tried it. Here is my reasoning with respect to not being optimistic.
A/C works by finding duplicates within a block. You don't mention a block size but assuming the standard 8K block ... how many duplicates do you expect to find? ... Even if you have 32K blocks (and there are issues with them I won't go into here) how many duplicates?
Granted this is not done at the level of level of visible characters but still I wouldn't expect a lot of value.
That said ... this is Oracle and the correct answer to almost every question is "it depends" so create a test schema ... test it ... find out ... and then update this thread so the rest of us know too. You might also want to test SecureFile tablespace compression. Thank you.