Shulou(Shulou.com)05/31 Report--

This article mainly talks about "what are the types of LOB". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what are the LOB types"?

Concepts related to LOB

LOB type:

When the information file (decimal, binary), image and even audio information is used as the storage carrier, lob type data is needed.

There are two kinds of Lob,Internal Lob and External Lob. Internal Lob means that the Lob data is stored in the Oracle data file, and External Lob refers to the Lob data stored in the operating system outside the database.

CLOB: stores large amounts of single-byte, character data, stored in an internal tablespace, and is used to store Lob of string types, such as text and XML files, which are encoded by the database character set.

NLOB: stores fixed width, multi-byte, character data, multi-byte country character data, stored in internal table space.

BLOB: stores large unstructured binary data in an internal tablespace.

BFILE: stores binaries in operating system files outside the database. The path to the file. The database stores a pointer to execute external files, so it is read-only.

The difference between Internal Lob and External Lob:

Internal Lob contains CLOB, NLOB, and BLOB;External Lob, only BFILE.

Internal LOB can be saved in a table as a column of a table, and external LOB can be saved in a file on the operating system.

Internal LOB stores data inside the database in the form of byte streams. Many operations of Internal LOB can participate in transactions and can be backed up and restored just like normal data.

External Lob, that is, BFILE type. In the database, this type stores only the location information of the data in the operating system, while the entity of the data exists in the file system of the operating system in the form of external files. Therefore, the data represented by this type is read-only and does not participate in the transaction.

Internal LOBs use copy semantics. That is when you INSERT or UPDATE a LOB with a LOB from another row in a table, the LOB locator as well as the LOB value are copied to the row. External LOBs on the other hand use reference semantics. That is only the BFILE location is copied and not the actual operating system file.

Each internal LOB column has a distinct LOB locator for each row and a distinct copy of the LOB value. Each BFILE column has its own BFILE locator for each row. However you could have two rows in the table that contain BFILE locators pointing to the same operating system file.

For a general data table, a data table corresponds to only one data segment object that stores data segments. For partition tables, usually a partition corresponds to a separate storage object.

When the data table includes data columns of type lob, there are also unique segment objects created. In addition to the regular segments, two obviously system-named segment objects are added, with the types lobsegment and lobindex, respectively.

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

The Lob segment (LobSegment) corresponds to the data stored on the lob column of the data table. In the lob type data column of Oracle, there are two kinds of save location structures. One is in-row storage, where the lob data for each row is stored in the data block as rows along with the data from the other columns. The value of the lob column in this case is small. The other is out-of-row storage, which can be saved in a separate lobsegment when the lob object is too large to be saved in a data block. When out-of-row storage, all that is saved on the lob column in the data row is a pointer reference to the corresponding location of the lobsegment.

The Lob index segment (LobIndex) is the index that Oracle forces to generate for each lob type column, and its main function is to accelerate the operation of lob type data retrieval. Lob index is symbiotic with lob columns, and if a delete operation is forced, an error will be reported.

SQL > drop index SYS_IL0000056069C00002 $$

Drop index SYS_IL0000056069C00002 $$

ORA-22864: cannot ALTER or DROP LOB index

In the actual physical design deployment process, there are often cases where large objects are partitioned and stored to deploy tablespaces separately. Depending on the actual situation, you can save some large lob columns along with the index in a separate tablespace.

Note, however, that in general data tables, the lob segment and the lob index segment are on the same table space. Even if there is support for SQL syntax, statements that store lob segments and lobindex separately are often ignored.

Lob and other types of conversions:

You can convert CHAR,NCHAR,VARCHAR2,NVARCHAR2,NCLOB types to CLOB through TO_CLOB

You can convert LONG RAW to BLOB,LONG to CLOB through TO_LOB

CHAR,NCHAR,VARCHAR2,NVARCHAR2,CLOB can be converted to NCLOB through TO_NCLOB.

LOB segment attributes:

