Databases Reference
In-Depth Information
The encryption of BLOB data is an important feature that allows us to encrypt data stored in
a particular BLOB field, for example, when we store an image diagnosis or digital documents
that must remain confidential. To encrypt data we will use transparent database encryption,
as for regular fields.
Caching LOB data in the database can be a nightmare, because this kind of data is usually
very large in size, so caching a single field may prove costly in terms of the database buffers
used to store the object. To accommodate enough space for a LOB—which ultimately won't be
used anymore—there will be many database blocks which were once cached and have now
been freed. For this reason, caching for LOB fields is often disabled, as we have done when
defining the
c_file
field
The last aspect to take care of when defining BLOB fields is logging. The default behavior is
the same that is used for other data types, namely, full logging enabled. Due to the large size
of BLOB data, we could choose to bypass the logging mechanism for this kind of information,
using the
NOLOGGING
parameters. In this case, however, we are not able to rely on database
read consistency, because the changes to the
BLOB
field were never logged.
With Oracle SecureFiles, we have a third choice for logging: we can use
filesystem_like_
logging
, which will log the metadata information changes about the BLOB, but won't log the
data itself, providing a comfortable alternative.
Using index clusters
A cluster is a group of tables that share common columns and are stored in the same data
blocks; this organization is useful when we access this data using joins in queries.
How to do it...
In this recipe, we will see how and when to use index clusters, and some tricks to adopt when
using this kind of storage. Follow these steps:
1.
Connect to the HR schema of
TESTDB
database:
CONNECT hr@TESTDB/hr
2.
Create a cluster:
CREATE CLUSTER LOC_ENTRIES (COUNTRY_ID CHAR(2)) SIZE 100;
3.
Create the cluster index:
CREATE INDEX IDX_LOC_ENTRIES ON CLUSTER LOC_ENTRIES;
4. Create and populate the first table:
CREATE TABLE CL_COUNTRIES CLUSTER LOC_ENTRIES (COUNTRY_ID) AS
SELECT * FROM COUNTRIES;
