Database Reference
In-Depth Information
Conceptually, the LOB is stored very much like that—in creating those two tables, we would have primary key on
the LOB table on the
ID,CHUNK_NUMBER
(analogous to the LOBINDEX created by Oracle), and we would have a table
LOB
storing the chunks of data (analogous to the LOBSEGMENT). The LOB column implements this master/detail
structure for us transparently. Figure
12-3
might make this idea clearer.
TABLE T
id
1
Txt
LOBINDEX
0xABCD01234
2
0xAFDE0023
...
...
0x01AB34DF
99
LOBSEGMENT
0xABCD01234
Chunk 10
0xAFDE0023
Chunk 1
0xABCD01234
Chunk 42
0xAFDE0023
Chunk 2
Figure 12-3.
Table to LOBINDEX to LOBSEGMENT
The LOB locator in the table really just points to the LOBINDEX; the LOBINDEX, in turn, points to all of the
pieces of the LOB itself. To get bytes N through M of the LOB, you would dereference the pointer in the table (the LOB
locator), walk the LOBINDEX structure to find the needed chunks, and then access them in order. This makes random
access to any piece of the LOB equally fast—you can get the front, the middle, or the end of a LOB equally fast, as you
don't always just start at the beginning and walk the LOB.
Now that you understand conceptually how a LOB is stored, I'd like to walk through each of the optional settings
listed previously and explain what they are used for and what exactly they imply.
LOB Tablespace
The
CREATE TABLE
statement returned from
DBMS_METADATA
both the SecureFiles and BasicFiles included the
following:
LOB ("TXT") STORE AS ... ( TABLESPACE "USERS" ...
The
TABLESPACE
specified here is the tablespace where the LOBSEGMENT and LOBINDEX will be stored, and
this may be different from the tablespace where the table itself resides. That is, the tablespace that holds the LOB data
may be separate and distinct from the tablespace that holds the actual table data.
The main reasons you might consider using a different tablespace for the LOB data versus the table data are
mostly administrative and performance related. From the administrative angle, a LOB datatype represents a sizable
amount of information. If the table had millions of rows, and each row has a sizeable LOB associated with it, the
LOB data would be huge. It would make sense to segregate the table from the LOB data just to facilitate backup and
recovery and space management. You may well want a different uniform extent size for your LOB data than you have
for your regular table data, for example.
