LOB Tablespace-Datatypes
The CREATE TABLE statement returned from DBMS_METADATA for 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 vs. 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 sizable 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.
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.
Note 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 4000 bytes or less in size. We’ll discuss this further in the section “IN ROW Clause.”
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. In fact, all storage characteristics of the LOBINDEX are inherited from the LOBSEGMENT, as we’ll see shortly.