With SECUREFILE
storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space.
Timing is fairly easy to profile but what's the easiest way to get a reasonably accurate measurement of how much space a specific LOB column takes up?
Best Answer
You can get the size in bytes of the BLOB column via
DBMS_LOB.GETLENGTH(...)
.To get a total size in bytes of column for the entire table
SUM
it up like this:If you've marked the BLOB column as deduplicated then you can run something like the following to get stats on the actual size in bytes used. Compare the
used_bytes
value before/after your enable settings that could change the size of the BLOB column (eg. dedupe, compression, etc).This code is a slightly modified version of the code in Oracle's post explaining how deduplication works. The original is available here: http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html.