1 Introduction to Large Objects

This chapter introduces Large Objects (LOBs) and discusses how LOB data types are used in application development. This chapter contains these topics:

What Are Large Objects?
Why Use Large Objects?
Why Not Use LONGs?
Different Kinds of LOBs
Introducing LOB Locators
Database Semantics for Internal and External LOBs
Large Object Data Types
Object Data Types and LOBs
Storing and Creating Other Data Types with LOBs

What Are Large Objects?

Large Objects (LOBs) are a set of data types that are designed to hold large amounts of data. A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

Why Use Large Objects?

This section introduces different types of data that you encounter when developing applications and discusses which kinds of data are suitable for large objects.

In the world today, applications must deal with the following kinds of data:

Simple structured data.

This data can be organized into simple tables that are structured based on business rules.
Complex structured data

This kind of data is complex in nature and is suited for the object-relational features of the Oracle database such as collections, references, and user-defined types.
Semi-structured data

This kind of data has a logical structure that is not typically interpreted by the database. For example, an XML document that is processed by your application or an external service, can be thought of as semi-structured data. The database provides technologies such as Oracle XML DB, Advanced Queuing, and Messages to help your application work with semi-structured data.
Unstructured data

This kind of data is not broken down into smaller logical structures and is not typically interpreted by the database or your application. A photographic image stored as a binary file is an example of unstructured data.

Large objects are suitable for these last two kinds of data: semi-structured data and unstructured data. Large objects features allow you to store these kinds of data in the database and in operating system files that are accessed from the database.

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

Can store unstructured and semi-structured data in an efficient manner.
Is optimized for large amounts of data.
Provides a uniform way of accessing data stored within the database or outside the database.

Using LOBs for Semi-structured Data

Examples of semi-structured data include document files such as XML documents or word processor files. These kinds of documents contain data in a logical structure that is processed or interpreted by an application, and is not broken down into smaller logical units when stored in the database.

Applications involving semi-structured data typically use large amounts of character data. The Character Large Object (CLOB) and National Character Large Object (NCLOB) data types are ideal for storing and manipulating this kind of data.

Binary File objects (BFILE data types) can also store character data. You can use BFILEs to load read-only data from operating system files into CLOB or NCLOB instances that you then manipulate in your application.

Using LOBs for Unstructured Data

Unstructured data cannot be decomposed into standard components. For example, data about an employee can be structured into a name, which is stored as a string; an identifier, such as an ID number, a salary and so on. A photograph, on the other hand, consists of a long stream of 1s and 0s. These bits are used to switch pixels on or off so that you can see the picture on a display, but are not broken down into any finer structure for database storage.

Also, unstructured data such as text, graphic images, still video clips, full motion video, and sound waveforms tends to be large in size. A typical employee record may be a few hundred bytes, while even small amounts of multimedia data can be thousands of times larger.

SQL data types that are ideal for large amounts of unstructured binary data include the BLOB data type (Binary Large Object) and the BFILE data type (Binary File object).

Why Not Use LONGs?

The database supports LONG and LOB data types. When possible, change your existing applications to use LOBs instead of LONGs because of the added benefits that LOBs provide. LONG-to-LOB migration enables you to easily migrate your existing applications that access LONG columns, to use LOB columns.

Applications developed for use with Oracle Database version 7 and earlier, used the LONG or LONG RAW data type to store large amounts of unstructured data.

With the Oracle8i and later versions of the database, using LOB data types is recommended for storing large amounts of structured and semi-structured data. LOB data types have several advantages over LONG and LONG RAW types including:

LOB Capacity: LOBs can store much larger amounts of data. LOBs can store 4GB of data or more depending on you system configuration. LONG and LONG RAW types are limited to 2GB of data.
Number of LOB columns in a table: A table can have multiple LOB columns. LOB columns in a table can be of any LOB type. In Oracle Database 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.
LOBs can also be object attributes.

Different Kinds of LOBs

Different kinds of LOBs can be stored in the database or in external files.

Note:

LOBs in the database are sometimes also referred to as internal LOBs or internal persistent LOBs.

Internal LOBs

LOBs in the database are stored inside database tablespaces in a way that optimizes space and provides efficient access. The following SQL data types are supported for declaring internal LOBs: BLOB, CLOB, and NCLOB. Details on these data types are given in "Large Object Data Types".

Persistent and Temporary LOBs

Internal LOBs (LOBs in the database) can be either persistent or temporary. A persistent LOB is a LOB instance that exists in a table row in the database. A temporary LOB instance is created when you instantiate a LOB only within the scope of your local application.

A temporary instance becomes a persistent instance when you insert the instance into a table row.

Persistent LOBs use copy semantics and participate in database transactions. You can recover persistent LOBs in the event of transaction or media failure, and any changes to a persistent LOB value can be committed or rolled back. In other words, all the Atomicity Consistency Isolation Durability (ACID) properties that pertain to using database objects pertain to using persistent LOBs.

External LOBs and the BFILE Data Type

External LOBs are data objects stored in operating system files, outside the database tablespaces. The database accesses external LOBs using the SQL data type BFILE. The BFILE data type is the only external LOB data type.

