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

This article mainly explains "what are the oracle lob classification rules". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what are the oracle lob classification rules.

What is lob?

The abbreviation of LOBs:Large Objects. A lob can hold a maximum of 8TB-128TB.

Lob classification

1. Internal LOBs

Lob is stored in the database, which is convenient for space management and reading efficiency. Internal LOBs is also divided into permanent and temporary. The permanent lob is stored inside the table with the database. Temporary lob is visible only by itself and exists in temporary tablespaces. Permanent lob supports transaction processing and media recovery.

Clob: stores character data in the character set defined by the database, with a fixed width, and can replace the long type.

Blob: stores binary data and can replace the long raw type.

Nclob: stores character data defined in the country character set, supporting variable length and width

two。 External LOBs

The external LOBs is not stored in the tablespace inside the database, but in the operating system file. The BFILE type is the only external LOBs data type. BFILE type is a read-only data type.

Bfile: external binaries

Comparison of lob with long or long raw types

In contrast, lob has the following advantages:

The long and long raws types can only store up to 2G data.

A table can have only one column of type long or long raw, but there can be many lob columns of different lob types.

Lob supports decentralized reads, while long only supports continuous reads.

Oracle recommends using lob instead of the long type.

A lob instance has a pointer (locator) and a value (value). Locator is the physical storage location of lob value, and lob value is the specific data stored by lob objects. In fact, the rows in the table only record the pointer to the lob object, not store the actual value.

Status of the lob column

Null: the table cell has been created, but the table cell has no pointer or value.

Empty: an instance of lob already exists in the table cell, but there is only a pointer and no value. The length of lob is 0.

Both the pointer and the value of the populated:lob instance exist.

Restrictions related to lob rules

1. Lob column primary key cannot be specified.

2.oracle has limited support for remote LOBs objects. There are only three ways left:

2.1 Create table as select or insert as select.

CREATE TABLE t AS SELECT * FROM table1@remote_site

INSERT INTO t SELECT * FROM table1@remote_site

UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site)

INSERT INTO table1@remote_site SELECT * FROM local_table

UPDATE table1@remote_site SET lobcol = (SELECT lobcol FROM local_table)

DELETE FROM table1@remote_site

2.2 Functions on remote LOBs returning scalars. The function of the remote lob returns scalar, as shown below:

CREATE TABLE tab AS SELECT DBMS_LOB.GETLENGTH@dbs2 (clob_col) len FROM tab@dbs2

CREATE TABLE tab AS SELECT LENGTH (clob_col) len FROM tab@dbs2

The following is not supported because DBMS_LOB.SUBSTR returns LOB, not a scalar:

CREATE TABLE tab AS SELECT DBMS_LOB.SUBSTR (clob_col) from tab@dbs2

2.3 data interface.

You can insert a character or binary buffer into a remote CLOB or BLOB, and select a remote CLOB or BLOB into a character or binary buffer. For example (in PL/SQL):

SELECT clobcol1, type1.blobattr INTO varchar_buf1, raw_buf2 FROM

Table1@remote_site

INSERT INTO table1@remotesite (clobcol1, type1.blobattr) VALUES varchar_buf1

Raw_buf2

INSERT INTO table1@remotesite (lobcol) VALUES ('test')

UPDATE table1 SET lobcol = 'xxx'

3. Cluster tables cannot include lob columns

4. The following data structures only support temporary lob objects and cannot be stored in table as permanent lob:

Variable length array of any lob type

The custom type includes lob, which is in the variable length array

Anydata type of any lob type

The custom type includes lob, which is also in anydata.

5.lob columns cannot appear in order by,group by or aggregate functions.

The first section of the 6.lob segment must contain at least three database blocks.

Cannot build index on 7.lob column. However, you can build a domain index.

The 8.lob column cannot be used in select distinct.

At this point, I believe you have a deeper understanding of "what are the oracle lob classification rules?" 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.