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

This article mainly shows you "how to automatically create LOB index segments and rebuild the index", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to automatically create LOB index segments and rebuild the index" this article.

The LOBs, or Large Objects field, is a type of field used in Oracle to handle recommendations for storing non-character data, such as mp3,video, picture, and long string data. Binary large objects, or BLOBs, character large objects, or CLOBs, can store TB data.

The LOB column has many related properties, and each LOB column property can use "LOB (lobcolname) STORE AS..." To describe it in this grammar.

A table with LOBs field types (CLOB,NCLOB and BLOB) creates two additional disk segments segment,LOBINDEX and LOBSEGMENT for each LOB column. You can view them and the LOB properties through the DBA_LOBS,ALL_LOBS or USER_LOBS data dictionary and view the chart.

Following the example of MOS (1490228.1), you can specify tablespaces for LOBINDEX and LOBSEGMENT (versions prior to 8i allowed LOBINDEX and LOBSEGMENT to use different tablespaces):

Create table DemoLob (A number, B clob) LOB (b) STORE AS lobsegname (TABLESPACE lobsegts STORAGE (lobsegment storage clause) INDEX lobindexname (TABLESPACE lobidxts STORAGE (lobindex storage clause) TABLESPACE tables_ts STORAGE (tables storage clause))

Let's do a simple experiment to prove that a table with a LOB column automatically creates an LOB index:

SQL > create table ml_test1 (a clob)

Table created

SQL > create index idx_ml_test1 on ml_test1 (a)

Create index idx_ml_test1 on ml_test1 (a)

*

ERROR at line 1:

ORA-02327: cannot create index on expression with datatype LOB

ORA-02327: cannot create an index with an expression of data type LOB

Indicates that you cannot create an index on a LOB column using CREATE INDEX.

SQL > col table_name for A10

SQL > select index_name,status,index_type,table_name from user_indexes where table_name='ML_TEST1'

INDEX_NAME STATUS INDEX_TYPE TABLE_NAME

-

SYS_IL0000013716C00001 $$VALID LOB ML_TEST1

Check that an index has been automatically created for the LOB column.

Col segment_name for a25

SQL > select segment_name, segment_type, bytes from user_segments where segment_type like'% LOB%' or segment_name like'% ML%'

SEGMENT_NAME SEGMENT_TY BYTES

SYS_IL0000013716C00001 $$LOBINDEX 65536

SYS_LOB0000013716C00001 $$LOBSEGMENT 65536

ML_TEST1 TABLE 65536

See that the table contains three segment types: TABLE, LOBINDEX, and LOBSEGMENT.

When stacking a table MOVE, the LOB type field and the index will not follow the MOVE, and you must manually manipulate the MOVE:

(MOS) ALTER TABLEtest MOVE TABLESPACE tbs1 LOB (lob1, lob2) STORE AS (TABLESPACE tbs2 DISABLE STORAGE IN ROW)

Alter tableml_test1 move tablespace users

Alter tableml_test1 move lob (a) store as (tablespace users)

Alter table ml_test1 move tablespace users LOB (a) STORE AS lobsegment (TABLESPACE users)

In addition to being used for mobile LOB, alter table can also be a way to rebuild the index of LOB fields, and you cannot rebuild LOBINDEX or LOBSEGMENT alone.

The above is all the contents of the article "how to automatically create LOB index segments and re-index". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.