BFILEs are read-only data types. The database allows read-only byte stream access to data stored in BFILEs. You cannot write to a BFILE from within your application.

The database uses reference semantics with BFILE columns. Data stored in a table column of type BFILE, is physically located in an operating system file, not in the database tablespace.

You typically use BFILEs to hold:

Binary data that does not change while your application is running, such as graphics.
Data that is loaded into other large object types, such as a BLOB or CLOB where the data can then be manipulated.
Data that is appropriate for byte-stream access, such as multimedia.
Read-only data that is relatively large in size, to avoid taking up large amounts database tablespace.

Any storage device accessed by your operating system can hold BFILE data, including hard disk drives, CD-ROMs, PhotoCDs and DVDs. The database can access BFILEs provided the operating system supports stream-mode access to the operating system files.

Note:

External LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying file system as governed by the operating system.

Introducing LOB Locators

A LOB instance has a locator and a value. The LOB locator is a reference to where the LOB value is physically stored. The LOB value is the data stored in the LOB.

When you use a LOB in an operation such as passing a LOB as a parameter, you are actually passing a LOB locator. For the most part, you can work with a LOB instance in your application without being concerned with the semantics of LOB locators. There is no requirement to dereference LOB locators, as is required with pointers in some programming languages.

There are some issues regarding the semantics of LOB locators and how LOB values are stored that you should be aware of. These details are covered in the context of the discussion where they apply throughout this guide.

See Also:

Database Semantics for Internal and External LOBs

In all programmatic environments, database semantics differ between internal LOBs and external LOBs as follows:

Internal LOBs use copy semantics.

With copy semantics, both the LOB locator and LOB value are logically copied during insert, update, or assignment operations. This ensures that each table cell or each variable containing a LOB, holds a unique LOB instance.
External LOBs use reference semantics.

With reference semantics, only the LOB locator is copied during insert operations. (Note that update operations do not apply to external LOBs as external LOBs are read-only. This is explained in more detail later in this section.)

Large Object Data Types

Table 1-1 describes each large object data type supported by the database and describes the kind of data each data type is typically used for. The names of data types given here are the SQL data types provided by the database. In general, the descriptions given for the data types in this table and the rest of this book also apply to the corresponding data types provided for other programmatic environments. Also, note that the term LOB generally refers to the set of all large object data types.

Table 1-1 Large Object Data Types

SQL Data Type	Description
`BLOB`	Binary Large Object Stores any kind of data in binary format. Typically used for multimedia data such as images, audio, and video.
`CLOB`	Character Large Object Stores string data in the database character set format. Used for large strings or documents that use the database character set exclusively. Characters in the database character set are in a fixed width format.
`NCLOB`	National Character Set Large Object Stores string data in National Character Set format. Used for large strings or documents in the National Character Set. Supports characters of varying width format.
`BFILE`	External Binary File A binary file stored outside of the database in the host operating system file system, but accessible from database tables. `BFILE`s can be accessed from your application on a read-only basis. Use `BFILE`s to store static data, such as image data, that is not manipulated in applications. Any kind of data, that is, any operating system file, can be stored in a `BFILE`. For example, you can store character data in a `BFILE` and then load the `BFILE` data into a `CLOB` specifying the character set upon loading.

Object Data Types and LOBs

You can declare LOB data types as fields, or members, of object data types. For example, you can have an attribute of type CLOB on an object type. In general, there is no difference in the usage of a LOB instance in a LOB column and the usage of a LOB instance that is a member or of an object data type. Any difference in usage is called out when it applies. When used in this guide, the term LOB attribute refers to a LOB instance that is a member of an object data type. Unless otherwise specified, discussions that apply to LOB columns also apply to LOB attributes.

Storing and Creating Other Data Types with LOBs

You can use LOBs to create other user-defined data types or store other data types as LOBs. This section discusses some of the data types provided with the database as examples of data types that are stored or created with LOB types.

VARRAYs Stored as LOBs

An instance of type VARRAY in the database is stored as an array of LOBs when you create a table in the following scenarios:

If the VARRAY storage clause— VARRAY varray_item STORE AS —is not specified, and the declared size of varray data is more than 4000 bytes.
If the varray column properties are specified using the STORE AS LOB clause— VARRAY varray_item STORE AS LOB ...

XMLType Columns Stored as CLOBs

A good example of how LOB data types can be used to store other data types is the XMLType data type. The XMLType data type is sometimes stored as a CLOB. Setting up your table or column to store XMLType data types as CLOBs enables you to store schema-less XML documents in the database.

See Also:

Oracle XML DB Developer's Guide for information on creating XMLType tables and columns and how XML is stored in CLOBs.
Oracle XML Developer's Kit Programmer's Guide, for information about working with XML.

LOBs Used in Oracle Multimedia

Oracle Multimedia uses LOB data types to create data types specialized for use in multimedia application such as Multimedia ORDAudio, ORDDoc, ORDImage, and ORDVideo. Oracle Multimedia uses the database infrastructure to define object types, methods, and LOBs necessary to represent these specialized types of data in the database.

See Also:

Oracle Multimedia User's Guide for more information on using Oracle Multimedia.
Oracle Multimedia Reference for more information on using Oracle Multimedia data types.