Databases Reference
In-Depth Information
The drawback of having a large
CHUNK
parameter occurs when we want to update the field,
because the database engine writes
CHUNK
sized pieces of data in redo logs and in undo
segments. The redo log files have block sizes equal to the physical sector size of the disk and
since the release of 11
g
R2 we can specify a size of 512, 1024, or 4096 bytes. To write a
chunk of 8192 bytes, for example, we need to write from 2 to 16 blocks in the redo log, so it's
more efficient to have smaller
CHUNK(s)
in this situation.
We have specified the use of
LOGGING
for BLOB data. Sometimes we hear of using
NOLOGGING
for BLOB fields because it's faster; the counterpart of using
NOLOGGING
is in
data availability. When we use
LOGGING
we are sure that data is recoverable in case of some
database server failure or should the disk/tape or storage media fail. In using
NOLOGGING
we
cannot recover changes from the redo log because the changes were never logged.
The use of
SecureFile
(s)
allows us to apply some of the enhancements using BLOB fields;
in fact we can deduplicate the field, compress, encrypt, manage caching, and log.
When we create a BLOB field in Oracle Database 11
g
R2, by default we are
using BasicFile, that is, the standard BLOB definition that was in use before.
We can change this behavior with
ALTER SYSTEM SET db_securefile
= 'FORCE'
, to have any BLOB field created by default as SecureFile; other
options are
PERMITTED
(the default),
ALWAYS
,
NEVER
,
IGNORE
.
Deduplication consists of storing one copy of a
BLOB
field when the same content is shared
among different rows, thus reducing the space requirements. It's a good practice and useful
in situations where the content of
BLOB
fields experience limited cardinality or a broad
repetition of data.
The cardinality is the number of elements in a set; low cardinality refers to
columns with few unique values, such as the gender column in a customer
table, while high cardinality refers to columns with values which are (almost)
unique, such as e-mail addresses or IDs.
The concept of data compression is easy to understand, compressing BLOB data reduces the
size. The compression, obviously, will produce the best effect when the binary data is not initially
compressed. In the example presented in this recipe, we have stored a text file, which is a very
good candidate for data compression. If we are storing images compressed by a third party tool
before being inserted into the DB in a
BLOB
field, compression will not offer great improvement
because the original format is already compressed. In these situations, when we know that the
content of a
BLOB
field will be a compressed format, it's better to disable the compression to
save CPU time. However, from Oracle database 11
g
R2, the SecureFiles Compression feature
automatically avoids compressing data that would not benefit from compression.
