|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)
Part Number A96591-01
This chapter discusses the following topics:
As applications evolve to encompass increasingly richer semantics, they encounter the need to deal with the following kinds of 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 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.
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.
Finally, some multimedia data may reside on operating system files, and it is desirable to access them from the database.
With the growth of the internet and content-rich applications, it has become imperative that the database support a datatype that fulfills the following:
Oracle supports the following two types of LOBs
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.
You can create Oracle Text (interMedia Text) indexes on CLOB columns and perform queries on XML.
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
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 (BLOB, CLOB, NCLOB, or BFILE) column types store values or references, called locators. Locators specify the location of large objects.
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.
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:
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:
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:
LOB columns are supported only in range partitioned index-organized tables.
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
By supporting extensible indexes, Oracle9i significantly reduces the effort needed to develop high-performance solutions that access complex datatypes such as LOBs.
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.
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.
Oracle9i Application Developer's Guide - Fundamentals for more information about using function-based indexing.
Composed XML documents can be stored in CLOBs.
XMLType columns use CLOBs for storage.
Oracle9i XML Developer's Kits Guide - XDK, Chapter 5,for information about XMLType, and how XML is stored in LOBs.
The following LOB related compatibility and migration issues are described in detail in Oracle9i Database Migration :
Examples in this guide use the following sample schemas:
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.
For further details on the Product Media sample schema, you must refer to Oracle9i Sample Schemas.