Database Reference
In-Depth Information
CHUNK
). If you pick a chunk size that does not meet your expected LOB sizes, you could end up wasting an excessive
amount of space. For example, if you have that table with 7KB LOBs on average, and you use a
CHUNK
size of 32KB, you
will be wasting approximately 25KB of space per LOB instance. On the other hand, if you use an 8KB
CHUNK
, you will
minimize any sort of waste.
You also need to be careful when you want to minimize the number of
CHUNK
s you have per LOB instance. As you
have seen, there is a LOBINDEX used to point to the individual chunks, and the more chunks you have, the larger this
index is. If you have a 4MB LOB and use an 8KB
CHUNK
, you will need at least 512
CHUNK
s to store that information. This
means you need at least enough LOBINDEX entries to point to these chunks. It might not sound like a lot until you
remember this is per LOB instance; if you have thousands of 4MB LOBs, you now have many thousands of entries.
This will also affect your retrieval performance, as it takes longer to read and manage many small chunks than it takes
to read fewer, but larger, chunks. The ultimate goal is to use a
CHUNK
size that minimizes your waste, but also efficiently
stores your data.
RETENTION Clause
The
RETENTION
clause differs depending on whether you're using SecureFiles or BasicFiles. If you look back at the
output of
DBMS_METADATA
at the beginning of the “Internal Lobs” section, notice that there is no
RETENTION
clause
in the
CREATE TABLE
statement for a SecureFiles LOB whereas there is one for a BasicFiles LOB. This is because
RETENTION
is automatically enabled for SecureFiles.
RETENTON
is used to control the read consistency of the LOB. I'll provide details in subsequent subsections on
how
RETENTION
is handled differently between SecureFiles and BasicFiles.
Read Consistency for LOBs
In previous chapters, we've discussed read consistency, multiversioning, and the role that undo plays in that. Well,
when it comes to LOBs, the way read consistency is implemented changes. The LOBSEGMENT does not use undo to
record its changes; rather, it versions the information directly in the LOBSEGMENT itself. The LOBINDEX generates
undo just as any other segment would, but the LOBSEGMENT does not. Instead, when you modify a LOB, Oracle
allocates a new
CHUNK
and leaves the old
CHUNK
in place. If you roll back your transaction, the changes to the LOB index
are rolled back and the index will point to the old
CHUNK
again. So the undo maintenance is performed right in the
LOBSEGMENT itself. As you modify the data, the old data is left in place and new data is created.
This comes into play for reading the LOB data as well. LOBs are read consistent, just as all other segments are. If
you retrieve a LOB locator at 9:00 a.m., the LOB data you retrieve from it will be “as of 9:00 a.m.” Just like if you open a
cursor (a resultset) at 9:00 a.m., the rows it produces will be as of that point in time. Even if someone else comes along
and modifies the LOB data and commits (or not), your LOB locator will be “as of 9:00 a.m.,” just like your resultset
would be. Here, Oracle uses the LOBSEGMENT along with the read-consistent view of the LOBINDEX to undo the
changes to the LOB, to present you with the LOB data as it existed when you retrieved the LOB locator. It does not use
the undo information for the LOBSEGMENT, since none was generated for the LOBSEGMENT itself.
We can see that LOBs are read-consistent easily. Consider this small table with an out-of-line LOB (it is stored in
the LOBSEGMENT):
EODA@ORA12CR1> create table t
2 ( id int primary key,
3 txt clob
4 )
5 lob( txt) store as ( disable storage in row )
6 /
Table created.
