Analysis of OracleLob type storage

In Oracle, multiple data types are provided for data table fields column and PLSQL, respectively, to cope with various types in actual development. The Lob type is a data type that stores large objects launched by Oracle. When we consider using the database as the storage carrier for information files (decimal, binary), images and even Audio Information, we need to use lob

In Oracle, multiple data types are provided for data table fields column and PL/SQL, respectively, to cope with multiple types in actual development. The Lob type is a data type that stores large objects launched by Oracle. When we consider using the database as the storage carrier for information files (decimal, binary), images and even Audio Information, we need to use lob

In Oracle, multiple data types are provided for data table fields column and PL/SQL, respectively, to cope with multiple types in actual development. The Lob type is a data type that stores large objects launched by Oracle. When we consider using the database as the storage carrier for information files (decimal, binary), images and even Audio Information, we need to use lob data.

Currently, Oracle supports four subtypes: CLOB, BLOB, NLOB, and BFILE. CLOB, BLOB, and NLOB both store data in the database, while the core of the BFILE type is the file pointer, and the real file is stored outside the database.

Compared with traditional data types, lob data has many special features in terms of management and space usage. This article describes some basic storage features of the lob type.

1, Environment preparation and Data segment features

We chose to perform the test under Oracle 10gR2.

SQL> select * from v $ version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

Use the create table command to create an experimental data table T.

SQL> create table t (id number, cl clob );

Table created

For a data table, a data table corresponds to only one data segment object. The special case here is the partition table. Generally, a partition corresponds to a separate storage object. When a data table contains a data column of the lob type, a unique segment object is created.

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME

----------------------------------------------------------------------

SYS_LOB0000056069C00002 $ LOBSEGMENT USERS

T TABLE USERS

SYS_IL0000056069C00002 $ LOBINDEX USERS

(Due to the length, irrelevant objects are omitted ......)

9 rows selected

We found that in addition to the conventional data segment T, we also added two segment objects obviously named by the system, with the types of lobsegment and lobindex respectively.

For Oracle lob data tables, creating a data table with the lob column corresponds to multiple data segments. In addition to the Data Segment Table Data Segment created by traditional Data tables, a lob column generates two specialized segments: lob Segment and lob index Segment.

The LobSegment corresponds to the data stored in the Lob column of the data table. There are two storage location structures for Oracle lob data columns. One is in-row storage, that is, the lob data of each row is stored in the data block together with the data of other columns in the form of rows. In this case, the value of the lob column is small. The other is out-of-row storage. When the lob object is large and cannot be saved in a data block, it can be stored in an independent lobsegment. When the out-of-row storage is used, the lob column in the Data row stores only a pointer reference pointing to the corresponding location of the lobsegment.

LobIndex is an index generated by Oracle for each Lob type. It is mainly used to accelerate lob data retrieval. Lobindex and lob columns coexist. An error is returned if the delete operation is performed forcibly.

SQL> drop index SYS_IL0000056069C00002 $;

Drop index SYS_IL0000056069C00002 $

ORA-22864: cannot ALTER or drop lob Index

2. lob type data table Original Definition Analysis

Using the dbms_metadata package, we can get all the definitions of the data table, including various default parameters and details.

Create table "SCOTT". "T"

("ID" NUMBER,

"CL" CLOB

) 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 ("CL") 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 ));

