1
Introduction to LOBs

This chapter discusses the following topics:

• Why Use LOBs?
• Why Not Use LONGs?
• LONG-to-LOB Migration API
• SQL Semantics Support for LOBs
• Partitioned Index-Organized Tables and LOBs
• Extensible Indexing on LOBs
• Function-Based Indexing on LOBs
• XML Documents Can be Stored in XMLType Columns as CLOBs
• Compatibility and Migration Issues
• Examples in This Guide

Why Use LOBs?

As applications evolve to encompass increasingly richer semantics, they encounter the need to deal with the following kinds of data:

• Simple structured data
• Complex structured data
• Semi-structured data
• Unstructured data

Traditionally, the Relational model has been very successful at dealing with simple structured data -- the kind which can be fit into simple tables. Oracle has added Object-Relational features so that applications can deal with complex structured data -- collections, references, user-defined types and so on. Our queuing technologies, such as Advanced Queueing, deal with Messages and other semi-structured data.

LOBs are designed to support the last kind of data -- unstructured data.

Unstructured Data

Unstructured Data Cannot be Decomposed Into Standard Components

Unstructured data cannot be decomposed into standard components. Data about an Employee can be 'structured' into a Name (probably a character string), an identification (likely a number), a Salary and so on. But if you are given a Photo, you find that the data really consists of a long stream of 0s and 1s. These 0s and 1s are used to switch pixels on or off so that you will see the Photo on a display, but they can't be broken down into any finer structure in terms of database storage.

Unstructured Data is Large

Also interesting is that unstructured data such as text, graphic images, still video clips, full motion video, and sound waveforms tend to be large -- a typical employee record may be a few hundred bytes, but even small amounts of multimedia data can be thousands of times larger.

Unstructured Data in System Files Need Accessing from the Database

Finally, some multimedia data may reside on operating system files, and it is desirable to access them from the database.

LOB Datatype Helps Support Internet Applications

With the growth of the internet and content-rich applications, it has become imperative that the database support a datatype that fulfills the following:

• Can store unstructured data
• Is optimized for large amounts of such data
• Provides a uniform way of accessing large unstructured data within the database or outside

Two Type of LOBs Supported

Oracle supports the following two types of LOBs

• Those stored in the database either in-line in the table or in a separate segment or tablespace, such as BLOB, CLOB, and NCLOB.
• Those stored as operating system files, such as BFILEs.

Using XML, LOBs, and Oracle Text (interMedia Text)

Use CLOBs or BFILEs to Store Unstructured Data

CLOBs can store large amounts of character data and are useful for storing unstructured XML documents. Also useful for storing multimedia data, BFILEs which are external file references can also be used. In this case the XML is stored and managed outside the RDBMS, but can be used in queries on the server.

Oracle Text (interMedia Text) Indexing Supports Searching Content in XML Elements

You can create Oracle Text (interMedia Text) indexes on CLOB columns and perform queries on XML.

LOBS Enable Oracle Text (interMEDIA Text)

While LOBs provide the infrastructure in the database to store multimedia data, Oracle8i and Oracle9i also provide developers with additional functionality for the most commonly used multimedia types. The multimedia types include text, image, locator, audio, and video data.

Oracle8i introduced the interMedia bundle, that supports text data, spatial location, images, audio, and video data. You can access interMedia objects using SQL queries, manipulate their contents (such as, trim an image), read and write their content, and convert data from one format to another.

interMedia in turn uses Oracle's infrastructure to define object types, methods, and LOBs necessary to represent these specialized types of data in the database. Oracle interMedia provide a predefined set of objects and operations that facilitate application development.

See also http://otn.oracle.com/products/text

Why Not Use LONGs?

In Oracle7, most applications storing large amounts of unstructured data used the LONG or LONG RAW data type.

Oracle8i and Oracle9i's support for LOB data types is preferred over support for LONG and LONG RAWs in Oracle7 in the following ways:

• LOB Capacity: With Oracle8 and Oracle8i, LOBs can store up to 4GB of data. This doubles the 2GB of data that LONG and LONG RAW data types could store.
• Number of LOB columns in a table: An Oracle8, Oracle8i, or Oracle9i table can have multiple LOB columns. Each LOB column in the same table can be of a different type. In Oracle7 Release 7.3 and higher, tables are limited to a single LONG or LONG RAW column.
• Random piece-wise access: LOBs support random access to data, but LONGs support only sequential access.

LOB Columns

LOB (BLOB, CLOB, NCLOB, or BFILE) column types store values or references, called locators. Locators specify the location of large objects.

LOB Columns Do Not Only Store Locators!

In LOB columns, the LOB locator is stored in-line in the row. Depending on the user-specified SQL data definition language (DDL) storage parameters, Oracle9i can store small LOBs, less than approximately 4K in-line in the table. Once the LOB grows bigger than approximately 4K Oracle9i moves the LOB out of the table into a different segment and possibly even into a different tablespace. Hence, Oracle9i sometimes stores LOB data, not just LOB locators, in-line in the row.

BLOB, CLOB, and NCLOB data is stored out-of-line inside the database. BFILE data is stored in operating system files outside the database. Oracle9i provides programmatic interfaces and PL/SQL support for access to and operation on LOBs.

LONG-to-LOB Migration API

Oracle9i supports LONG as well as LOB datatypes. When possible, change your existing applications to use LOBs instead of LONGs because of the added benefits that LOBs provide.

LONG-to-LOB migration allows you to easily migrate your existing applications that access LONG columns, to use LOB columns. The migration has two parts:

• Data migration
• Application migration

  See Also: See Chapter 7, "Modeling and Design", "LOBs Compared to LONG and LONG RAW Types". Chapter 8, "Migrating From LONGs to LOBs"

SQL Semantics Support for LOBs

