Store your media data with SecureFiles LOBs.

Oracle recommends using SecureFiles LOBs for storing media data whenever possible. SecureFiles LOBs are identified by specifying the SQL parameter SECUREFILE.

You can achieve the best performance for LOBs by specifying storage for LOBs in a different tablespace than the one used for the table that contains the LOB.

If many different LOBs are to be accessed frequently, you can also specify a separate tablespace for each LOB column or attribute to reduce device contention.

The cache option is a part of the STORE AS clause, and determines whether LOB pages are stored in the buffer cache.

These are the possible values for the cache option:

• For the value CACHE, Oracle places LOB pages in the buffer cache where they can be shared among multiple users. Over time and if the LOB pages are no longer accessed, the pages are eventually removed from the buffer cache.

• For the value NOCACHE, LOB pages are not placed in the buffer cache.

• For the value CACHE READS, LOB pages are placed in the cache for read operations only.

If your application performs multiple read operations on a media object (for example: invoking the getProperties( ) procedure or the setProperties( ) method and then generating a thumbnail image), enable read caching for the source media LOB.

The logging option is a part of the STORE AS clause, and determines if REDO data is logged when a LOB is updated.

If the [NO]LOGGING clause is omitted, neither NOLOGGING nor LOGGING is specified and the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.

There is another alternative depending on how the cache option is specified.

• If CACHE is specified and [NO]LOGGING is omitted, LOGGING is automatically implemented (because you cannot have CACHE NOLOGGING).

• If CACHE is not specified and [NO]LOGGING is omitted, the [NO]LOGGING value is obtained from the tablespace in which the LOB segment resides.

Specify NOLOGGING only when you do not care about media recovery. However, if the disk, tape, or storage media fails, you will not be able to recover your changes from the log because those changes were not logged.

NOLOGGING can be useful for bulk loading of media data. For instance, when loading data into the LOB, if you do not care about the redo operation and you can start the load over if it fails, set the LOB data segment storage characteristics to NOCACHE NOLOGGING. This option provides good performance for the initial loading of data.

After you finish loading data, if necessary, you can use the ALTER TABLE statement to modify the LOB storage characteristics for the LOB data segment for normal LOB operations (for example: to CACHE or NOCACHE LOGGING).

This section describes a simple example that shows how to use the performance profiles of Oracle Multimedia operations to guide your usage of LOB storage options.

create table images(id        integer primary key,
                    original  ordsys.ordimage,
                    scale50   ordsys.ordimage,
                    scale25   ordsys.ordimage)
tablespace tbs1
lob(original.source.localdata)store as secureFile (tablespace tbs2)
lob(scale50.source.localdata)store as secureFile (tablespace tbs2)
lob(scale25.source.localdata)store as secureFile (tablespace tbs2);

alter table images modify lob(original.source.localdata) (nocache nologging);

alter table images modify lob(original.source.localdata) (cache reads);
alter table images modify lob(scale50.source.localdata) (nocache nologging);
alter table images modify lob(scale25.source.localdata) (nocache nologging);

alter table images modify lob(original.source.localdata) (nocache logging);
alter table images modify lob(scale50.source.localdata) (nocache logging);
alter table images modify lob(scale25.source.localdata) (nocache logging);