Shulou(Shulou.com)06/01 Report--

What is LOB?

Lob is a large object data type of oracle database, which can store more than 4000bytes strings, binary data, OS files and other large object information. The version of the root oracle of the maximum storable capacity is related to the oracle block size.

What types of LOB are available?

At present, ORACLE provides CLOB,NCLOB,BLOB,BFILE a total of four LOB types, CLOB,NLOB is a large string type, NLOB is a multi-language set character type, similar to NVARCHAR type, using them to replace the previous LONG type; BLOG is a large binary type, used to replace the previous LONG RAW type; BFILE can store a variety of files in the operating system.

What are Internal LOBs and External LOBs?

Internal LOBs means that LOB data is stored in oracle's tablespace, CLOB,NCLOB,BLOG is Internal LOBs;, and External LOBs refers to LOB data stored in operating system files outside the database. BFIEL is the only External LOBs.BFILE that provides a way for us to access external files in SQL.

How much data can LOB store?

BFILE: Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter Which is itself limited by the maximum number of open files the operating system will allow.BLOB: Maximum size: (4 GB-1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns per table is limited only by the maximum number of columns per table (that is 10001). CLOB: Maximum size: (4 GB-1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns per table is limited onlyby the maximum number of columns per table (that is,10001). NCLOB: Maximum size: (4 GB-1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns per table is limited onlyby the maximum number of columns per table (that is,10001).

Where can LOB be used?

In addition to defining the type of column in TABLE, LOB can also be used to store XML data, the collection type of ORACLE, see the following example:

1) definition of create table lobtest (a varchar2 (20), b clob, c blob) tablespace users;-lob table

2) LOB storage collection type

Create tablespace person_lob datafile size 102464K autoextend on next 100M maxsize 1024000K extent management local uniform size 1M segment space management auto

/

Alter user mayp quota unlimited on person_lob

Create or replace type person_att is object (id number (5), name varchar2 (30), sex varchar2 (1), age number (5))

/

Create or replace type person_atts is varray (5) of person_att

/

Create table person (person_id number (5), attribute1 person_atts)

Varray attribute1 store as lob person_attribute (

Tablespace person_lob

Enable storage in row

Chunk 16384

Pctversion 5

Cache reads

Index person_lob_idx)

Tablespace users

Pctfree 0

/

An example of using lob to store varray type data is defined above.

What are the attributes of the LOB section?

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. An unreasonable definition of chunk is not as good as a waste of storage space and can also affect 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:

Data Size CHUNK Size Disk Space Used to Store the LOB Space Utilization (Percent) 3500 enable storage in row irrelevant 3500 in row 100 3500 disable storage in row 32 KB 32 KB 10 3500 disable storage in row 4 KB 4 KB 90 33 KB 32 KB 64 KB 51 2 GB + 10 32 KB 2 GB + 32 KB 99 +

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 in LOB than chunk 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, no matter how large the lob data is, oracle will store lob data in lob segment, so the above

3500 disable storage in row 32 KB, 32 KB, 10 cases, wasting 90% of storage space.

Pctversion n / retention: these two attributes are used to solve the problem of consistent reading of lob segments. The particularity of lob determines that it cannot use undo/rollback segment to manage its own updated old version. Usually, lob will divide part of its tablespace to manage its own undo. Ensure that the update principle in read consistent.lob is to allocate a new chunk in the lob segment to insert the 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 the lob segment. Pctverision is a percentage, which defines the percentage of all the 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 and reuses the pre-mirrored space. 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. The decision to adopt this undo method must be made after testing the application.

Nocache/cache reads/cache: defines the cache mode of LOB. Nocache is not cache any lob data; cache reads is cache data in the case of lob read; cache is both read and write cache data.

Example created by lob:

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

/

Can LOB operate in a SQL environment?

LOB can perform DML operations just like other data types.

Insert into person_new values (1 grammatical robin.makeshift is a goold boy',empty_blob ())

Update person_new set remark='he is a goog boy,he is from Beijing China' where id = 1

Delete from person_new where id = 1

Commit

Create table bfile_test (files bfile) tablespace users

Insert into bfile_test values (bfilename ('EXPORT','c.dat'))

Commit

SQL > select * from bfile_test

SP2-0678: Column or attribute type can not be displayed by SQL*Plus

SQL > select * from person_new

SP2-0678: Column or attribute type can not be displayed by SQL*Plus

SQL > select remark from person_new

REMARK

-

He is a goog boy,he is from Beijing China

As you can see from the above example, BLOG and BFIEL cannot be displayed directly in sql*plus.

What are the API that can operate LOB?

PL/SQL can manipulate any LOB,BFILE object with DBMS_LOB, and Oracle also provides an OCI programming interface to manipulate LOB. Java,C# provides the corresponding API operation LOB.

How to convert other character types to CLOB and binary types to BLOB?

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.

Can LOB objects be operated remotely by db link?

In addition to BFILE,CLOB,BLOB, remote LOB objects can be accessed and manipulated locally. Currently, the following operations are allowed:

CREATE TABLE AS SELECT... FROM table@remote

INSERT INTO.. SELECT * FORM table@remote

UPDATE... SET.. = (SELECT.. FROM table@remote)

INSERT INTO TABLE@REMOTE (…) AS SELECT * FROM LOCAL_TABLE

UPDATE remote@table SET.. = (SELECT.. FROM local_table)

DELETE FROM remote@table WHERE... ..

LOB-related SQL function and DBMS_LOB do not allow access to and manipulation of remote LOB objects.

Can the LOB column be indexed?

Currently, the CLOB column supports Oracle Text Index,Domain Index,Function-Base Index,Extensible Index.

Does LOB support partitioned tables?

You can use LOB columns in partitioned tables and also support operations such as exchange partition,move partition,merge partition.

Does LOB support Index-organization table?

Oracle supports creating clob,blog and bfile columns in index-organization table, but not lob columns in partitioned index-organization table.

Reference: Oracle Database Application Developer's Guide-Large Objects 10g Release 2

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.