oracle之 Oracle LOB 詳解

一.  官方說明

Oracle 11gR2 文件：

LOB Storage

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45267

 

Oracle 10gR2 文件：

LOBs in Tables

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_tables.htm#sthref165

 

1.1 Creating Tables That Contain LOBs

When creating tables that contain LOBs, use the guidelines described in the followingsections:

 

1.1.1 Initializing Persistent LOBs to NULL or Empty

You can set apersistent LOB — ?that is, a LOB column in a table, or a LOB attribute in anobject type that you defined— to be NULL or empty:

（1）Settinga Persistent LOB to NULL: A LOB setto NULL has no locator. A NULL value is stored in the rowin the table, not a locator. This is the same process as for all other datatypes.

（2）Settinga Persistent LOB to Empty: By contrast, an empty LOBstored in a table is a LOB of zero length that has a locator. So, ifyou SELECT from an empty LOB column or attribute, then you get back alocator which you can use to populate the LOB with data using supported programmaticenvironments, such as OCI or PL/SQL(DBMS_LOB).

--NULL 與 Empty的區別是NULL 沒有locator指標，而Empty 有locator 指標。

 

1.1.2 Setting a Persistent LOB to NULL

You may want toset a persistent LOB value to NULL upon inserting the row in caseswhere you do not have the LOB data at the time of the INSERT or ifyou want to use a SELECT statement, such as the following, to determinewhether the LOB holds a NULL value:

SELECT COUNT (*) FROM print_media WHERE ad_graphic IS NOT NULL;

SELECT COUNT (*) FROM print_media WHERE ad_graphic IS NULL;

 

Note that you cannot call OCI or DBMS_LOB functions on a NULL LOB, so you mustthen use an SQL UPDATE statement to reset the LOB column to anon-NULL (or empty) value.

--如果想使用OCI 或者DBMS_LOB, LOB 列需要設定為非NULL 或者empty。

The point isthat you cannot make a function call from the supported programmaticenvironments on a LOB that is NULL. These functions only work with alocator, and if the LOB column is NULL, then there is no locator in therow.

 

1.1.3 Settinga Persistent LOB to Empty

You can initialize a persistent LOB to EMPTY rather that NULL. Doing so,enables you to obtain a locator for the LOB instance without populating the LOBwith data.

To set apersistent LOB to EMPTY, use the SQLfunction EMPTY_BLOB() or EMPTY_CLOB() inthe INSERT statement:

SQL>INSERTINTO a_table VALUES (EMPTY_BLOB());

 

As an alternative, you can use the RETURNING clause to obtain the LOBlocator in one operation rather than calling a subsequent SELECT statement:

 

 

 

 

1.1.4 Initializing LOBs

You can initialize the LOBs in print_media by using thefollowing INSERT statement:

SQL>INSERTINTO print_media VALUES (1001, EMPTY_CLOB(), EMPTY_CLOB(), NULL,EMPTY_BLOB(),EMPTY_BLOB(), NULL, NULL, NULL, NULL);

 

This sets thevalueof ad_sourcetext, ad_fltextn, ad_composite, and ad_photo toan empty value, and sets ad_graphic to NULL.

 

1.1.5 Initializing Persistent LOB Columns and Attributes to a Value

You caninitialize the LOB column or LOB attributes to a value that contains more than4G bytes of data, the limit before release 10.2.

--在Oracle 11g中初始化LOB 的內容可以超過4G，而在oracle10g裡最大是4G。

 

A LOB can be up to 8 terabytes or more insize depending on your block size.

A LOB can be up to 128 terabytes or more insize depending on your block size.

這個是初始化的最大值，LOB可存放的最大容量：

Oracle 9iR2 是4G。

Oracle 10g 最大8T。

Oracle 11g 最大是128T。

具體取決與blocksize 的大小。

 

1.1.6 Initializing BFILEs to NULL or a File Name

A BFILE canbe initialized to NULL or to a filename. To do so, you can usethe BFILENAME() function.

See Also:

"BFILENAMEand Initialization".

 

1.1.7 Restrictionon First Extent of a LOB Segment

The first extent of any segment requires at least 2 blocks (if FREELISTGROUPS was 0). That is, the initial extent size of the segment should beat least 2 blocks. LOBs segments are different because they need at least 3 blocks in the first extent. If you try tocreate a LOB segment in a permanent dictionary managed tablespace with initial= 2 blocks, then it still works because it is possible for segments in permanent dictionary-managed tablespaces to override the default storagesetting of the tablespaces.

 

But if uniformlocally managed tablespaces or dictionary managed tablespaces of the temporarytype, or locally managed temporary tablespaces have an extent size of 2 blocks,then LOB segments cannot be created in these tablespaces. This is because inthese tablespace types, extent sizes are fixed and the default storage settingof the tablespaces is not ignored.

 

1.2 Choosinga LOB Column Data Type

1.2.1 LOBs Compared to LONG and LONG RAW Types

Table11-1 lists the similarities and differences between LOBs, LONGs, andLONG RAW types.

Table 11-1 LOBs Vs. LONG RAW