By default, when a table with a LOB field is defined, oracle automatically creates two segments for each LOB, lob segment and lob index segment. Lob segment stores the specific value of each lob, while lob index segment stores the address of each lob value. Lob segment, lob index segment, and table segment are stored in the same table space. Oracle provides separate segment attributes for lob segments. When we create a table, we can define that lob and table are stored in different tablespaces. When defining lob in general, we must consider the following more important attributes:

Chunk: a larger logical block than oracle block size, dedicated to the storage of LOB data, defaults to the size of db_block_size, and must be defined as a multiple of db_block_size if manually defined. The maximum cannot exceed 32K. Unreasonable chunk definition not only wastes storage space, but also affects performance. Then before defining it, we must understand the average size of the data of each LOB column, so as to minimize the waste of LOB space. Look at the table below to tell the story:

The above table illustrates the storage relationship between chunk and data with some data, and more vividly illustrates the interest rate of disk space. The part marked in red illustrates the unreasonable definition of chunk. It must be noted that the space wasted by chunk in LOB cannot be reused.

Disable/enable storage in row: enable storage in row by default, without separating lob segments, every row of data in table is stored in the same block, so if the lob column is very large, it may cause serious row links; when lob segments and table segments are separated, oracle will automatically store lob data less than 4k in table segment and lob data greater than 4k in lob segments.

If set to disable storage in row, when lob segment and table segment are separated, oracle will store lob data in lob segment no matter how large the lob data is. This leads to the above 3500 disable storage in row 32 KB, 32 KB, 10 cases, wasting 90% of the storage space.

Pctversion/retention: these two attributes are used to solve the problem of consistent reading of lob segments. The particularity of lob determines that it can not use undo/rollback segment to manage its own updated old version. Usually lob will divide part of its tablespace to manage its own undo and guarantee read consistent.

The update principle in lob is to allocate a new chunk in lob segment to insert new data and retain the old image. If there are multiple updates to a data, then there will be multiple versions. Pctversion is used to define the size of the undo area in lob segment. Pctverision is a percentage, which defines the percentage of all lob space used to store the previous image. If the previous image space exceeds this percentage, oracle does not automatically expand the size of this part. These pre-mirrored spaces are reused. If a lob segment segment is updated frequently, the growth of the lob segment may be rapid. Lob is a new parameter of 9i and can only be used when tablespace adopts ASSM. When lob is updated, the former image will be retained for a period of time, which is determined by the undo_retention parameter. Which undo method to use must be decided after testing the application.

Nocache/cache reads/cache: define the cache mode of LOB

Nocache does not cache any lob data

Cache reads is cache data in the case of lob read

Cache is cache data for both reading and writing.

Freepools integer: assign free list to log segment. Integer is the number of instances in RAC environment. 1 in a single instance environment

