Database Reference
In-Depth Information
Most of the parameters for a BasicFiles LOB are identical to those of a SecureFiles LOB. The main differences
being that the SecureFiles LOB storage clause contains fewer parameters (like no FREELISTS and FREELIST GROUPS in
the LOB storage clause).
LOB Components
As shown in the DBMS_METADATA output in the prior sections, the LOB has several interesting attributes:
A tablespace (
USERS in this example)
ENABLE STORAGE IN ROW as a default attribute
CHUNK 8192
RETENTION
NOCACHE
A full storage clause
These attributes imply there is a lot going on in the background with LOBs, and there is. A LOB column always
results in what I call a multisegment object , meaning the table will use multiple physical segments. If we had created
that table in an empty schema, we would discover the following:
EODA@ORA12CR1> select segment_name, segment_type from user_segments;
SEGMENT_NAME SEGMENT_TY
------------------------------ ----------
T TABLE
SYS_LOB0000020053C00002$$ LOBSEGMENT
SYS_IL0000020053C00002$$ LOBINDEX
SYS_C005432 INDEX
An index was created in support of the primary key constraint—that is normal—but what about the other
two segments, the LOBINDEX and the LOBSEGMENT? Those were created in support of our LOB column. The
LOBSEGMENT is where our actual data will be stored (well, it might be stored in the table T also, but we'll cover that
in more detail when we get to the ENABLE STORAGE IN ROW clause). The LOBINDEX is used to navigate our LOB, to
find the pieces of it. When we create a LOB column, in general what is stored in the row is a pointer , or LOB locator .
This LOB locator is what our application retrieves. When we ask for “bytes 1,000 through 2,000” of the LOB, the LOB
locator is used against the LOBINDEX to find where those bytes are stored, and then the LOBSEGMENT is accessed.
The LOBINDEX is used to find the pieces of the LOB easily. You can think of a LOB then as a master/detail sort of
relation. A LOB is stored in chunks or pieces, and any piece is accessible to us. If we were to implement a LOB using
just tables, for example, we might do so as follows:
Create table parent
( id int primary key,
other-data...
);
Create table lob
( id references parent on delete cascade,
chunk_number int,
data <datatype>(n),
primary key (id,chunk_number)
);
 
Search WWH ::




Custom Search