Oracle Data Types and storage methods (IV)

Part 4 LOB type
§ 4.1 LOB type
4.1.1 LOB type classification
CLOB: character LOB. used to store a large amount of text information. It is stored using the default character set.
NCLOB: used to store character LOB. It uses the National Character Set of the database to store characters, rather than the default Character Set of the database.
BLOB: Binary LOB, which stores a large amount of binary information. Character Set conversion is not performed during storage.
CLOB and BLOG can store 8 TB bytes in ORACLE 10 Gb.
BFILE: binary file LOB, which is just a file pointer. The specific file is stored in the operating system.
4.1.2 LOB Storage
We call CLOB, NCLOB, and BLOB stored in the database as internal LOB. These storage methods are similar, so we can discuss them together.
SQL> create table test_lob (id int primary key, remark clob );
Table created
There are many options for creating the LOB column. You can check the ORACLE document.
The simplest is to use dbms_metadata to obtain its complete script.
Select dbms_metadata.get_ddl ('table', 'test _ lob') from dual;
The following result is displayed.
Create table "YUAN". "TEST_LOB"
("ID" NUMBER (*, 0 ),
"REMARK" 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 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) 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)
TABLESPACE "USERS"
LOB ("REMARK") store (
TABLESPACE "USERS" enable storage in row chunk 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT ))
The LOB column can have the following attributes.
The stored tablespace. In this example, the tablespace is USER. That is to say, the tablespace can be specified separately for LOB.
The default attribute of ENABLE STORAGE IN ROW
CHUNK attributes
PCTVERSION attributes
NOCACHE attribute.
A complete STORAGE statement.
It can be seen that the data type described before the LOB type is much more complex than the data type.
When we create a table with a LOB column, we can find it from USER_SEGMENTS. Several segment objects are added to the database.
SQL> select segment_name, segment_type from user_segments;
SEGMENT_NAME SEGMENT_TYPE
---------------------------------------------------
BIN $ nzwc1_dmqm + ygfB1U8tcIw ==0 0 TABLE
BIN $0jfW0nNQR/2 JEQmbAmfcRQ = $0 TABLE
TEST_TIMESTAMP TABLE
TEST_TIMESTAMP2 TABLE
TEST_TIMESTAMPWZ TABLE
TEST_TIMELTZ TABLE
TEST_INTERVARYM TABLE
TEST_INTERVALYM2 TABLE
TEST_INTERVALDS TABLE
TEST_LOB TABLE
SYS_LOB0000043762C00002 $ LOBSEGMENT
SYS_IL0000043762C00002 $ LOBINDEX
SYS_C004324 INDEX
The following four segment space objects are added. Four physical segments are added. For a common TABLE, only one or two segment objects are added. The type is TABLE and INDEX.
In the LOB column, two segment objects are added. The types are LOBSEGMENT and LOBINDEX.
SYS_C004324 is an index segment, because we have a column as the primary key.
As a common field, the data is stored in the table segment. The index is placed in the index segment.
For LOB data, the data is not stored in the table segment, but stored in the LOBSEGMENT segment. (in some cases, it is stored in the test_lob table. I will talk about it later)
LOBINDEX is used to point to the LOB segment and find a part of it.
Therefore, the LOB stored in the table stores an address, a pointer, or a LOB locator ).
The address of each LOB row should be stored in LOBindex. The data is stored in LOBSEGMENT.
We first find an address in the LOB column of TEST_LOB, and then find the location where these bytes are stored in LOBINDEX. then access LOBSEGMENT. therefore, we can think of lobindex and lobsegment as a relationship between the master table and the fine table.
In fact, the lob column stores an address segment. Then, find all the address segments in lobindex. Then, read the values of all address segments in lobSegment.
4.1.3 LOB storage Parameters
Here, we have a basic understanding of how LOB is stored. We also see the LOB type parameters in the script. Now we will understand these parameters.
1. LOB tablespace
LOB ("REMARK") store (
TABLESPACE "USERS"
The create statement in the test_lob table contains the preceding statement. the specified tablespace refers to the tablespace that stores lobindex and lobsegment. that is to say, the tables that store the lob data and the LOB column can be in different tablespaces.
Data Tables and LOB are stored in different tablespaces.
Why is the LOB data stored in different tablespaces? This is mainly about management and performance.
The LOB data type represents a very large capacity. before ORACLE 10 Gb, the LOB column can store 4 GB of data. in ORACLE 10 Gb, the LOB type can store 8 TB data. this is very huge data.
Therefore, it is necessary to use a separate tablespace for LOB data for backup, recovery, and space management. you can even use the size of another segment for the LOB data, instead of the size of the segment used by the common table data.
In addition, I/O performance is considered. LOB is not cached in the buffer cache. therefore, each LOB read/write will generate physical I/O. because of this, it is clear that some objects require more physical I/O than most other objects in actual user access, you need to separate these objects from other disks.
In addition, lobindex and lobsegment are in the same tablespace. you cannot place lobindex and lobsegment in different tablespaces. in versions earlier than oracle 8i, lobindex and lobsegment can be placed in different tablespaces.
2. in row statement
LOB ("REMARK") store (
TABLESPACE "USERS" ENABLE STORAGE IN ROW
We have learned about the LOB storage structure, but this structure will bring additional disk access. both read and write operations will be slower than normal data types and lead to more physical I/O.
IN this case, ORALCE makes an improvement by the in row statement.
Using enable storage in row literally means allowing IN-row storage. when the LOB content is less than 4000 bytes, the data is stored in the data table, that is, the LOB data and the data table are both in the same tablespace. here, the LOB is equivalent to VARCHAR2. Here, the data in the LOB column can also be stored in the buffer zone. when the LOB content exceeds 4000 bytes, the data is moved to the lobsegment.
When defining a LOB column, its size is generally smaller than 4000 bytes. Enabling in row is very important.
If you want to disable in row, use DISALBE STORAGE IN ROW
3. CHUNK Parameters
LOB ("REMARK") store (
TABLESPACE "USERS" enable storage in row chunk 8192
CHUNK indicates a large block. refers to the unit of LOB storage. the index pointing to the LOB data points to each data block. a chunk is a logical continuous set of data blocks. a chunk is the minimum allocation unit of a LOB. the Minimum Memory Allocation unit of the database is the data BLOCK ). the CHUNK size must be an integer multiple of the ORACLE block size.
Let's take a look at the relationship between LOB and CHUNK.
1. Each LOB instance (that is, the LOB value of each row) occupies at least one CHUNK.
Take the data table test_lob in this section as an example. The remark column is of the LOB type.
Assume that the table has 1000 rows of data, and the value of the remark column in each row is 7 kb.
In this way, the database will allocate 1000 chunks. if the CHUNK size is set to 64 KB, 1000 64 kB chunks will be allocated. if the CHUNK size is 8 KB, 1000 8 KB chunks are allocated.
One important point is that a CHUNK can only be used by one LOB object. it has a fixed length type like CHAR. if we set the CHUNK to 64 KB, but in fact each of our LOB objects is only 7 kb, each column will waste kb space. 1000 columns waste 55 MB of space. setting CHUNK to 8 KB wastes about 1 MB of space for the 1000 columns.
We also know lobindex and point to each block. it records the addresses of each block. therefore, when there are more blocks, the larger the index, and the larger the index, the slower the read/write speed. the overall performance will be reduced.
For example, if the actual LOB field value of each column is about 8 MB and the CHUNK of 8 KB is used, 1024 chunks are required. In lobindex, 1024 records are generated to point to these chunks.
Specify the CHUNK value, which affects the performance and space.
If the CHUNK is too large, the storage space will be wasted. If the CHUNK is too small, the performance will be reduced.
Therefore, we need to make trade-offs between space and performance.
4. PCTVERSION statement
LOB ("REMARK") store (
TABLESPACE "USERS" enable storage in row chunk 8192 PCTVERSION 10
PCTVERSION is used to control the read consistency of LOB. common fields all have UNDO records. lobsegment does not have an undo record. instead, the Stop version is directly maintained in the lobsegment itself. lobindex generates undo records like other segments. but lobsegment won't.
When a LOB object is modified, oracle allocates a new CHUNK, And the CHUNK is retained. if the transaction is committed normally, lobindex points to a new CHUNK. if the transaction is rolled back, lobindex then refers to the original CHUNK. therefore, undo maintenance is implemented in the LOB segment itself.
In this way, a lot of useless chunks will be overhead. this is also a very large space loss. these chunks refer to the old data version. how can we control the space occupied by these old versions of data? This is the role of PCTVERSION. that is to say, how much extra space is used to store old version data. the default value is 10%. if you do modify LOB frequently, it is not enough to set it to 10%. You need to add this value.
5. CACHE Parameters
LOB ("REMARK") store (
TABLESPACE "USERS" enable storage in row chunk 8192 PCTVERSION 10
NOCACHE
In addition to NOCACHE, this option also applies to CACHE and cache reads. this parameter controls whether the lobsegment data is stored in the buffer cache. the default value is NOCACHE, that is, each access directly reads and writes from the disk.
Cache reads allows you to CACHE the LOB data read from the disk, but write data into the disk directly.
The CACHE allows both read and write operations to cache lob data.
In some cases, the size of the LOB field is only several KB, which is very useful for caching. if the data is not cached, you must wait for the LOB field to be updated and read and write data directly from the disk.
To modify the cache settings, use the following statement:
Alter table test_lob modify LOB (remark) (CACHE );
Alter table test_lob modify LOB (remark) (NOCACHE );
Alter table test_lob modify LOB (remark) (CACHEREADS );
However, for LOB reads and writes with a large data volume, for example, if it exceeds 20 mb, there is no reason to put it into the cache.
§ 4.2 BFILE
The BFILE type is only a pointer to a file on the operating system. It is used to provide read-only access to files stored in the operating system.
When using BFILE, you can also use a DIRECTORY object. DIRECTORY is a string that maps an operating system DIRECTORY to a database, so as to provide value portability.
SQL> create table test_bfile (id int primary key, moviefile bfile );
Table created
SQL> create or replace directory movie_directory as 'd:/movie ';
Directory created
SQL> insert into test_bfile values (1, bfilename ('movie _ directory', 'hero. dat '));
1 row inserted
The DBMS_LOB package is required for BFILE operations. A system method and function are provided.