IN ROW Clause-Datatypes
The CREATE TABLE statement returned from DBMS_METADATA earlier for both theSecureFiles 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 4000 bytes will be stored in the table itself, much like a VARCHAR2 would be. Only when LOBs exceed 4000 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.
Note Starting with Oracle 12c, 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.
We can see the effect of this with a rather simple example. We’ll create a table with aLOB that can store data in row and one that cannot:
$ sqlplus eoda/foo@PDB1
SQL> create table t( id int primary key,in_row clob,out_row clob)lob (in_row) store as ( enable storage in row ) lob (out_row) store as ( disable storage in row );Table created.
Now, if we try to read out each row and, using the DBMS_MONITOR package, do this with SQL_TRACE enabled, we’ll be able to see the performance upon data retrieval of each:
SQL> declarel_cnt number;l_data varchar2(32765);beginselect count(*)into l_cnt
from t;dbms_monitor.session_trace_enable;for i in 1 .. l_cnt
The retrieval of the IN_ROW column was significantly faster and consumed far fewer resources. We can see that it used 54,720 logical I/Os (query mode gets), whereas the OUT_ROW column used significantly more logical I/Os. At first, it is not clear where these extra logical I/Os are coming from, but if you remember how LOBs are stored, it will become obvious. These are the I/Os against the LOBINDEX segment in order to find the pieces of the LOB. Those extra logical I/Os are all against this LOBINDEX.
Additionally, you can see that the retrieval of 18,240 rows with out-of-row storage incurred 18,240 physical I/Os and resulted in 18,240 I/O waits for direct path read. These were the reads of the noncached LOB data. We might be able to reduce them in this case by enabling caching on the LOB data, but then we’d have to ensure we had sufficient additional buffer cache to be used for this. Also, if there were some really large LOBs in there, we might not really want this data to be cached.
This in-row/out-of-row storage will affect modifications as well as reads. If we were to update the first 100 rows with short strings and insert 100 new rows with short strings and use the same techniques to monitor performance as follows:
SQL> create sequence s start with 100000; Sequence created.
SQL> declarel_cnt number;l_data varchar2(32765);
we would discover findings similar to the following output in the resulting TKPROF report:
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 66 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 66 (recursive depth: 1)
Number of plan statistics captured: 1
As we can see, the update of the out-of-line LOB consumed measurably more resources. It spent some amount of time doing direct path writes (physical I/O) and performed many more current mode gets. These were in response to the fact that the LOBINDEX and LOBSEGMENT had to be maintained in addition to the table itself. The INSERT activity shows the same disparity:
INSERT INTO T (ID, IN_ROW) VALUES ( S.NEXTVAL, ‘Hello World’ )
Note the increased I/O usage, both on the read and writes. All in all, this shows that if you use a CLOB, and many of the strings are expected to fit in the row (i.e., will be less than 4000 bytes), then using the default of ENABLE STORAGE IN ROW is a good idea.