Pro Oracle Database 11g Administration (46 page)

Caching LOBs

By default, when reading and writing LOB columns, Oracle doesn’t cache LOBs in memory. You can change the default behavior by setting the cache-related storage options. This example specifies that Oracle should cache a LOB column in memory:

create table patchmain(

patch_id number

,patch_desc clob)

lob(patch_desc) store as (tablespace lob_data cache);

You can verify the LOB caching with this query:

SQL> select table_name, column_name, cache from user_lobs;

Here’s some sample output:

TABLE_NAME COLUMN_NAME CACHE

-------------------- -------------------- ----------

PATCHMAIN PATCH_DESC YES

Table 11–3 describes the memory cache settings related to LOBs. If you have LOBs that are frequently read and written to, consider using the CACHE option. If your LOB column is read frequently but rarely written to, then the CACHE READS setting is more appropriate. If the LOB column is infrequently read or written to, then the NOCACHE setting is suitable.

Table 11–3.
Cache Descriptions Regarding LOB Columns

Cache Setting

Meaning

CACHE

Oracle should place LOB data in the buffer cache for faster access.

CACHE READS

Oracle should place LOB data in the buffer cache for reads but not for writes.

NOCACHE

LOB data shouldn’t be placed in the buffer cache. This is the default for both SecureFile and BasicFile LOBs.

254

CHAPTER 11 ■ LARGE OBJECTS

Storing LOBs In and Out of Line

By default, up to approximately 4000 characters of a LOB column are stored in line with the table row. If the LOB is over 4000 characters, then Oracle automatically stores the LOB outside of the row data. The main advantage of storing a LOB in row is that small LOBs (less than 4000 characters) require less I/O, because Oracle doesn’t have to search out of row for the LOB data.

However, storing LOB data in row isn’t always desirable. The disadvantage of storing LOBs in row is that the table row sizes are potentially longer. This can impact the performance of full-table scans, range scans, and updates to columns other than the LOB column. In these situations, you may want to disable storage in the row. For example, you explicitly instruct Oracle to store the LOB outside of the row with the DISABLE STORAGE IN ROW clause:

create table patchmain(

patch_id number

,patch_desc clob

,log_file blob)

lob(patch_desc, log_file)

store as (

tablespace lob_data

disable storage in row);

If you want to store up to 4000 characters of a LOB in the table row, use the ENABLE STORAGE IN ROW

clause when creating the table:

create table patchmain(

patch_id number

,patch_desc clob

,log_file blob)

lob(patch_desc, log_file)

store as (

tablespace lob_data

enable storage in row);

■
Note
The LOB locator is always stored in line with the row.

You can’t modify the LOB storage in a row after the table has been created. The only ways to alter storage in row is to either move the LOB column or drop and re-create the table. This example alters the storage in row by moving the LOB column:

alter table patchmain

move lob(patch_desc)

store as (enable storage in row);

You can verify the in-row storage via the IN_ROW column of USER_LOBS: select

table_name

,column_name

,tablespace_name

255

CHAPTER 11 ■ LARGE OBJECTS

,in_row

from user_lobs;

A value of YES indicates that the LOB is stored in row:

TABLE_NAME COLUMN_NAME TABLESPACE_NAME IN_ROW

--------------- --------------- --------------- ------

PATCHMAIN LOG_FILE LOB_DATA NO

PATCHMAIN PATCH_DESC LOB_DATA YES

Using SecureFile Features

As mentioned previously in this chapter, the SecureFile LOB architecture allows you to compress LOB

columns, eliminate duplicates, and transparently encrypt LOB data. These features provide high performance and manageability of LOB data and are available in Oracle Database 11
g
and higher. The next few subsections cover features specific to SecureFiles.

Compressing LOBs

If you’re using SecureFile LOBs, then you can specify a degree of compression. The benefit is that the LOBs consume much less space in the database. The downside is that reading and writing the LOBs may take longer. See Table 11–4 for a description of the compression values.

This example creates a CLOB column with a low degree of compression:

CREATE TABLE patchmain(

patch_id NUMBER

,patch_desc CLOB)

LOB(patch_desc) STORE AS SECUREFILE

(COMPRESS LOW)

TABLESPACE inv_clob;

Table 11–4.
Degrees of Compression Available with SecureFile LOBs
Compression Type

Description

HIGH

Highest degree of compression. Incurs higher

latency when reading and writing the LOB.

MEDIUM

Medium level of compression. Default value if

compression is specified but with no degree.

LOW

Lowest level of compression. Provides the lowest

latency when reading and writing the LOB.

COMPRESS clause isn’t specified.

No compression is used if you don’t specify the

COMPRESS clause.

If a LOB has been created as a SecureFile, you can alter its compression level. For example, this changes the compression to HIGH:

256

CHAPTER 11 ■ LARGE OBJECTS

ALTER TABLE patchmain

MODIFY LOB(patch_desc)

(COMPRESS HIGH);

If you create a LOB with compression but decide that you don’t want to use the feature, you can alter the LOB to have no compression via the NOCOMPRESS clause:

ALTER TABLE patchmain

MODIFY LOB(patch_desc)

(NOCOMPRESS);

■
Tip
Try to enable compression, deduplication, and encryption through a CREATE TABLE statement. If you use an ALTER TABLE statement, the table is locked while the LOB is modified.

Deduplicating LOBs

If you have an application where identical LOBs are associated with two or more rows, you should consider using the SecureFile deduplication feature. When enabled, this instructs Oracle to check when a new LOB is inserted into a table and see whether that LOB is already stored in another row (for the same LOB column). If it’s already stored, then Oracle stores a pointer to the existing identical LOB. This can potentially mean huge space savings for your application.