Create unique index "SCOTT". "SYS_IL0000056069C00002 $" ON "SCOTT". "T "(

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"

PARALLEL (DEGREE 0 INSTANCES 0 );

The extracted metadata consists of two parts: data table creation and lob index creation. In the data table creation section, we can see that lob is used as an independent segment object for storage settings and parameter settings.

In the lob index creation section, we can see that although it is the corresponding index creation statement, the index name is obviously the object name automatically generated by the system.

In addition, there are many special lob parameters, such as cache and enable storage in-row, which have a huge impact on the behavior and access performance of data table lob. Due to space limitations and content limitations, we will not go into detail in this article.

3. Transfer of lob segments and lob index Storage

For multiple segments involved in a data table, we often need to move them. The most common method is to use move to move the object tablespace.

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME

----------------------------------------------------------------------

SYS_LOB0000056099C00002 $ LOBSEGMENT USERS

T TABLE USERS

SYS_IL0000056099C00002 $ LOBINDEX USERS

9 rows selected

SQL> alter table t move tablespace system;

Table altered

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME

----------------------------------------------------------------------

T TABLE SYSTEM

SYS_LOB0000056099C00002 $ LOBSEGMENT USERS

SYS_IL0000056099C00002 $ LOBINDEX USERS

9 rows selected

The above experiment clearly shows that when the general move command is used, only the T segment of the data table will be moved to the new tablespace. The Lob segment and the corresponding lobindex segment remain unchanged. To move lob/lobindex, You need to perform additional operations separately.

SQL> alter index SYS_IL0000056069C00002 $ rebuild tablespace users;

Alter index SYS_IL0000056069C00002 $ rebuild tablespace users

ORA-02327: Unable to create index with expression for data type LOB

Lobindex cannot be directly reconstructed using rebuild.

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME

----------------------------------------------------------------------

T_LOGSEGMENT LOBSEGMENT USERS

SYS_IL0000056069C00002 $ LOBINDEX USERS

T TABLE EXAMPLE

9 rows selected

SQL> alter table t move lob (cl) store as t_logsegment (tablespaceexample );

Table altered

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME

----------------------------------------------------------------------

T TABLE EXAMPLE

SYS_IL0000056069C00002 $ LOBINDEX EXAMPLE

T_LOGSEGMENT LOBSEGMENTEXAMPLE

9 rows selected

Use the alter table xxx move lob (xx) store as xxx (tablespace xxx) command to adjust the storage location of the lob column.

When creating a data table, you can also use the data table space statement corresponding to lob (xxx) to store the space information of the lob object.

SQL> Create table DemoLob (A number, B clob)

2 LOB (B)

3 store as lobsegname (

4 TABLESPACE users

5 -- STORAGE (lobsegment storage clause)

6 INDEX lobindexname (

7 TABLESPACE example

8 -- STORAGE (lobindex storage clause)

9)

10)

11 TABLESPACE system

12 -- STORAGE (tables storage clause)

13;

Table created

SQL> select table_name, column_name, segment_name, tablespace_name, index_name from user_lobs;

TABLE_NAME COLUMN_NAM SEGMENT_NAME TABLESPACE_NAME INDEX_NAME

--------------------------------------------------------------------------------------------------------------

T cl T_LOGSEGMENT EXAMPLE SYS_IL0000056069C00002 $

In the actual physical design and deployment process, large object partitions and storage space are often deployed separately. You can save some large lob columns and indexes in a separate tablespace based on the actual situation.

Note that the lob and lobindex segments are in a tablespace. Even if SQL syntax is supported, the statements that store the lob segments and lobindex separately are usually ignored.

SQL> alter table t move lob (cl) store as T_LOGSEGMENT (tablespace example index t_logindex (tablespace users ));

Table altered

SQL> select table_name, column_name, segment_name, tablespace_name, index_name from user_lobs;

TABLE_NAME COLUMN_NAM SEGMENT_NAME TABLESPACE_NAME INDEX_NAME

--------------------------------------------------------------------------------------------------------------

T cl T_LOGSEGMENT EXAMPLE SYS_IL0000056069C00002 $

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME

----------------------------------------------------------------------

T TABLE EXAMPLE

SYS_IL0000056069C00002 $ LOBINDEX EXAMPLE

T_LOGSEGMENT LOBSEGMENT EXAMPLE

9 rows selected

4. Conclusion

The Lob type is a frequently used complex data type. There is a big difference between lob processing and management methods and our conventional methods. Pay special attention to both the development and O & M processes.