Index lobindexname (tablespace tablesapce_name ((storage.)): specify index storage parameters for lob columns

Example created by lob:

SQL > create tablespace person_lob datafile'+ data' size 10m

Tablespace created.

SQL > create table person_new (id number (5), name varchar2 (30), remark clob,photo blob not null)

Lob (remark) store as person_remark (

Tablespace person_lob

Enable storage in row

Chunk 8192

Pctversion 2

Cache reads

Index person_remark_idx)

Lob (photo) store as person_photo (

Tablespace person_lob

Disable storage in row

Chunk 16384

Pctversion 2

Cache reads

Index person_photo_idx)

Tablespace users

Pctfree 10

Table created.

-- query:

SQL > select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where SEGMENT_NAME='PERSON_NEW'

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

SYS PERSON_NEW TABLE USERS

SQL > select table_name,column_name,segment_name,tablespace_name,index_name,chunk from USER_LOBS WHERE table_name='PERSON_NEW'

TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK

- --

PERSON_NEW REMARK PERSON_REMARK PERSON_LOB PERSON_REMARK_IDX 8192

PERSON_NEW PHOTO PERSON_PHOTO PERSON_LOB PERSON_PHOTO_IDX 16384

SQL >

Create a lab environment:

-- create tablespaces test, test_ind, test_tmp, test_blob and test user test

SQL > create tablespace test datafile'+ DATA' size 20m

Tablespace created.

SQL > create tablespace test_ind datafile'+ DATA' size 20m

Tablespace created.

SQL > create temporary tablespace test_tmp TEMPFILE'+ DATA' size 20m

Tablespace created.

SQL > create tablespace test_blob datafile'+ DATA' size 20m

Tablespace created.

SQL > create user test identified by test default tablespace test temporary tablespace test_tmp

User created.

SQL > grant connect,resource to test

Grant succeeded.

SQL > create directory EXPDP as'/ home/oracle'

Directory created.

SQL > grant read,write on directory EXPDP to system

Grant succeeded.

-- expand the capacity of table spaces test_ind, test_blob, test

SQL > alter tablespace test_ind add datafile'+ DATA' size 400m

Tablespace altered.

SQL > alter tablespace test_blob add datafile'+ DATA' size 400m

Tablespace altered.

SQL > alter tablespace test add datafile'+ DATA' size 400m

Tablespace altered.

-- create a table T1 with lob fields under test:

SQL > conn test/test

Connected.

SQL >

SQL > create table T1 (id number (8), name varchar2 (10), addr blob,res clob,photo bfile)

Lob (addr) store as testblob

(tablespace test_blob

Chunk 16k

Disable storage in row

)

Lob (res) store as testclob

(tablespace test_blob

Chunk 16k

Disable storage in row

);

Table created.

SQL > show user

USER is "TEST"

SQL > select table_name,tablespace_name from user_tables

TABLE_NAME TABLESPACE_NAME

T1 TEST

SQL >

Tables are created in TEST tablespaces.

-load data:

SQL > Insert Into T1 Values (1 bfilename ('EXPDP','IMG_0210.JPG'))

1 row created.

SQL > Insert Into T1 Values (2 CLOB',bfilename CLOB',bfilename ('EXPDP','IMG_0210.JPG'))

1 row created.

SQL > Insert Into T1 Values (3) empty_clob (), null)

1 row created.

SQL > commit

-- query:

SQL > set lines 200

SQL > col COLUMN_NAME for A20

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

TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK

T1 ADDR TESTBLOB TEST_BLOB SYS_IL0000088708C00003 $16384

T1 RES TESTCLOB TEST_BLOB SYS_IL0000088708C00004 $16384

You can see that the SEGMENT names TESTBLOB and TESTCLOB are defined when the table is created.

SQL > conn / as sysdba

Connected.

SQL > col SEGMENT_NAME for A25

SQL > select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where OWNER='TEST'

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

TEST T1 TABLE TEST

TEST TESTCLOB LOBSEGMENT TEST_BLOB

TEST TESTBLOB LOBSEGMENT TEST_BLOB

TEST SYS_IL0000088708C00004 $$LOBINDEX TEST_BLOB

TEST SYS_IL0000088708C00003 $$LOBINDEX TEST_BLOB

You can see here that TESTCLOB, TESTBLOB, segment,SYS_IL0000087540C00004 $$, and SYS_IL0000087540C00003 $$, both of type LOBSEGMENT, are segment objects of type LOBINDEX and are created automatically.

Operation and management of Lob large objects

-- create a table with large object fields

SQL >! export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

SQL > grant read,write on directory EXPDP to test

Grant succeeded.

SQL > conn test/test

Connected.

SQL >

SQL > create table test001 (fname varchar2 (50), content blob)

Table created.

SQL > create table test002 (fname varchar2 (50), content clob)

Table created.

-- (1).. Ready to insert large object

-1. Create a file storage directory (for Oracle to manage, this directory)

It has been created before.

-- 2. You can authorize this directory to be accessed by other users

Grant read,write on directory EXPDP to scott

-- (2). Prepare to store large objects in the test001 table

Declare

Tempimg blob

Tempdir bfile: = bfilename ('EXPDP',' IMG_0210.jpg')

Begin

Insert into test001

Values

('IMG_0210.jpg', empty_blob ())

Returning content into tempimg

Dbms_lob.fileopen (tempdir)

Dbms_lob.loadfromfile (tempimg, tempdir, dbms_lob.getlength (tempdir))

Dbms_lob.fileclose (tempdir)

Dbms_output.put_line ('Congratulations, you finally made it!')