■
Note
Deduplication requires the Oracle Advanced Compression option. See the Oracle Database Licensing Information guide (available on OTN) for more information.

This example creates a LOB column using the deduplication feature:

CREATE TABLE patchmain(

patch_id NUMBER

,patch_desc CLOB)

LOB(patch_desc) STORE AS SECUREFILE

(DEDUPLICATE)

TABLESPACE inv_clob;

To verify that the deduplication feature is in effect, run this query: select

table_name

,column_name

,deduplication

from user_lobs;

257

CHAPTER 11 ■ LARGE OBJECTS

Here’s some sample output:

TABLE_NAME COLUMN_NAME DEDUPLICATION

--------------- --------------- ---------------

PATCHMAIN PATCH_DESC LOB

If an existing table has a SecureFile LOB, then you can alter the column to enable deduplication: alter table patchmain

modify lob(patch_desc) (deduplicate);

Here’s another example that modifies a partitioned LOB to enable deduplication: alter table patchmain modify partition p2

lob (patch_desc) (deduplicate lob);

If you decide that you don’t want deduplication enabled, use the KEEP_DUPLICATES clause: alter table patchmain

modify lob(patch_desc) (keep_duplicates);

Encrypting LOBs

You can transparently encrypt a SecureFile LOB column (just like any other column). Before you use encryption features, you must set up an encryption
wallet
. If you don’t know how to setup a wallet, I’ve included a sidebar at the end of this subsection that describes this task. Also, see the
Oracle Advanced
Security
guide (available on OTN) for more details.

■
Note
The SecureFile encryption feature requires a license for the Oracle Advanced Security Option. See the Oracle Database Licensing Information guide (available on OTN) for more information.

The ENCRYPT clause enables SecureFile encryption using Oracle Transparent Data Encryption (TDE).

The following example enables encryption for the PATCH_DESC LOB column: CREATE TABLE patchmain(

patch_id number

,patch_desc clob)

LOB(patch_desc) STORE AS SECUREFILE (encrypt)

tablespace inv_clob;

When you describe the table, the LOB column now shows that encryption is in effect: SQL> desc patchmain;

Name Null? Type

----------------------------------------- -------- ----------------------------

PATCH_ID NUMBER

PATCH_DESC CLOB ENCRYPT

258

CHAPTER 11 ■ LARGE OBJECTS

The wallet must be open for TDE to work. If the wallet is closed, you can’t access encrypted columns. If you stop and restart your database, you must reopen the wallet before you can access encrypted columns.

SQL> alter system set encryption wallet open authenticated by "foobar"; In the prior statement, you must use the same password that you used when you created the wallet: Migrating BasicFiles to SecureFiles

You can migrate BasicFile LOB data to SecureFiles via one of the following methods:

• Create a new table, load the data from the old table, and rename the tables.

• Move the table.

• Redefine the table online.

Each of these techniques is described in the following subsections.

Creating a New Table

Here’s a brief example of creating a new table and loading data from the old table. In this example, PATCHMAIN_NEW is the new table being created with a SecureFile LOB.

create table patchmain_new(

patch_id number

,patch_desc clob)

lob(patch_desc) store as securefile (tablespace lob_data);

Next, load the newly created table with data from the old table:

SQL> insert into patchmain_new select * from patchmain;

Now, rename the tables:

SQL> rename patchmain to patchmain_old;

SQL> rename patchmain_new to patchmain;

When using this technique, be sure any grants that were pointing at the old table are reissued for the new table.

Moving a Table to a SecureFile Architecture

You can also use the ALTER TABLE...MOVE statement to redefine the storage of a LOB as a SecureFile. For example:

alter table patchmain

move lob(patch_desc)

store as securefile (tablespace inv_clob);

You can verify that the column is now a SecureFile via this query:

SQL> select table_name, column_name, securefile from user_lobs;

260

CHAPTER 11 ■ LARGE OBJECTS

The SECUREFILE column now has a value of YES:

TABLE_NAME COLUMN_NAME SEC

--------------- --------------- ---

PATCHMAIN PATCH_DESC YES

Online Redefinition

You can also redefine a table while it’s online via the DBMS_REDEFINITION package. Use the following steps to do an online redefinition:

1. Ensure that the table has a primary key. If the table doesn’t have a primary key, then create one:

alter table patchmain

add constraint patchmain_pk

primary key (patch_id);

2. Create a new table that defines the LOB column(s) as SecureFile:

create table patchmain_new(

patch_id number

,patch_desc clob)

lob(patch_desc)

store as securefile (tablespace lob_data);

3. Map the columns of the new table to the original table:

declare

l_col_map varchar2(2000);

begin

l_col_map := 'patch_id patch_id, patch_desc patch_desc';

dbms_redefinition.start_redef_table(

'DARL','PATCHMAIN','PATCHMAIN_NEW',l_col_map

);

end;

/

4. Copy the data (this can take a long time if there are many rows): set serverout on size 1000000

declare

l_err_cnt integer :=0;

begin

dbms_redefinition.copy_table_dependents(

'DARL','PATCHMAIN','PATCHMAIN_NEW',1,TRUE, TRUE, TRUE, FALSE, l_err_cnt

);

dbms_output.put_line('Num Errors: ' || l_err_cnt);

end;

/

5. Finish the redefinition:

begin

dbms_redefinition.finish_redef_table('DARL','PATCHMAIN','PATCHMAIN_NEW'); end;