LOBs in Oracle are stored in-row when they are less than 4K in size and out-of-row otherwise.
I'm wondering how do SECUREFILE storage options COMPRESS and ENABLE STORAGE IN ROW work?
I have a lot of XML documents that are 4-10K in size that could easily be compressed to fit in under 4K. Would COMPRESS option result LOBs getting stored in-row therefore improving performance? Or does Oracle make in-row/out-of-row storage decision based on uncompressed size?
Best Answer
Consider using de-duplication if you have lots of identical XML - documentation link.
I wrote a huge unit test, but it turns out that SecureFile LOBs are always stored in a Lob Segment outside the row!