Commit

End

/

PL/SQL procedure successfully completed.

-- write Blob objects to disk files

Declare

L_file utl_file.file_type

L_buffer raw (32767)

L_amount binary_integer: = 3276

L_pos int: = 1

L_blob blob

L_blob_len int

Begin

Select content into l_blob from test001

L_blob_len: = dbms_lob.getlength (l_blob)

L_file: = utl_file.fopen ('EXPDP',' HHAHAHAHAHAHAHAHAAHA.JPG', 'wb')

While l_pos dbms_lob.read (l_blob, l_amount, l_pos, l_buffer)

Utl_file.put_raw (l_file, l_buffer, true); l_pos: =

L_pos + l_amount; end loop

Utl_file.fclose (l_file)

Dbms_output.put_line ('Congratulations, you finally made it!')

End

/

PL/SQL procedure successfully completed.

In the actual test, the size of HHAHAHAHAHAHAHAHAAHA.JPG is the same as that of IMG_0210.jpg and can be opened.

/ * Writing and reading of large text objects (clob) * /

-- the first way to write a text file

Declare

Tempimg clob

Tempdir bfile: = bfilename ('EXPDP',' 70093.txt')

Amount int: = dbms_lob.getlength (tempdir)

Src_offset int: = 1

Dest_offset int: = 1

Csid int: = 0

Lc int: = 0

Warning int

Begin

Insert into test002

Values

('FIRST', empty_clob ())

Returning content into tempimg

Dbms_lob.fileopen (tempdir)

Dbms_lob.loadclobfromfile (tempimg

Tempdir

Amount

Dest_offset

Src_offset

Csid

Lc

Warning)

Dbms_lob.fileclose (tempdir)

Dbms_output.put_line ('Congratulations on your success at last')

Commit

End

/

PL/SQL procedure successfully completed.

-- the second way to write a text file (judging the end of the file by exception)

Declare

Filecontent clob

Input_file utl_file.file_type

Buffer varchar2 (2000)

L_pos int: = 1

Amount int

Begin

Insert into test002

Values

('SECOND', empty_clob ())

Returning content into filecontent

