Optimizing Storage Structures - Oracle Database 11gR2 Performance Tuning - page 95

To populate the BLOB field, we have defined a DIRECTORY called TESTBLOB to refer to
the folder $ORACLE_HOME/rdbms/admin , and we have used a PL/SQL block to populate
the c_file field for the first 100 customers in the table, loading the same file in it—the
catalog.sql script, which is about 58 Kbytes.
To do so, we have used a simple sequence of operations: updating the row with an empty
blob—returned by the function empty_blob() —and using the procedure DBMS_LOB.
LOADFROMFILE to populate the newly created empty BLOB reference.
We have then used the procedure DBMS_SPACE.SPACE_USAGE to measure the used space
—expressed in database blocks and in bytes—in the segment where the LOB data is stored.
We have then modified the definition of the c_file field, enabling deduplicating first—which
means that Oracle stores only one copy for the same BLOB content—and then compression.
After each of these steps, we have again measured the space used by the BLOB storage,
verifying a great decrease in occupation.
There's more...
In the example, there is an aspect related to the trivial nature of the test case: the content of
the BLOB field is populated with the data of a text file; in real-life databases, we chose another
data type to store text data, for example, VARCHAR , NVARCHAR , CLOB , and reserved BLOB
fields to store binary data.
In creating the c_file BLOB field, we have the clause enable storage in row , which
means "store the data in the same DB block in which other fields of the row are stored". We
saw that the data are not actually stored in rows, because when the size of the BLOB field is
greater than 4000 bytes it is always stored off-line. The same behavior occurs when the DB
block size is large enough to accommodate the BLOB field.
We can even specify a CHUNK size when defining a BLOB field, which is an integer multiple
of DB_BLOCK_SIZE : when the database engine reads BLOB fields, it accesses the data in
pieces sized accordingly to the CHUNK parameter, and it's more efficient to read large chunks
of data than small.
Here is an example of defining a BLOB field with a specified CHUNK size:
ALTER TABLE MyCustomers ADD (c_file BLOB)
LOB(c_file) STORE AS SECUREFILE (
tablespace ASSM_TS
CHUNK 4096
enable storage in row
nocache
logging
);