Database Reference
In-Depth Information
The other reason could be for I/O performance. By default, LOBs are not cached in the buffer cache (more on
that later). Therefore, by default every LOB access, be it read or write, is a physical I/O—a direct read from disk or a
direct write to disk.
■
LOBs may be in line or stored in the table. In that case, the LOB data would be cached, but this applies only to
LOBs that are 4,000 bytes or less in size. We'll discuss this further in the section “IN rOW Clause.”
Note
Because each access is a physical I/O, it makes sense to segregate the objects you know for a fact will be
experiencing more physical I/O than most objects in real time (as the user accesses them) to their own disks.
It should be noted that the LOBINDEX and the LOBSEGMENT will
always be in the same tablespace
. You
cannot have the LOBINDEX and LOBSEGMENT in separate tablespaces. Much earlier releases of Oracle allowed
you to separate them, but versions 8
i
Release 3 and up at least do not allow you to specify separate tablespaces
for the LOBINDEX and LOBSEGMENT. In fact, all storage characteristics of the LOBINDEX are inherited from the
LOBSEGMENT, as we'll see shortly.
IN ROW Clause
The
CREATE TABLE
statement returned from
DBMS_METADATA
earlier, both the SecureFiles and BasicFiles included the
following:
LOB ("TXT") STORE AS ... (... ENABLE STORAGE IN ROW ...
This controls whether the LOB data is always stored separate from the table in the LOBSEGMENT or if it can
sometimes be stored right in the table itself without being placed into the LOBSEGMENT. If
ENABLE STORAGE IN ROW
is set, as opposed to
DISABLE STORAGE IN ROW
, small LOBs of up to 4,000 bytes will be stored in the table itself, much
like a
VARCHAR2
would be. Only when LOBs exceed 4,000 bytes will they be moved out of line into the LOBSEGMENT.
Enabling storage in the row is the default and, in general, should be the way to go if you know the LOBs will many
times fit in the table itself. For example, you might have an application with a description field of some sort in it. The
description might be anywhere from 0 to 32KB of data (or maybe even more, but mostly 32KB or less). Many of the
descriptions are known to be very short, consisting of a couple of hundred characters. Rather than going through
the overhead of storing these out of line and accessing them via the index every time you retrieve them, you can
store them in line, in the table itself. Further, if the LOB is using the default of
NOCACHE
(the LOBSEGMENT data is
not cached in the buffer cache), then a LOB stored in the table segment (which is cached) will avoid the physical I/O
required to retrieve the LOB.
■
starting with Oracle 12
c
, you can create a
VARCHAR2
,
NVARCHAR2
, or
RAW
column that will store up to 32,767
bytes of information. see the “extended Datatypes” section in this chapter for details.
Note
We can see the effect of this with a rather simple example. We'll create a table with a LOB that can store data in
row and one that cannot:
EODA@ORA12CR1> create table t
2 ( id int primary key,
3 in_row clob,
4 out_row clob
5 )