In this release, for the first time, you can access LOBs using SQL VARCHAR2 semantics, such as SQL string operators and functions.

By providing you with an SQL interface, which you are familiar with, accessing LOB data can be greatly facilitated. You can benefit from this added functionality in the following two cases:

• When using small-sized LOBs (~ 10-100K) to store data and you need to access the LOB data in SQL queries, the syntax is the same as that of VARCHAR2's.
• When you have just migrated your LONG columns to LOBs. In this release, you can take advantage of an easier migration process using the LONG-to-LOB migration API described in Chapter 8, "Migrating From LONGs to LOBs".

  See Also: Chapter 7, "Modeling and Design", "SQL Semantics Support for LOBs".

Partitioned Index-Organized Tables and LOBs

Oracle9i introduces support for LOB, VARRAY columns stored as LOBs, and BFILEs in partitioned index-organized tables. The behavior of LOB columns in these tables is similar to that of LOB columns in conventional (heap-organized) partitioned tables, except for the following differences:

• Tablespace mapping
• Inline as opposed to out-of-line LOBs

LOB columns are supported only in range partitioned index-organized tables.

Extensible Indexing on LOBs

Oracle provides an extensible server which provides 'extensible indexing'. This allows you to define new index types as required. This is based on the concept of cooperative indexing where a data cartridge and Oracle9i build and maintain indexes for data types such as text and spatial for example, for On-line-Analytical Processing (OLAP).

The cartridge is responsible for defining the index structure, maintaining the index content during load and update operations, and searching the index during query processing. The index structure can be stored in Oracle as heap-organized, or an index-organized table, or externally as an operating system file.

To this end, Oracle introduces the concept of an indextype. The purpose of an indextype is to enable efficient search and retrieval functions for complex domains such as text, spatial, image, and OLAP by means of a data cartridge. An indextype is analogous to the sorted or bit-mapped index types that are built-in within the Oracle Server. The difference is that an indextype is implemented by the data cartridge developer, whereas the Oracle kernel implements built-in indexes. Once a new indextype has been implemented by a data cartridge developer, end users of the data cartridge can use it just as they would built-in indextypes.

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

• Define the format and content of an index. This enables cartridges to define an index structure that can accommodate a complex data object.
• Build, delete, and update a domain index. The cartridge handles building and maintaining the index structures. Note that this is a significant departure from the medicine indexing features provided for simple SQL data types. Also, since an index is modeled as a collection of tuples, in-place updating is directly supported.
• Access and interpret the content of an index. This capability enables the data cartridge to become an integral component of query processing. That is, the content-related clauses for database queries are handled by the data cartridge.

By supporting extensible indexes, Oracle9i significantly reduces the effort needed to develop high-performance solutions that access complex datatypes such as LOBs.

Extensible Optimizer

The extensible optimizer functionality allows authors of user-defined functions and indexes to create statistics collection, selectivity, and cost functions. This information is used by the optimizer in choosing a query plan. The cost-based optimizer is thus extended to use the user-supplied information; the rule-based optimizer is unchanged.

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

Function-Based Indexing on LOBs

A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data.

Function-based indexes cannot currently be built on nested tables. However, you can now build function-based indexes on LOB columns and varrays.

XML Documents Can be Stored in XMLType Columns as CLOBs

Composed XML documents can be stored in CLOBs. XMLType columns use CLOBs for storage.

Compatibility and Migration Issues

The following LOB related compatibility and migration issues are described in detail in Oracle9i Database Migration :

• "Varying Width Character Sets for CLOBs or NCLOBs", under "Datatypes, Compatibility and Interpretability Issues".
• Downgrading with CACHE READs Defined: See "Downgrading to an Older Version 8 Release", under "Remove Incompatibilities", "Datatypes", "Discontinue Use of Cache Reads Specified for LOBs".
• Downgrading -- Removing LOB Columns from Partitioned Table: See the chapter, "Downgrading to an Older Version 8 Release", under "Remove Incompatibilities", "Datatypes", "Remove LOB Columns from Partitioned Tables".
• Downgrading -- LOBs and Varrays in Index Organized Tales: See "Downgrading to an Older Version 8 Release", under "Remove Incompatibilities, "Schema Objects", "Discontinue Use of LOBs and Varrays in Index Organized Tables".
• Downgrading --Varying Width Character Sets for CLOBs or NCLOBs: See the chapter, "Downgrading to an Older Version 8 Release", under "Remove Incompatibilities", under "Datatypes", "Remove CLOBs and NCLOBs from Tables in Database with Varying-Width Character Set".
• Downgrading -- Partitoned Index Organized Tables (PIOTs) (on LOBs):See the chapter, "Removing Oracle9i Incompatibilities".
• Downgrading -- Functional Indexes on LOBs: See the chapter, "Removing Oracle9i Incompatibilities".
• Downgrading -- LONG to LOB data and application migration: See the chapter, "Removing Oracle9i Incompatibilities".

Examples in This Guide

Examples in this guide use the following sample schemas:

• Product Media (PM) sample schema
• Multimedia sample schema - deprecated

The Multimedia schema is deprecated and is not supplied with Oracle9i sample schemas. The Product Media schema replaces the Multimedia schema as the sample schema for most LOB examples.

Examples in Chapter 10, Chapter 11, and Chapter 12 of this guide have been migrated to the PM schema. Most other examples have not been migrated and still use the Multimedia schema. Appendix B, "The Multimedia Schema" is included in this guide to provide a context for these examples.

The Product Media sample schema provides tables and sample data that are suited for illustrating LOB database features. Much of this data is unstructured as LOB APIs are designed to handle large unstructured data. "Unstructured Data" is described earlier in this chapter.

The examples in this guide that are based on the PM schema use the Print_media sample table.