LOB Data Type	LONG and LONG RAW Data Type
You can store multiple LOBs in a single row	You can store only one LONG or LONG RAW in each row.
LOBs can be attributes of a user-defined data type	This is not possible with either a LONG or LONG RAW
Only the LOB locator is stored in the table column; BLOB and CLOB data can be stored in separate tablespaces and BFILE data is stored as an external file. For inline LOBs, the database stores LOBs that are less than approximately 4000 bytes of data in the table column.	In the case of a LONG or LONG RAW the entire value is stored in the table column.
When you access a LOB column, you can choose to fetch the locator or the data.	When you access a LONG or LONG RAW, the entire value is returned.
A LOB can be up to 128 terabytes or more in size depending on your block size.	A LONG or LONG RAW instance is limited to 2 gigabytes in size.
There is greater flexibility in manipulating data in a random, piece-wise manner with LOBs. LOBs can be accessed at random offsets.	Less flexibility in manipulating data in a random, piece-wise manner with LONG or LONG RAW data.LONGs must be accessed from the beginning to the desired location.
You can replicate LOBs in both local and distributed environments.	Replication in both local and distributed environments is not possible with a LONG or LONGRAW (see

 

1.2.2 Storing Varying-Width Character Data in LOBs

Varying-width character data in CLOB and NCLOB data types is stored in aninternal format that is compatible with UCS2 Unicode character set format. Thisensures that there is no storage loss of character data in a varying-widthformat. Also note the following if you are using LOBs to store varying-widthcharacter data:

（1）You can create tables containing CLOB and NCLOB columns even if youuse a varying-width CHAR or NCHAR database character set.

（2）You can create a table containing a data type that has a CLOB attributeregardless of whether you use a varying-width CHAR database characterset.

 

1.2.3 Implicit Character Set Conversions with LOBs

            For CLOB and NCLOB instancesused in OCI (Oracle Call Interface), or any of the programmatic environmentsthat access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.

The DBMS_LOB.LOADCLOBFROMFILE API, performs an implicit conversion from binary data to character datawhen loading to a CLOB or NCLOB. With the exception of DBMS_LOB.LOADCLOBFROMFILE,LOB APIs do not perform implicit conversions from binary data to characterdata.

For example,when you use the DBMS_LOB.LOADFROMFILE API to populatea CLOB or NCLOB, you are populating the LOB with binary datafrom a BFILE. In this case, you must perform character set conversions onthe BFILE data before calling DBMS_LOB.LOADFROMFILE.

 

Note:

The databasecharacter set cannot be changed from a single-byte to a multibyte character setif there are populated user-defined CLOB columns in the database tables.The national character set cannot be changedbetween AL16UTF16 and UTF8 if there are populateduser-defined NCLOB columns in the database tables.

 

1.3 LOB Storage Parameters

1.3.1 Inlineand Out-of-Line LOB Storage

LOB columnsstore locators that reference the location of the actual LOB value. Dependingon the column properties you specify when you create the table, and dependingthe size of the LOB, actual LOB values are stored either in the table row(inline) or outside of the table row (out-of-line).

LOB 儲存分為2種： Inline 和 Out-Of-Line Storage。 Inline Storage 是儲存在表的空間裡，而out-of-line storage 是儲存在lobsegment裡的。

 

LOB values are stored out-of-line when any of the following situations apply:

（1）If youexplicitly specify DISABLE STORAGE IN ROW forthe LOB storage clause when you create the table.

（2）If the sizeof the LOB is greater than approximately 4000 bytes(4000 minus system control information), regardless of the LOB storageproperties for the column.

（3）If you updatea LOB that is stored out-of-line and the resulting LOB is less thanapproximately 4000 bytes, it is still stored out-of-line.

 

LOB values are stored inline when any of the following conditions apply:

（1）When the sizeof the LOB stored in the given row is small, approximately 4000 bytes or less,and you either explicitly specify ENABLE STORAGE IN ROW orthe LOB storage clause when you create the table, or when you do not specifythis parameter (which is the default).

（2）When the LOB value is NULL (regardless of the LOB storageproperties for the column).

 

Using the default LOB storage properties (inline storage) canallow for better database performance; it avoids theoverhead of creating and managing out-of-line storage for smaller LOB values.If LOB values stored in your database are frequently small in size, then usinginline storage is recommended.

 

Note:

（1）LOB locatorsare always stored in the row.

（2）A LOB locatoralways exists for any LOB instance regardless of the LOB storage properties orLOB value - NULL, empty, or otherwise.

（3）If the LOB iscreated with DISABLE STORAGE IN ROW properties and the Basic FilesLOB holds any data, then a minimum of one CHUNK of out-of-line storagespace is used; even when the size of the LOB is less thanthe CHUNK size.

（4）If a LOBcolumn is initialized with EMPTY_CLOB() or EMPTY_BLOB(), then noLOB value exists, not even NULL. The row holds a LOB locator only. Noadditional LOB storage is used.

（5）LOB storageproperties do not affect BFILE columns. BFILE data is always stored in operating system files outside the database.

 

1.3.2 Defining Tablespaceand Storage Characteristics for Persistent LOBs

When defining LOBs in a table, you can explicitly indicate the tablespace and storagecharacteristics for each persistent LOB column.

 

To create aBasicFiles LOB, the BASICFILE keyword is optional but is recommendedfor clarity, as shown in the following example:

 

For SecureFiless, the SECUREFILE keyword is necessary, as shown in thefollowing example (assuming TABLESPACE lobtbs1 is ASSM):

 Note:

There are notablespace or storage characteristics that you can specifyfor external LOBs (BFILEs) as they are not stored in the database.

If you must modify the LOB storage parameters on an existing LOB column, then usethe ALTER TABLE ... MOVE statement. You can changethe RETENTION, PCTVERSION,CACHE, NOCACHE LOGGING, NOLOGGING,or STORAGE settings. You can also changethe TABLESPACE using the ALTER TABLE ... MOVE statement.

 

1.3.3 Assigninga LOB Data Segment Name

As shown in the previous example, specifying a name for the LOB data segment makes for a muchmore intuitive working environment. When querying the LOB data dictionary views USER_LOBS, ALL_LOBS, DBA_LOBS (see ), you see the LOB data segment that you chose instead ofsystem-generated names.

 

1.3.4 LOB Storage Characteristics for LOB Column or Attribute

LOB storage characteristics that can be specified for a LOB column or a LOB attributeinclude the following:

（1）TABLESPACE

（2）PCTVERSION or RETENTION

（3）CACHE/NOCACHE/CACHE READS

（4）LOGGING/NOLOGGING

（5）CHUNK

（6）ENABLE/DISABLE STORAGE IN ROW

（7）STORAGE

 

Note that you can specify either PCTVERSION or RETENTION for BasicFilesLOBs, but not both. For SecureFiless, only the RETENTION parametercan be specified.

 

For most users, defaults for these storagecharacteristics are sufficient. If you want to fine-tune LOB storage, thenconsider the following guidelines.

 

1.3.4.1 TABLESPACE and LOB Index

Best performancefor LOBs can be achieved by specifying storage for LOBs in a tablespacedifferent from the one used for the table that contains the LOB. If manydifferent LOBs are accessed frequently, then it may also be useful to specify aseparate tablespace for each LOB column or attribute in order to reduce devicecontention.

The LOB index isan internal structure that is strongly associated with LOB storage. This implies that a user may not drop the LOB index and rebuild it.

--LOB index 是隨Lobsegment 自動建立的，不能只刪除和重建LOB index。

 

Note:

The LOB index cannot be altered. –LOB index 不能被修改。

 

The system determines which tablespace to use for LOB data and LOB index depending on yourspecification in the LOB storage clause:

（1）If youdo not specify a tablespace for the LOB data, then the tablespace ofthe table is used for the LOB data and index.

（2）If youspecify a tablespace for the LOB data, then both the LOB data and index use thetablespace that was specified.

      --lobsegment 和lobindex 使用相同的表空間

 

1.3.4.2 Tablespace for LOB Index in Non-Partitioned Table

When creating atable, if you specify a tablespace for the LOB index for a non-partitionedtable, then your specification of the tablespace is ignored and the LOB indexis co-located with the LOB data. Partitioned LOBs do not include the LOB indexsyntax.

Specifying aseparate tablespace for the LOB storage segments enables a decrease incontention on the tablespace of the table.

 

1.3.4.3 PCTVERSION

When a BasicFiles LOB is modified, a new version of the BasicFiles LOB page isproduced in order to support consistent read of prior versions of theBasicFiles LOB value.

PCTVERSION is the percentage of all used BasicFiles LOB data space that can be occupied byold versions of BasicFiles LOB data pages. As soon as old versions ofBasicFiles LOB data pages start to occupy more thanthe PCTVERSION amount of used BasicFiles LOB space, Oracle Databasetries to reclaim the old versions and reuse them. In other words, PCTVERSION isthe percent of used BasicFiles LOB data blocks that is available for versioningold BasicFiles LOB data.

Oracle 的一致性透過UNDO 來體現，而LOB 的一致性是例外，是透過自己來實現的，就是在修改之前先copy 一下對應的chunk，chunk 是LOB的基本單位。 這個PCTVERSION就是一個用來控制回滾空間大小的一個引數，該值越大，回滾的時間相對就長，但是佔用的空間也就越大。

 

PCTVERSION has a default of 10 (%), a minimum of 0, and amaximum of 100.

To decide whatvalue PCTVERSION should be set to, consider the following:

（1）How oftenBasicFiles LOBs are updated?

（2）How often theupdated BasicFiles LOBs are read?

 

Table 11-2 Recommended PCTVERSION Settings

BasicFiles LOB Update Pattern	BasicFiles LOB Read Pattern	PCTVERSION
Updates X% of LOB data	Reads updated LOBs	X%
Updates X% of LOB data	Reads LOBs but not the updated LOBs	0%
Updates X% of LOB data	Reads both updated and non-updated LOBs	2X%
Never updates LOB	Reads LOBs	0%

 

If yourapplication requires several BasicFiles LOB updates concurrent with heavy readsof BasicFiles LOB columns, then consider using a higher valuefor PCTVERSION, such as 20%.

 

Setting PCTVERSION totwice the default value allows more free pages to be used for old versions ofdata pages. Because large queries may require consistent reads of BasicFilesLOB columns, it may be useful to retain old versions of BasicFiles LOB pages.In this case, BasicFiles LOB storage may grow because the database does notreuse free pages aggressively.

If persistentBasicFiles LOB instances in your application are created and written just onceand are primarily read-only afterward, then updates are infrequent. In thiscase, consider using a lower value for PCTVERSION, such as 5% or lower.

The moreinfrequent and smaller the BasicFiles LOB updates are, the less space must bereserved for old copies of BasicFiles LOB data. If existing BasicFiles LOBs areknown to be read-only, then you could safely set PCTVERSION to 0%because there would never be any pages needed for old versions of data.

 

當pctversion=0的時候，表示舊版本資料是可以被其他事務產生的版本佔用。如果設定為100，就表示舊版本資料永遠都不會被覆寫使用。

 

 

 

1.3.4.4 RETENTION Parameter for BasicFiles LOBs

As an alternative to the PCTVERSION parameter, you can specifythe RETENTION parameter in the LOB storage clause of the CREATETABLE or ALTER TABLE statement. Doing so, configures the LOBcolumn to store old versions of LOB data for a period of time, rather thanusing a percentage of the table space. For example:

 

 

 

	The RETENTION parameter is designed for use with UNDO features of the database, such asFlashback Versions Query. When a LOB column has the RETENTION property set, old versions of the LOB data are retainedfor the amount of time specified by the UNDO_RETENTION parameter.

 

Note the following withrespect to the RETENTION parameter:

（1）UNDO SQLis not enabled for LOB columns as it is with other data types. You must setthe RETENTION property on a LOB column to use Undo SQL on LOB data.

（2）You cannotset the value of the RETENTION parameter explicitly. The amount oftime for retention of LOB versions in determined bythe UNDO_RETENTION parameter.

--RETENTION 引數不能顯示的設定，只能透過UNDO_RETENTION 引數來繼承。

（3）Usage ofthe RETENTION parameter is only supported in Automatic UndoManagement mode. You must configure your table for use with Automatic UndoManagement before you can set RETENTION on a LOB column. ASSM is required forLOB RETENTION to be in effect for BasicFiles LOBs.The RETENTION parameter of the SQL (in theSTORE AS clause)is silently ignored if the BasicFiles LOB resides in an MSSM tablespace.

（4）The LOBstorage clause can specify RETENTION or PCTVERSION, but notboth.

--RETENTION 和 PCTVERSION 只能設定一個。

 

1.3.4.5 RETENTION Parameter for SecureFiless

With 11.1 and above it is recommended to use Securefile instead of basicfiles for the LOBs. The securefiles were developed so that it will anticipate the need to move the HW mark specially with the create, insert, update of the LOBs.

Specifying the RETENTION parameter for SecureFiless indicates that the databasemanages consistent read data for the SecureFiles storage dynamically, takinginto account factors such as the UNDO mode of the database.

（1）Specify MAX if the database is in FLASHBACK mode to limit the size of theLOB UNDO retention in bytes. If you specify MAX, then you mustalso specify the MAXSIZE clause in the storage_clause.

（2）Specify MIN if the database is in FLASHBACK mode to limitthe UNDO retention duration for the specific LOB segmentto n seconds.

（3）Specify AUTO ifyou want to retain UNDO sufficient for consistent read purposes only.This is the default.

（4）Specify NONE ifno UNDO is required for either consistent read or flashback purposes.

 

The default RETENTION for SecureFilessis AUTO.

 

1.3.4.6  CACHE / NOCACHE / CACHE READS

Table 11-3 When to Use CACHE, NOCACHE, andCACHE READS

Cache Mode	Read	Write
CACHE READS	Frequently	Once or occasionally
CACHE	Frequently	Frequently
NOCACHE (default)	Once or occasionally	Never

 

1.3.4.7 CACHE / NOCACHE / CACHE READS: LOB Valuesand Buffer Cache

（1）CACHE: Oracleplaces LOB pages in the buffer cache for faster access.

（2）NOCACHE: As aparameter in the STORE AS clause, NOCACHE specifies thatLOB values are not brought into the buffer cache.

（3）CACHE READS:LOB values are brought into the buffer cache only during read and not duringwrite operations.

NOCACHE is thedefault for both SecureFiless and BasicFiles LOBs.

 

Note:

Using the CACHE option results in improved performance when reading andwriting data from the LOB column. However, it can potentially age other non-LOBpages out of the buffer cache prematurely.

 

1.3.4.8 LOGGING / NOLOGGING Parameter for BasicFiles LOBs

[NO]LOGGING hasa similar application with regard to using LOBs as it does for other tableoperations. In the usual case, if the [NO]LOGGING clause is omitted, thenthis means that neither NOLOGGING nor LOGGING is specifiedand the logging attribute of the table or table partition defaults to thelogging attribute of the tablespace in which it resides.

 

For LOBs, there is afurther alternative depending on how CACHE is stipulated.

（1）CACHE isspecified and [NO]LOGGING clause is omitted. LOGGING isautomatically implemented (because you cannot have CACHE NOLOGGING).

（2）CACHE isnot specified and [NO]LOGGING clause is omitted. The process defaultsin the same way as it does for tables and partitioned tables. That is, the[NO]LOGGINGvalue is obtained from the tablespace in which the LOB segmentresides.

            當Lob資料設定為cache的時候，自動就是logging屬性。如果設定為nologging，只是lobsegment部分的資料變化不會寫redo log，不會影響到其他的in row和column列的資料redo記錄工作。

 

The following issues should also be kept inmind.

（1）LOBs Always Generate Undo for LOB Index Pages

Regardless ofwhether LOGGING or NOLOGGING is set, LOBs never generaterollback information (undo) for LOB data pages because old LOB data is storedin versions. Rollback information that is created for LOBs tends to be smallbecause it is only for the LOB index page changes.

（2）When LOGGING is Set Oracle Generates Full Redo for LOB Data Pages

NOLOGGING isintended to be used when a customer does not care about media recovery. Thus,if the disk/tape/storage media fails, then you cannot recover your changes fromthe log because the changes were never logged.

（3）NOLOGGING is Useful for Bulk Loads or Inserts.

For instance,when loading data into the LOB, if you do not care about redo and can juststart the load over if it fails, set the LOB data segment storagecharacteristics toNOCACHE NOLOGGING. This provides good performance forthe initial load of data.

Once you havecompleted loading data, if necessary, use ALTER TABLE to modifythe LOB storage characteristics for the LOB data segment for normal LOBoperations, for example, to CACHE or NOCACHE LOGGING.

 

Note:

CACHE implies that you also get LOGGING.

 

1.3.4.9 LOGGING/FILESYSTEM_LIKE_LOGGING forSecureFiless

NOLOGGING or LOGGING hasa similar application with regard to using SecureFilessas LOGGING/NOLOGGING does for other table operations. In the usualcase, if the logging_clause is omitted, then the SecureFiles inherits itslogging attribute from the tablespace in which it resides. In this case,if NOLOGGING is the default value, the SecureFiles defaultsto FILESYSTEM_LIKE_LOGGING.

 

Note:

Usingthe CACHE option results in improved performance when reading andwriting data from the LOB column. However, it can potentially age other non-LOBpages out of the buffer cache prematurely.

 

1.3.4.10 CACHE Implies LOGGING

For SecureFiless, there is a furtheralternative depending on how CACHE is specified:

（1）CACHE isspecified and the LOGGING clause is omitted,then LOGGING is used.

（2）CACHE isnot specified and the logging_clause is omitted. Then the process defaults inthe same way as it does for tables and partitioned tables. That is,the LOGGING value is obtained from the tablespace in which the LOBvalue resides. If the tablespace is NOLOGGING, then the SecureFilesdefaults to FILESYSTEM_LIKE_LOGGING.

 

The following issues should also be kept in mind.

（1）SecureFilessand an Efficient Method of Generating REDO and UNDO

This means thatOracle Database determines if it is more efficient togenerate REDO and UNDO for the change to a block, similarto heap blocks, or if it generates a version and full REDO of the newblock similar to BasicFiles LOBs.

 

（2）FILESYSTEM_LIKE_LOGGINGis Useful for Bulk Loads or Inserts

For instance,when loading data into the LOB, if you do not care about REDO and canjust start the load over if it fails, set the LOB data segment storagecharacteristics to FILESYSTEM_LIKE_LOGGING. This provides good performance forthe initial load of data.

Once you havecompleted loading data, if necessary, use ALTER TABLE to modifythe LOB storage characteristics for the LOB data segment for normal LOBoperations. For example, to CACHE or NOCACHE LOGGING.

 

1.3.4.11 CHUNK

A chunk is one or more Oracle blocks. You can specify the chunk size for the BasicFiles LOBwhen creating the table that contains the LOB. This corresponds to the datasize used by Oracle Database when accessing or modifying the LOB value. Part ofthe chunk is used to store system-related information and the rest stores theLOB value. The API you are using has a function that returns the amount ofspace used in the LOB chunk to store the LOB value. In PL/SQLuse DBMS_LOB.GETCHUNKSIZE. In OCI, useOCILobGetChunkSize().

 

Note:

If the tablespace block size is the same as the database block size,then CHUNK is also a multiple of the database block size. The default CHUNK size is equal to the size of onetablespace block, and the maximum value is 32K.

--chunk 預設和tablespace 的block 相等，並且最大值為32k。

 

（1）Choosing the Value of CHUNK

Once the valueof CHUNK is chosen (when the LOB column is created), it cannot bechanged. Hence, it is important that you choose a value which optimizes yourstorage and performance requirements. For SecureFiless CHUNK is anadvisory size and is provided for backward compatibility purposes.

 

Space Considerations

The valueof CHUNK does not matter for LOBs that are stored inline. Thishappens when ENABLE STORAGE IN ROW is set, and thesize of the LOB locator and the LOB data is less than approximately 4000 bytes.However, when the LOB data is stored out-of-line, it always takes up space inmultiples of the CHUNK parameter. This can lead to a large waste ofspace if your data is small, but the CHUNK is set to a large number.

 

Table 11-4 Data Size and CHUNK Size

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+

 

PerformanceConsiderations

Accessing lobs in big chunks is more efficient. You can set CHUNK to the data sizemost frequently accessed or written. For example, if only one block of LOB datais accessed at a time, then set CHUNK to the size of one block. Ifyou have big LOBs, and read or write big amounts of data, then choose a largevalue for CHUNK.

 

（2）Set INITIAL and NEXT to Larger than CHUNK

If you explicitly specify storage characteristics for the LOB, then make surethat INITIAL and NEXT for the LOB data segment storage areset to a size that is larger than the CHUNK size. For example, if the databaseblock size is 2KB and you specify a CHUNK of 8KB, then make surethat INITIAL and NEXT are bigger than 8KB and preferablyconsiderably bigger (for example, at least 16KB).

 

Put another way: If you specify a valuefor INITIAL, NEXT, or the LOB CHUNK size, then make surethey are set in the following manner:

CHUNK <= NEXT

CHUNK <= INITIAL

 

1.3.4.12 ENABLE or DISABLE STORAGE IN ROW Clause

You use the ENABLE | DISABLE STORAGE IN ROW clauseto indicate whether the LOB should be stored inline (in the row) or out-of-line.

 

Note:

You may notalter this specification once you have made it: if you ENABLE STORAGE INROW, then you cannot alter it to DISABLE STORAGE IN ROW and viceversa.

The defaultis ENABLE STORAGE IN ROW.

 

Guidelines for ENABLE or DISABLE STORAGE IN ROW

The maximumamount of LOB data stored in the row is the maximum VARCHAR2 size(4000). This includes the control information and the LOB value. If youindicate that the LOB should be stored in the row, once the LOB value andcontrol information is larger than approximately 4000, then the LOB value isautomatically moved out of the row.

 

This suggests thefollowing guidelines:

The default,ENABLE STORAGE IN ROW, is usually the best choice for the following reasons:

（1）Small LOBs:If the LOB is small (less than approximately 4000 bytes), then the whole LOBcan be read while reading the row without extra disk I/O.

（2）Large LOBs:If the LOB is big (greater than approximately 4000 bytes), then the controlinformation is still stored in the row if ENABLE STORAGE IN ROW is set, evenafter moving the LOB data out of the row. This control information could enableus to read the out-of-line LOB data faster.

 

However, in somecases DISABLE STORAGE IN ROW is a better choice. This is because storing theLOB in the row increases the size of the row. This impacts performance if youare doing a lot of base table processing, such as full table scans, multi-rowaccesses (range scans), or many UPDATE/SELECT to columns other than the LOBcolumns.

 

1.4 Indexing LOB Columns

This section discusses different techniquesyou can use to index LOB columns.

Note:

After you move a LOB column any existing table indexes mustbe rebuilt.

 

1.4.1 Using Domain Indexing on LOB Columns

You might beable to improve the performance of queries by building indexes specificallyattuned to your domain. Extensibility interfaces provided with the databaseallow for domain indexing, a framework for implementing such domain specificindexes.

 

Note:

You cannot builda B-tree or bitmap index on a LOB column.

 

1.4.2 Indexing LOB Columns Using a Text Index

Depending on the nature of the contents of the LOB column, one of the Oracle Text options couldalso be used for building indexes. For example, if a text document is stored in a CLOB column, then you can build a text index to speed up the performanceof text-based queries over the CLOB column.

 

1.4.3 Function-Based Indexes on LOBs

A function-based index is an index builton an expression. It extends your indexing capabilities beyond indexing on acolumn. A function-based index increases the variety of ways in which you canaccess data.

Function-basedindexes cannot be built on nested tables or LOB columns. However, you can buildfunction-based indexes on VARRAYs.

Like extensible indexes and domain indexes on LOB columns,function-based indexes are also automatically updated when a DML operation isperformed on the LOB column. Function-based indexes are also updated when anyextensible index is updated.

 

1.4.4 ExtensibleIndexing on LOB Columns

The database provides extensible indexing, a feature which enables you to define new index types as required.This is based on the concept of cooperative indexing where a data cartridge and the database build and maintain indexes for data types such as text and spatialfor example, for On-line-Analytical Processing (OLAP).

The cartridge isresponsible for defining the index structure, maintaining the index contentduring load and update operations, and searching the index during queryprocessing. The index structure can be stored in Oracle as heap-organized, oran index-organized table, or externally as an operating system file.

To support this structure, the database providesan indextype. The purpose of an indextype is toenable efficient search and retrieval functions for complex domains such astext, spatial, image, and OLAP by means of a data cartridge. An indextype isanalogous to the sorted or bit-mapped index types that are built-in within theOracle Server. The difference is that an indextype is implemented by the datacartridge developer, whereas the Oracle kernel implements built-in indexes.Once a new indextype has been implemented by a data cartridge developer, endusers of the data cartridge can use it just as they would built-in indextypes.

 

When thedatabase system handles the physical storage of domain indexes, data cartridges

（1）Define theformat and content of an index. This enables cartridges to define an indexstructure that can accommodate a complex data object.

（2）Build,delete, and update a domain index. The cartridge handles building andmaintaining the index structures. Note that this is a significant departurefrom the medicine indexing features provided for simple SQL data types. Also,because an index is modeled as a collection of tuples, in-place updating isdirectly supported.

（3）Access andinterpret the content of an index. This capability enables the data cartridgeto become an integral component of query processing. That is, thecontent-related clauses for database queries are handled by the data cartridge.

 

By supportingextensible indexes, the database significantly reduces the effort needed todevelop high-performance solutions that access complex data types such as LOBs.

 

1.4.5 Extensible Optimizer

The extensible optimizer functionality allows authors of user-defined functions and indexes tocreate statistics collections, selectivity, and cost functions. Thisinformation is used by the optimizer in choosing a query plan. The cost-basedoptimizer is thus extended to use the user-supplied information.

Extensible indexing functionality enables you to define new operators, index types, anddomain indexes. For such user-defined operators and domain indexes, theextensible optimizer functionality allows users to control the three maincomponents used by the optimizer to select an execution plan: statistics,selectivity, and cost.

 

1.4.6 OracleText Indexing Support for XML

You can createOracle Text indexes on CLOB columns and perform queries on XML data.

 

1.5 Manipulating LOBs in Partitioned Tables

You canpartition tables that contain LOB columns. As aresult, LOBs can take advantage of all of the benefits of partitioningincluding the following:

（1）LOB segmentscan be spread between several tablespaces to balance I/O load and to makebackup and recovery more manageable.

（2）LOBs in apartitioned table become easier to maintain.

（3）LOBs can bepartitioned into logical groups to speed up operations on LOBs that areaccessed as a group.

 

This section describes some of the ways you can manipulate LOBs in partitioned tables.

 

1.5.1 Partitioninga Table Containing LOB Columns

LOBs aresupported in RANGE partitioned, LIST partitioned, and HASH partitioned tables.Composite heap-organized tables can also have LOBs.

 

You can partition a tablecontaining LOB columns using the following techniques:

（1）When thetable is created using the PARTITION BY ... clause of the CREATETABLE statement.

（2）Adding apartition to an existing table using the ALTER TABLE ... ADDPARTITION clause.

（3）Exchangingpartitions with a table that has partitioned LOB columns using the ALTERTABLE ... EXCHANGE PARTITION clause. Note that EXCHANGEPARTITION can only be used when both tables have the same storageattributes, for example, both tables store LOBs out-of-line.

 

Creating LOBpartitions at the same time you create the table (in the CREATETABLE statement) is recommended. If you create partitions on a LOB columnwhen the table is created, then the column can hold LOBs stored either inlineor out-of-line LOBs.

After a table iscreated, new LOB partitions can only be created on LOB columns that are storedout-of-line. Also, partition maintenance operations, SPLITPARTITION and MERGE PARTITIONS, only work on LOB columns that storeLOBs out-of-line.

 

1.5.2 Creatingan Index on a Table Containing Partitioned LOB Columns

To improve the performance of queries, you can create indexes onpartitioned LOB columns. For example:

 

CREATE INDEX index_name

   ONtable_name (LOB_column_1, LOB_column_2, ...) LOCAL;

 

Note that onlydomain and function-based indexes are supported on LOB columns. Other types ofindexes, such as unique indexes are not supported with LOBs.

 

1.5.3 Moving Partitions Containing LOBs

You can move a LOB partition into a different tablespace. This isuseful if the tablespace is no longer large enough to hold the partition. To doso, use the ALTER TABLE ... MOVE PARTITION clause. For example:

 

1.5.4 SplittingPartitions Containing LOBs

You can split a partition containing LOBs into two equally sizedpartitions using the ALTER TABLE ... SPLIT PARTITION clause. Doing sopermits you to place one or both new partitions in a new tablespace. Forexample:

 

1.5.5 Merging Partitions Containing LOBs

You can mergepartitions that contain LOB columns using the ALTER TABLE ... MERGEPARTITIONS clause. This technique is useful for reclaiming unusedpartition space. For example:

 

1.6 LOBs in Index Organized Tables

Index OrganizedTables (IOTs) support internal and external LOB columns. For the most part, SQLDDL, DML, and piece wise operations on LOBs in IOTs produce the same results asthose for normal tables. The only exception is the default semantics of LOBsduring creation. The main differences are:

（1）TablespaceMapping: By default, or unless specified otherwise, theLOB data and index segments are created in the tablespace in which the primarykey index segments of the index organized table are created.

（2）Inlineas Compared to Out-of-Line Storage: By default, allLOBs in an index organized table created without an overflow segment are storedout of line. In other words, if an index organized table is created without anoverflow segment, then the LOBs in this table have their default storageattributes as DISABLE STORAGE IN ROW. If you forcibly tryto specify an ENABLE STORAGE IN ROW clause for suchLOBs, then SQL raises an error.

 

On the otherhand, if an overflow segment has been specified, then LOBs in index organizedtables exactly mimic their semantics in conventional tables (see "DefiningTablespace and Storage Characteristics for Persistent LOBs").

 

Exampleof Index Organized Table (IOT) with LOB Columns

 

Executing thesestatements results in the creation of an index organizedtable iotlob_tab with the following elements:

（1）A primary key index segment in thetablespace iot_ts,

（2）An overflow data segment intablespace ioto_ts

（3）Columns starting fromcolumn C3 being explicitly stored in the overflow data segment

（4）BLOB (column C2) datasegments in the tablespace lob_ts

（5）BLOB (column C2) indexsegments in the tablespace lobidx_ts

（6）CLOB (column C3) datasegments in the tablespace iot_ts

（7）CLOB (column C3) indexsegments in the tablespace iot_ts

（8）CLOB (column C3) storedin line by virtue of the IOT having an overflow segment

（9）BLOB (column C2)explicitly forced to be stored out of line

 

Note:

If no overflowhad been specified, then both C2 and C3 would have been stored out of line bydefault.

Other LOBfeatures, such as BFILEs and varying character width LOBs, are alsosupported in index organized tables, and their usage is the same as forconventional tables.

 

1.7 Restrictions for LOBs in Partitioned Index-OrganizedTables

LOB columns aresupported in range-, list-, and hash-partitioned index-organized tables withthe following restrictions:

（1）Composite partitionedindex-organized tables are not supported.

（2）Relational and object partitionedindex-organized tables (partitioned by range, hash, or list) can hold LOBsstored as follows; however, partition maintenance operations, suchas MOVE, SPLIT, and MERGE are not supported with:

1）VARRAY datatypes stored as LOB data types

2）Abstract datatypes with LOB attributes

3）Nested tableswith LOB types

 

1.8 Updating LOBs in Nested Tables

To update LOBsin a nested table, you must lock the row containing the LOB explicitly. To doso, you must specify the FOR UPDATE clause in the subquery prior to updatingthe LOB value.

Note thatlocking the row of a parent table does not lock the row of a nested tablecontaining LOB columns.

 

Note:

Nested tablescontaining LOB columns are the only data structures supported for creatingcollections of LOBs. You cannot create a VARRAY of any LOB data type.

 

 

二. LOB 說明

2.1 LOB 分類

LOB大物件主要是用來儲存大量資料的資料庫欄位，在Oracle 9iR2 中LOB的最大容量是4G，Oracle 10g 最大8T，Oracle 11g 最大是128T。具體取決與blocksize 的大小。

 

Oracle 中支援4 種型別的LOB：

CLOB：字元LOB。這種型別用於儲存大量的文字資訊，如XML 或者只是純文字。這個資料型別需要進行字符集轉換，也就是說，在獲取時，這個欄位中的字元會從資料庫的字符集轉換為客戶的字符集，而在修改時會從客戶的字符集轉換為資料庫的字符集。

NCLOB：這是另一種型別的字元LOB。儲存在這一列中的資料所採用的字符集是資料庫的國家字符集，而不是資料庫的預設字符集。

BLOB：二進位制LOB。這種型別用於儲存大量的二進位制資訊，如字處理文件，影像和你能想像到的任何其他資料。它不會執行字符集轉換。應用向BLOB 中寫入什麼位和位元組，BLOB就會返回什麼為和位元組。

BFILE：二進位制檔案LOB。這與其說是一個資料庫儲存實體，不如說是一個指標。帶BFILE列的資料庫中儲存的只是作業系統中某個檔案的一個指標。這個檔案在資料庫之外維護，根本不是資料庫的一部分。BFILE 提供了檔案內容的只讀訪問。

 

LOB資料型別分類：

1.     按儲存資料的型別分：
  （1）字元型別：
       CLOB:儲存大量 單位元組 字元資料。
       NLOB:儲存定寬 多位元組 字元資料。
  （2）二進位制型別：
        BLOB:儲存較大無結構的二進位制資料。
  （3）二進位制檔案型別：
        BFILE:將二進位制檔案儲存在資料庫外部的作業系統檔案中。存放檔案路徑。

2.     按儲存方式分：
   （1）儲存在內部表空間（內部LOB）：
       CLOB，NLOB和BLOB
   （2）指向外部作業系統檔案（外部LOB）：
        BFILE

 

有關LOB 型別的處理，參考之前整理的Blog：

ORACLE LOB 大物件處理

http://blog.csdn.net/tianlesoftware/article/details/5070981

 

2.2 內部LOB

先看示例：

SQL>create user anqing identified by anqing;

SQL>grant connect,resource,dba to anqing;

 

SYS@anqing1(rac1)> conn anqing/anqing;

Connected.

ANQING@anqing1(rac1)> create table tl(idnumber primary key,txt clob);

Table created.

 

TL 表的建立指令碼如下：

 

 

 

SYS_C007307索引用來支援主鍵約束，lobindex 和lobsegment是為了支援我們的LOB 列。我們的實際LOB 資料就儲存在lobsegment 中（ 確實，LOB 資料也有可能儲存在表T 中，不過稍後討論ENABLE STORAGE IN ROW 子句時還會更詳細地說明這個內容）。lobindex 用於執行LOB 的導航，來找出其中的某些部分。建立一個LOB 列時，一般來說，儲存在行中的這是一個指標（pointer），或LOB 定位器（LOB locator）。我們的應用所獲取的就是這個LOB 定位器。

 

當請求得到LOB 的“12.000～2,000 位元組”時，將對lobindex 使用LOB 定位器來找出這些位元組儲存在哪裡，然後再訪問lobsegment。可以用lobindex 很容易地找到LOB 的各個部分。由此說來，可以把LOB想成是一種主/明細關係。

 

LOB 按“塊”（chunk）或（piece）來儲存，每個片段都可以訪問。例如，如果我們使用表來實現一個LOB，可以如下做到這一點：

 

 

 

從概念上講，LOB 的儲存與之非常相似，建立這兩個表時，在LOB 表的ID.CHUNK_NUMBER上要有一個主鍵（這對應於Oracle建立的lobindex），而且要有一個LOB 表來儲存資料塊（對應於lobsegment）。LOB列為我們透明地實現了這種主/明細結構。

 

為了得到LOB 中的N~M位元組，要對錶中的指標（LOB 定位器）解除引用，遍歷lobindex 結構來找到所需的資料庫（chunk）， 然後按順序訪問。這使得隨機訪問LOB 的任何部分都能同樣迅速，你可以用同樣快的速度得到LOB 的最前面、中間或最後面的部分，因為無需再從頭開始遍歷LOB。

 

2.2.1.  LOB 表空間

CREATE TABLE 語句包括以下內容：

LOB(TXT)STOREAS(TABLESPACEUSERS…

這裡指定的TABLESPACE 是將儲存lobsegment 和lobindex 表空間，這可能與表本身所在的表空間不同。也就是說，儲存LOB 資料的表空間可能不同於儲存實際表資料的表空間。

為什麼考慮為LOB 資料使用另外一個表空間（而不用表資料所在的表空間）呢？注意原因與管理和效能有關。從管理的角度看，LOB 資料型別表示一種規模很大的資訊。如果表有數百萬行，而每行有一個很大的LOB，那麼LOB 就會極為龐大。為LOB 資料單獨使用一個表空間有利於備份和恢復以及空間管理，單從這一點考慮，將表與LOB 資料分離就很有意義。例如，你可能希望LOB 資料使用另外一個統一的區段大小，而不是普通表資料所用的區段大小。

另一個原因則出於I/O 效能的考慮。預設情況下，LOB 不在緩衝區快取中進行快取（有關內容將在後面再做說明）。因此，預設情況下，對於每個LOB 訪問，不論是讀還是寫，都會帶來一個物理I/O（從磁碟直接讀，或者向磁碟直接寫）。

注意: LOB 可能是內聯的（inline），或者儲存在表中。在這種情況下，LOB 資料會被快取，但是這隻適用於小於4,000 位元組的LOB。我們將在“IN ROW 子句”一節中進一步討論這種情況。

由於每個訪問都是一個物理I/O，所以如果你很清楚在實際中（當使用者訪問時）有些物件會比大多數其他物件經歷更多的物理I/O，那麼將這些物件分離到它們自己的磁碟上就很有意義。

 

需要說明，lobindex 和lobsegment 總是會在同一個表空間中。不能將lobindex 和lobsegment 放在不同的表空間中。在Oralce 的更早版本中，允許為lobindex 和lobsegment 分別放在單獨的表空間中，但是從8i Release 3 以後，就不再允許為lobindex 和logsegment 指定不同的表空間。實際上，lobindex的所有儲存特徵都是從lobsegment 繼承的。

 

2.2.2. IN ROW 子句

CREATE TABLE 語句還包括以下內容：

LOB(TXT)STOREAS(…ENABLE STORAGEINROW…

 

這控制了LOB 資料是否總與表分開儲存（儲存在lobsegment 中），或是有時可以與表一同儲存，而不用單獨放在lobsegment 中。如果設定了ENABLE STORAGE IN ROW，而不是DISABLE STORAGE IN ROW，小LOB（最多4,000 位元組）就會像VARCHAR2 一樣儲存在表本身中。只有當LOB 超過了4,000 位元組時，才會“移出”到lobsegment 中。

預設行為是啟用行記憶體儲（ENABLE STORAGEIN ROW），而且一般來講，如果你知道LOB 總是能在表本身中放下，就應該採用這種預設行為。例如，你的應用可能有一個某種型別的DESCRIPTION 欄位。這個DESCRIPTION 可以儲存0～32KB 的資料（或者可能更多，但大多數情況下都少於或等於32KB）。已知很多描述都很簡短，只有幾百個字元。如果把它們單獨儲存，並在每次獲取時都透過索引來訪問，就會存在很大的開銷，你完全可以將它們內聯儲存，即放在表本身中，這就能避免單獨儲存的開銷。不僅如此，如果LOB還能避免獲取LOB 時所需的物理I/O。

 

一般來說，OUT ROW，即將資料儲存在segment裡，在這種情況下不會在buffer cache 中進行快取，這樣每次都會產生物理IO. 同時對out row 進行讀寫操作時，雖然有lobindex的存在，但 DML 操作需要同時維護lobindex和lobsegment。 採用OUT ROW 會增加邏輯IO和物理IO，所以預設啟用IN ROW。 對lob 進行快取，減小IO成本。

 

2.2.3. CHUNK 子句

CREATE TABLE 語句包括以下內容：

LOB("TXT") STORE AS ( ... CHUNK 8192 ... )

 

LOB 儲存在塊（chunk）中，指向LOB 資料的索引會指向各個資料塊。塊（chunk）是邏輯上連續的一組資料庫塊（block），這也是LOB 的最小分配單元，而通常資料庫的最小分配單元是資料庫塊。CHUNK 大小必須是Oracle 塊大小的整數倍，只有這樣才是合法值。

 

從兩個角度看，選擇CHUNK 大小時必須當心。首先，每個LOB例項（每個行外儲存的LOB 值）會佔用至少一個CHUNK。一個CHUNK 有一個LOB 值使用。如果一個表有100 行，而每行有一個包含7KB 資料的LOB，你就會分配100 個CHUNK，如果將CHUNK 大小設定為32KB，就會分配100 個32KB 的CHUNK。如果將CHUNK大小設定為8KB，則（可能）分配100 個8KB 的CHUNK。關鍵是，一個CHUNK 只能有一個LOB 使用（兩個LOB 不會使用同一個CHUNK）。如果選擇了一個CHUNK 大小，但不符合你期望的LOB 大小，最後就會浪費大量的空間。例如，如果表中的LOB 平均有7KB，而你使用的CHUNK 大小為32KB，對於每個LOB 例項你都會“浪費”大約25KB 的空間，另一方面，倘若使用8KB 的CHUNK，就能使浪費減至最少。

 

還需要注意要讓每個LOB 例項相應的CHUNK 數減至最少。前面已經看到了，有一個lobindex 用於指向各個塊，塊越多，索引就越大。如果有一個4MB 的LOB，並使用8KB 的CHUNK，你就至少需要512 個CHUNK來儲存這個訊息。這也說明，至少需要512 個lobindex 條目指向這些CHUNK。聽上去好像沒什麼，但是你要記住，對於每個LOB 個數的512 倍。另外，這還會影響獲取效能，因為與讀取更少但更大的CHUNK 相比，現在要花更長的資料來讀取和管理許多小CHUNK。我們最終的目標是：使用一個能使“浪費”最少，同時又能高效儲存資料的CHUNK大小。

 

2.2.4. PCTVERSION 子句

DBMS_METADATA 返回的CREATE TABLE 語句包括以下內容：

LOB("TXT") STORE AS ( ... PCTVERSION 10. ... )

 

多版本一致讀、當前讀是Oracle資料庫具有的獨特屬性，也是其最重要的特性之一。藉助undo表空間的前映象資料儲存，Oracle Server Process可以訪問到一些特定時間點（SCN）的資料，作為一致性讀取、免於髒資料。

但對於Lob型別而言，一致讀問題同樣存在。Oracle需要一種保留Lob資料映象的機制，儲存一系列old version。目前，Oracle提供了兩種維持機制來進行控制：基於時間的版本保留retention和基於空間的版本保留pctversion。

 

 

 

LOB在lobsegment 中保留某個百分比的空間來實現LOB 的版本化，直接在lobsegment 本身中維護資訊的版本。lobindex 會像其他段一樣生成undo，但是lobsegment 不會。

相反，修改一個LOB 時，Oracle 會分配一個新的CHUNK，並且仍保留原來的CHUNK。如果回滾了事務，對LOB 索引所做的修改會回滾，索引將再次指向原來的CHUNK。因此，undo 維護會在LOB 段本身中執行。修改資料時，原來的資料庫保持不動，此外會建立新資料。

讀LOB 資料時這也很重要。LOB 是讀一致的，這與所有其他段一樣。如果你在上午9:00 獲取一個LOB定位器，你從中獲取的LOB 資料就是“上午9:00 那個時刻的資料”。這就像是你在上午9:00 開啟了一個遊標（一個結果集）一樣，所生成的行就是那個時間點的資料行。與結果集類似，即使別人後來修改了LOB資料。在此，Oracle 會使用lobsegment，並使用logindex 的讀一致檢視來撤銷對LOB 的修改，從而提取獲取LOB 定位器當時的LOB 資料。它不會使用logsegment 的undo 資訊，因為根本不會為logsegment 本身生成undo 資訊。

 

PCTVERSION 控制著用於實現LOB 資料版本化的已分配LOB 空間的百分比（這些資料庫塊由某個時間點的LOB 所用，並處在lobsegment 的HWM 以下）。對於許多使用情況來說，預設設定10%就足夠了，因為在很多情況下，你只是要INSERT 和獲取LOB（通常不會執行LOB 的更新；LOB 往往會插入一次，而獲取多次）。因此，不必為LOB 版本化預留太多的空間（甚至可以沒有）。

 

不過，如果你的應用確實經常修改LOB，倘若你頻繁地讀LOB，與此同時另外某個會話正在修改這些LOB，10%可能就太小了。如果處理LOB 時遇到一個ORA-22924錯誤，解決方案不是增加undo表空間的大小，也不是增加undo保留時間（UNDO_RETENTION），如果你在使用手動undo 管理，那麼增加更多RBS 空間也不能解決這個問題。而是應該使用以下命令：

ALTER TABLEtabname MODIFY LOB (lobname) ( PCTVERSION n );

並增加lobsegment 中為實現資料版本化所用的空間大小。

 

2.2.5. RETENTION 子句

Retention是表示採用基於時間版本保留策略。簡單的說，就是儘量保證保留一個時間段內的資料lob版本不會清除掉，即多長時間內來保證一致讀。在資料庫版本的相容性版本設定在9.2.0.0以上，並且undo_management引數值為true時，lob是預設直接使用retetion設定的。

需要注意，不能使用這個子句來指定保留時間；而要從資料庫的UNDO_RETENTION 設定來繼承它。

  這個子句與PCTVERSION 子句是互斥的，即RETENTION和 PCTVERSION 只能設定一個，不能兩個都設定。

 

2.2.6. CACHE 子句

前面的DBMS_METADATA返回的CREATETABLE 語句包括以下內容：

LOB("TXT") STORE AS (... NOCACHE ... )

 

除了NOCACHE，這個選項還可以是CACHE 或CACHE READS。這個子句控制了lobsegment 資料是否儲存在緩衝區快取中。預設的NOCACHE 指示，每個訪問都是從磁碟的一個直接讀，類似地，每個寫/修改都是對大盤的一個直接寫。CACHE READS 允許快取從磁碟讀的LOB 資料，但是LOB 資料的寫操作必須直接寫至磁碟。CACHE 則允許讀和寫時都能快取LOB 資料。

在許多情況下，預設設定可能對我們並不合適。如果你只有小規模或中等規模的L O B（例如，使用LOB來儲存只有幾KB 的描述性欄位），對其快取就很有意義。如果不快取，當使用者更新描述欄位時，還必須等待I/O 將資料寫指磁碟（將執行一個CHUNK大小的I/O，而且使用者要等待這個I/O 完成）。如果你在執行多個LOB 的載入，那麼載入每一行時都必須等待這個I/O 完成。所以啟用執行LOB 快取很合理。你可以開啟和關閉快取，來看看會有什麼影響：

ALTER TABLEtabname MODIFY LOB (lobname) ( CACHE );

ALTER TABLEtabname MODIFY LOB (lobname) ( NOCACHE );

 

對於一個規模很多的初始載入，啟用LOB 的快取很有意義，這允許DBWR在後臺將LOB 資料寫至磁碟，而你的客戶應用可以繼續載入更多的資料。對於頻繁訪問或修改的小到中等規模的LOB，快取就很合理，可以部門讓終端使用者實時等待物理I/O 完成。不過，對於一個大小為50MB的LOB，把它放在快取中就沒帶道理了。

要記住，此時可以充分使用Keep 池或回收池。並非在預設快取中將lobsegment 資料與所有“常規”資料一同快取，可以使用保持池或回收池將其分開快取。採用這種方式，既能快取LOB 資料，而且不影響系統中現有資料的快取。

 

2.2.7. LOB STORAGE 子句

DBMS_METADATA 返回的CREATE TABLE 語句還包括以下內容：

 

 

 

也就是說，它有一個完整的儲存子句，可以用來控制物理儲存特徵。需要指出，這個儲存子句同樣適用於lobsegment 和lobindex，對一個段的設定也可以用於另一個段。假設有一個本地管理的表空間，LOB的相關設定將是FREELISTS、FREELIST GROUPS 和BUFFER_POOL。

對LOB 段使用保持池或回收池可能是一個很有用的技術，這樣就能快取LOB 資料，而且不會“破壞”現有的預設緩衝區快取。並不是將LOB 與常規表一同放在塊緩衝區中，可以在SGA 中專門為這些LOB 物件預留一段專用的記憶體。BUFFER_POOL 子句可以達到這個目的。

 

2.3 BFILE

BFILE 型別只是作業系統上一個檔案的指標。它用於為這些作業系統檔案提供只讀訪問。

注意: 內建包UTL_FILE 也為作業系統檔案提供了讀寫訪問。不過它沒有使用BFILE 型別。

 

使用BFILE 時，還有使用一個Oracle DIRECTORY 物件。DIRECTORY 物件只是將一個作業系統目錄對映至資料庫中的一個“串”或一個名稱（以提供可移植性；你可能想使用BFILE 中的一個串，而不是作業系統特定的檔名約定）。

 

作為一個小例子，下面建立一個帶BFILE 列的表，並建立一個DIRECTORY物件，再插入一行，其中引用了檔案系統中的一個檔案：

 

 

 

現在，就可以把BFILE 當成一個LOB 來處理，因為它就是一個LOB。例如，我們可以做下面的工作：

 

 

可以看到所指定的檔案大小為1MB。注意，這裡故意在INSERT語句中使用了MY_DIR。如果使用混合大小寫或小寫，會得到以下錯誤：

 

 

 

這個例子只是說明：Oracle 中的DIRECTORY 物件是識別符號，而預設情況下識別符號都以大寫形式儲存。

BFILENAME 內建函式接受一個串，這個串的大小寫必須與資料字典中儲存的DIRECTORY物件的大小寫完全匹配。所以，我們必須在BFILENAME 函式中使用大寫，或者在建立DIRECTORY 物件時使用加引號的識別符號：

 

 

 

不建議使用加引號的識別符號；而傾向於在BFILENAME 呼叫中使用大寫。加引號的識別符號屬於“異類”，可能會在以後導致混淆。

BFILE 在磁碟上佔用的空間不定，這取決於DIRECTORY 物件名的檔名的長度。在前面的例子中，所得到的BFILE 長度大約為35 位元組。一般來說，BFILE 會佔用大約20 位元組的開銷，再加上DIRECTORY 物件的長度以及檔名本身的長度。

與其他LOB 資料不同，BFILE 資料不是“讀一致”的。由於BFILE 在資料庫之外管理，對BFILE 解除引用時，不論檔案上發生了什麼，都會反映到你得到的結果中。所以，如果反覆讀同一個BFILE，可能會產生不同的結果，這與對CLOB、BLOB 或NCLOB 使用LOB 定位器不同。

 

三. Move Table 與 LOB

在之前的Blog：

Oracle 高水位(HWM: High Water Mark) 說明

http://blog.csdn.net/tianlesoftware/article/details/4707900

 

提到解決高水位的一種方法就是Move Table，如果我們的表裡有LOB 欄位，那麼我們在Move 的時候就需要注意一下。

 

在建立含有lob欄位的表時，oracle會自動為lob欄位建立兩個單獨的segment,一個用來存放資料（segment_type=LOBSEGMENT），另一個用來存放索引（segment_type=LOBINDEX）。

我們對錶MOVE時，LOG型別欄位和該欄位的索引不會跟著MOVE，必須要單獨來進行MOVE，語法如下如：

       SQL>altertable owner.table_name move tablespace tablespace_name lob (lob_column) store as (tablespace tablespace_name);

 

也可以分2部來走：

SQL> altertable owner.table_name move tablespace tablespace_name;

SQL>altertable owner.table_name move lob(lob_column) store as (tablespacetablespace_name) ;

 

            Move操作會導致表上的索引失效，操作結束後我們需要對索引進行rebuild。這部分可以參考我的Blog：

             Oracle alter index rebuild 說明

http://blog.csdn.net/tianlesoftware/article/details/6538928

 

 

四. 含有LOB 欄位表的遷移示例

 

這裡演示2個不同使用者之間的一個遷移，使用expdp/impdp 來實現，有關data pump 參考：

Oracle expdp/impdp 使用示例

http://blog.csdn.net/tianlesoftware/article/details/6260138

 

1.     先建立directory：

 

 

 

2.     建立2個測試用的使用者，每個使用者有獨立的表空間。

 

 

3.     登陸anqing1 使用者，建立含有LOB的表：

 

 

 

這裡我們可以看到，我的表LOB1佔用空間44M，LOBSEGMENT 佔用27M。

 

4.     採用 REMAP_SCHEMA

4.1 expdp 匯出：

 

 

4.2 impdp 匯入：

 

 

4.3 驗證：

 

 

 

我們的資料已經匯入到了anqing2使用者下，但是該表的物理儲存還是存在anqing1的tablespace下面。

 

4.4 Move Table

            將LOB1從anqing1 表空間下面Move 到anqing2下面。

 

 

 

 

這裡的LOB1 表已經移到anqing2的表空間下了，但是LOBSEGMENT和LOBINDEX 還沒有移動。 繼續操作：

 

 

 

我這裡演示的是不同使用者之間的一個遷移，如果是相同使用者下的遷移，只需要在操作之前把相關的表空間和使用者建好就可以了。

 

 

 

注：第二小結內容出自：<Oracle 9i/10g 程式設計藝術>