Creating a BasicFiles LOB-Datatypes
In 12c and above, to create a BasicFiles LOB, you’ll need to use the STORE AS BASICFILE syntax:
SQL> create table t( id int primary key,txt clob)
segment creation immediatelob(txt) store as basicfile;Table created.
Using the DBMS_METADATA package, we can see the details of a BasicFiles LOB:
SQL> select dbms_metadata.get_ddl( ‘TABLE’, ‘T’ ) from dual;DBMS_METADATA.GET_DDL(‘TABLE’,’T’)
CREATE TABLE “EODA”.”T”
(“ID” NUMBER(*,0),”TXT” CLOB, PRIMARY KEY (“ID”)USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE “USERS” ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
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) TABLESPACE “USERS”
LOB (“TXT”) STORE AS BASICFILE (TABLESPACE “USERS” ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING 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))
Most of the parameters for a BasicFiles LOB are identical to those of a SecureFiles LOB. The main difference is that the SecureFiles LOB storage clause contains fewer parameters (like no FREELISTS and FREELIST GROUPS in the LOB storage clause).
LOB Components
As shown in the DBMS_METADATA output in the prior sections, the LOB has several interesting attributes:
•\ A tablespace (USERS in this example)
•\ ENABLE STORAGE IN ROW as a default attribute
•\ CHUNK 8192
•\ RETENTION
•\ NOCACHE
•\ A full storage clause
These attributes imply there is a lot going on in the background with LOBs, and there is. A LOB column always results in what I call a multisegment object, meaning the table will use multiple physical segments. If we had created that table in an empty schema, we would discover the following:
SQL> select segment_name, segment_type from user_segments;SEGMENT_NAME SEGMENT_TY
An index was created in support of the primary key constraint—that is normal—but what about the other two segments, the LOBINDEX and the LOBSEGMENT? Those were created in support of our LOB column. The LOBSEGMENT is where our actual data will be stored (well, it might be stored in the table T also, but we’ll cover that in more detail when we get to the ENABLE STORAGE IN ROW clause).
The LOBINDEX is used to navigate our LOB, to find the pieces of it. When we create a LOB column, in general what is stored in the row is a pointer, or LOB locator. This LOB locator is what our application retrieves. When we ask for “bytes 1000 through 2000” of the LOB, the LOB locator is used against the LOBINDEX to find where those bytes are stored, and then the LOBSEGMENT is accessed. The LOBINDEX is used to find the pieces of the LOB easily.
You can think of a LOB then as a master/detail sort of relation. A LOB is stored in chunks or pieces, and any piece is accessible to us. If we were to implement a LOB using just tables, for example, we might do so as follows:
Create table parent( id int primary key,other-data…);
Create table lob
(id references parent on delete cascade, chunk_number int,data (n),primary key (id,chunk_number));
Conceptually, the LOB is stored very much like that—in creating those two tables, we would have a 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.
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.