Input_file: = utl_file.fopen ('EXPDP',' 2.txtlegs,'r')

Loop

Utl_file.get_line (input_file, buffer)

Amount: = length (buffer)

Exit when amount select 'alter table' | | t.table_name | | 'move tablespace tabespace_name lob (' | t.COLUMN_NAME | |') store as (tablespace tablespace_name); 'from user_lobs t

'ALTERTABLE' | | T.TABLE_NAME | |' MOVETABLESPACETABESPACE_NAMELOB ('| T.COLUMN_NAME | |') STOREAS (TABLESPACETABLESPACE_NAME);'

-

Alter table T1 move tablespace tabespace_name lob (ADDR) store as (tablespace tablespace_name)

Alter table T1 move tablespace tabespace_name lob (RES) store as (tablespace tablespace_name)

Alter table TEST001 move tablespace tabespace_name lob (CONTENT) store as (tablespace tablespace_name)

Alter table TEST002 move tablespace tabespace_name lob (CONTENT) store as (tablespace tablespace_name)

-- migrate the lob field of the T1 table to the test_ind tablespace:

SQL > alter table T1 move tablespace test lob (ADDR) store as (tablespace test_ind)

Table altered.

SQL > alter table T1 move tablespace test lob (RES) store as (tablespace test_ind)

Table altered.

SQL > select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where OWNER='TEST'

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

TEST SYS_LOB0000088713C00002 $$LOBSEGMENT TEST

TEST SYS_IL0000088713C00002 $$LOBINDEX TEST

TEST TEST001 TABLE TEST

TEST T1 TABLE TEST

TEST TESTCLOB LOBSEGMENT TEST_IND

TEST TESTBLOB LOBSEGMENT TEST_IND

TEST SYS_IL0000088708C00003 $$LOBINDEX TEST_IND

TEST SYS_IL0000088708C00004 $$LOBINDEX TEST_IND

8 rows selected.

SQL > select INDEX_NAME,OWNER,STATUS from dba_indexes where TABLE_NAME='T1' and TABLE_OWNER='TEST'

INDEX_NAME OWNER STATUS

SYS_IL0000088708C00003 $$TEST VALID

SYS_IL0000088708C00004 $$TEST VALID

You can see that the LOBSEGMENT and LOBINDEX type segment of the same lob field are moved to the TEST_IND tablespace at the same time, and the index is in a valid state.

-- Import and export with LOB word table

Create directory EXPDP as'/ home/oracle'

Grant read,write on directory EXPDP to system

Export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

-- having already done the above, import and export directly, as follows

[oracle@dbrac1 ~] $expdp system/oracle directory=EXPDP tables=test.t1 dumpfile=20171129exp.dmp logfile=20171129exp.log

Export: Release 11.2.0.4.0-Production on Wed Oct 18 12:33:55 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP

Data Mining and Real Application Testing options

Starting "SYSTEM". "SYS_EXPORT_TABLE_01": system/* directory=EXPDP tables=test.t1 dumpfile=20171129exp.dmp logfile=20171129exp.log

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Exported "TEST". "T1" 6.757 KB 3 rows

Master table "SYSTEM". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded

*

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

/ home/oracle/20171129exp.dmp

Job "SYSTEM". "SYS_EXPORT_TABLE_01" successfully completed at Wed Oct 18 12:35:14 2017 elapsed 0 00:01:04

[oracle@dbrac1 ~] $

-- then import

Import the table test to scott users, and save the lob field to test_blob in the user tablespace.

Check first:

SQL > conn scott/tiger

Connected.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

T4 TABLE

-- perform import operation again

[oracle@dbrac1 ~] $impdp system/oracle directory=EXPDP tables=test.t1 remap_schema=test:scott remap_tablespace=test:users,TEST_IND:TEST_BLOB dumpfile=20171129exp.dmp logfile=20171129imp.log

Import: Release 11.2.0.4.0-Production on Wed Oct 18 12:39:53 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP

Data Mining and Real Application Testing options

Master table "SYSTEM". "SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYSTEM". "SYS_IMPORT_TABLE_01": system/* directory=EXPDP tables=test.t1 remap_schema=test:scott remap_tablespace=test:users,TEST_IND:TEST_BLOB dumpfile=20171129exp.dmp logfile=20171129imp.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . Imported "SCOTT". "T1" 6.757 KB 3 rows

Job "SYSTEM". "SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 18 12:40:09 2017 elapsed 0 00:00:12

[oracle@dbrac1 ~] $

-- verify:

SQL > select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where OWNER='SCOTT'

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

SCOTT PK_EMP INDEX USERS

SCOTT PK_DEPT INDEX USERS

SCOTT T4 TABLE PARTITION USERS

SCOTT T4 TABLE PARTITION USERS

SCOTT T1 TABLE USERS

SCOTT SALGRADE TABLE USERS

SCOTT EMP TABLE USERS

SCOTT DEPT TABLE USERS

SCOTT TESTCLOB LOBSEGMENT TEST_BLOB

SCOTT TESTBLOB LOBSEGMENT TEST_BLOB

SCOTT SYS_IL0000088773C00004 $$LOBINDEX TEST_BLOB

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

SCOTT SYS_IL0000088773C00003 $$LOBINDEX TEST_BLOB

12 rows selected.

The data has been imported into user users, and the fields of the original lob are imported into the test_blob tablespace

LOB performance issu

The lob field generates lobindex and lobsegment by default, and without specifying a specific tablespace, the lob field index is stored under the system tablespace.

When lob is stored in a table, it can be cached, and it is much more efficient than lob stored in lobsegment (without lobindex).

When lob is stored in logsegment, it is not in the buffer cache by default, and the read and write of lob is physical IO, which is very expensive, so do not update lob fields larger than 4kb frequently, which is very inefficient.

When lob is stored in logsegment, you can define and specify the use of cache (default is nocache), which is very effective for medium-sized lob (for example, a few kilos to dozens of k), reducing physical IO.

At this point, I believe you have a deeper understanding of "what are the types of LOB?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.