Datatypes - Expert Oracle Database Architecture - page 572

CACHE Clause
The CREATE TABLE statement returned from DBMS_METADATA previously included the following for both SecureFiles
and BasicFiles:

LOB ("TXT") STORE AS ... (... NOCACHE ... )

The alternative to NOCACHE is CACHE or CACHE READS . This clause controls whether or not the LOBSEGMENT data
is stored in the buffer cache. The default NOCACHE implies that every access will be a direct read from disk and every
write/modification will likewise be a direct read from disk. CACHE READS allows LOB data that is read from disk to be
buffered, but writes of LOB data will be done directly to disk. CACHE permits the caching of LOB data during both reads
and writes.
In many cases, the default might not be what you want. If you have small- to medium-sized LOBS (e.g., you are
using them to store descriptive fields of just a couple of kilobytes), caching them makes perfect sense. If they are not
cached, when the user updates the description field the user must also wait for the I/O to write the data to disk (an I/O
the size of a CHUNK will be performed and the user will wait for this I/O to complete). If you are performing a large load
of many LOBs, you will have to wait for the I/O to complete on each row as they are loaded. It makes sense to enable
caching on these LOBs. You may turn caching on and off easily:

ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE );
ALTER TABLE tabname MODIFY LOB (lobname) ( NOCACHE );

to see the effect this may have on you. For a large initial load, it would make sense to enable caching of the LOBs and
allow DBWR to write the LOB data out to disk in the background while your client application keeps loading more.
For small- to medium-sized LOBs that are frequently accessed or modified, caching makes sense so the end user
doesn't have to wait for physical I/O to complete in real time. For a LOB that is 50MB in size, however, it probably does
not make sense to have that in the cache.

■ Bear in mind that you can make excellent use of the Keep or recycle pools (discussed in Chapter 4) here. Instead
of caching the LOBseGMeNt data in the default cache with all of the regular data, you can use the Keep or recycle pools
to separate it out. In that fashion, you can achieve the goal of caching LOB data without affecting the caching of existing
data in your system.

Tip

LOB STORAGE Clause
And lastly, the CREATE TABLE statement returned from DBMS_METADATA previously included the following for
SecureFiles:

LOB ("TXT") STORE AS SECUREFILE (...
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

And here is the corresponding output for BasicFiles:

LOB ("TXT") STORE AS BASICFILE ( ...
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))