This chapter introduces Large Objects (LOBs), SecureFiles LOBs, and Database File System (DBFS) and discusses how LOB data types are used in application development.
Large Objects are used to hold large amounts of data inside Oracle Database, SecureFiles provides performance equal to or better than file system performance when using Oracle Database to store and manage Large Objects, and DBFS provides file system access to files stored in Oracle Database.
This chapter contains these topics:
Large Objects (LOBs) are a set of data types that are designed to hold large amounts of data. The maximum size for a single LOB can range 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.
This section describes 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. Note that large objects are suitable for the last two: semistructured and unstructured.
Simple structured data
Simple structured data can be organized into relational tables that are structured based on business rules.
Complex structured data
Complex structured data is more complex than simple structured data and is suited for the object-relational features of the Oracle database such as collections, references, and user-defined types.
Semistructured data
Semistructured data has a logical structure that is not typically interpreted by the database, for example, an XML document that your application or an external service processes. Oracle Database provides features such as Oracle XML DB, Oracle Multimedia DICOM, and Oracle Spatial and Graph to help your application work with semistructured data.
Unstructured data
Unstructured data is easily not broken down into smaller logical structures and is not typically interpreted by the database or your application, such as a photographic image stored as a binary file.
Large objects are suitable for semistructured and unstructured data. Large object 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 for Oracle Database to provide LOB support that:
Can store unstructured and semistructured 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
Semistructured data include document files such as XML documents or word processor files, which 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 that use semistructured data often 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.
Unstructured data is data that cannot be decomposed into standard components.
This is in contrast to structured data, such as data about an employee typically containing these components: a name, stored as a string; an identifier, such as an ID number; a salary; and so on.
Unstructured data, such as a photograph, 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 the bits are not broken down into any standard components 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).
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 Oracle7 and earlier used the LONG or LONG RAW data type to store large amounts of unstructured data.
With Oracle8i and later versions of the database, using LOB data types is recommended for storing large amounts of structured and semistructured 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 4 GB of data or more depending on your system configuration. LONG and LONG RAW types are limited to 2 GB 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 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.
LOBs can also be object attributes.
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.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".
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, and Durability (ACID) properties that apply to database objects apply to persistent LOBs.
External LOBs are data objects stored in operating system files, outside the database tablespaces.
BFILE is the SQL data type that the database uses to access external LOBs and is the only SQL data type available for external LOBs.
BFILEs are read-only data types. The database allows read-only byte stream access to data stored in BFILEs. You cannot write to or update 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.
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
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.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.
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 because external LOBs are read-only as described in "External LOBs and the BFILE Data Type".
The database provides a set of large object data types as SQL data types where the term LOB generally refers to the set. 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.
Table 1-1 describes each large object data type that the database supports and describes the kind of data that uses it.
Table 1-1 Large Object Data Types
| SQL Data Type | Description |
|---|---|
|
Binary Large Object Stores any kind of data in binary format. Typically used for multimedia data such as images, audio, and video. |
|
|
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. |
|
|
National Character Set Large Object Stores string data in National Character Set format, typically large strings or documents. Supports characters of varying width format. |
|
|
External Binary File A binary file stored outside of the database in the host operating system file system, but accessible from database tables. Any kind of data, that is, any operating system file, can be stored in a |
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 use of a LOB instance in a LOB column or as a member of an object data type. 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.
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.
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 is not specified, and the declared size of varray data is more than 4000 bytes: VARRAY varray_item STORE AS
If the VARRAY column properties are specified using the STORE AS LOB clause: VARRAY varray_item STORE AS LOB ...
Oracle Multimedia uses LOB data types to create object types specialized for use in multimedia application. Multimedia data types include ORDAudio, ORDDoc, ORDImage, ORDVideo, and ORDDicom. 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 about Oracle Multimedia
Oracle Multimedia Reference for more information about Oracle Multimedia data types
SecureFiles LOB storage is one of two storage types used with Oracle Database 12c; the other type is BasicFiles LOB storage. Certain advanced features can be applied to SecureFiles LOBs, including compression and deduplication (part of the Advanced Compression Option), and encryption (part of the Advanced Security Option).
SecureFiles LOBs can only be created in a tablespace managed with Automatic Segment Space Management (ASSM).
SecureFiles is the default storage mechanism for LOBs starting with Oracle Database 12c, and Oracle strongly recommends SecureFiles for storing and managing LOBs, rather then BasicFiles. BasicFiles will be deprecated in a future release.
See Also:
"Using Oracle LOB Storage" for a discussion of both storage typesDatabase File System (DBFS) provides a file system interface to files that are stored in an Oracle Database. The files are usually stored as SecureFiles LOBs, and pathnames, directories, and other filesystem information is stored in database tables. SecureFiles LOBs is the default storage method for DBFS, but BasicFiles LOBs can be used in some situations.
See Also:
"What is Database File System (DBFS)?"With DBFS, you can make references from SecureFiles LOB locators to files stored outside the database. These references are called DBFS Links or Database File System Links.
See Also:
"Database File System Links"