Large Objects   «Prev  Next»

Lesson 6Large varrays and LOBs
ObjectiveDescribe how Oracle uses LOBs to store large varrays

Utilization of Large Objects (LOBs) for Storing Large Varrays in Oracle 12c Database

This article contains an examination of Oracle 12c Database's usage of Large Objects (LOBs) for managing the storage of extensive varrays. Oracle Database provides robust capabilities for storing, managing, and accessing large amounts of data, and LOBs are instrumental in efficiently handling large varrays, enhancing the performance, scalability, and reliability of database operations.
In Oracle Database 12c, Large Objects (LOBs) play a critical role in storing and managing large varrays efficiently. Varrays, or varying arrays, are a collection of elements with the same data type, offering efficient storage and retrieval of collections of related data items. Oracle’s implementation of LOBs enables the secure, efficient, and effective management of these large varray elements, ensuring optimal performance and data integrity.

LOBs in Oracle 12c:

In the context of Oracle 12c, LOBs are used to store large blocks of unstructured data (such as text, image, video, and spatial data) and structured data like varrays. LOBs can be categorized into two types: internal LOBs (BLOBs and CLOBs) that store data inside the database, and external LOBs (BFILEs) that store data outside the database in operating system files. Internal LOBs, especially, are widely used to manage and store large varrays efficiently.

Storing Varrays as LOBs:

When dealing with large varrays in Oracle 12c, the data is stored as LOBs to ensure efficient data management and access. This storage mechanism allows for optimized performance and scalability, ensuring that large volumes of data can be handled seamlessly without compromising on performance or data integrity.
  1. Efficient Storage: Storing large varrays as LOBs in Oracle 12c allows the database to manage the data efficiently, optimizing storage allocation and ensuring rapid access to the data elements. LOB data is stored in a way that maximizes the efficiency of space utilization and data retrieval operations.
  2. Enhanced Performance: The use of LOBs for storing large varrays optimizes database performance by ensuring that data access and manipulation operations are conducted efficiently. LOB locators are used to manage access to the LOB data, enhancing the speed and efficiency of database operations involving large varrays.
  3. Security and Data Integrity: LOBs in Oracle 12c provide a secure mechanism for storing large varrays, ensuring the confidentiality, integrity, and availability of the data. Adequate access controls and security mechanisms are enforced, ensuring that the data is protected from unauthorized access or modification.
  4. Improved Scalability: Storing large varrays as LOBs enhances the scalability of the Oracle 12c Database, allowing it to handle larger volumes of data seamlessly. This scalability ensures that the database can accommodate growing data requirements efficiently, maintaining optimal performance and reliability.
In summary, Oracle 12c utilizes LOBs to manage the storage of large varrays effectively, ensuring optimal performance, efficient storage, robust security, and enhanced scalability. This mechanism allows organizations to handle large volumes of structured data like varrays seamlessly, promoting efficient and reliable database operations.

The Object Relational Model and Collection

One of the concepts behind the object-relational model implemented by Oracle is the idea of a collection. A collection is a model of a one-to-many relationship. An object can contain a collection of other entities, such as a purchase order containing a collection of line items.
Two types of collections are supported by Oracle varrays and nested tables. As their names imply, a varray is very much like an array, in that it is stored as a complete entity, whereas a nested table is stored like a collection of rows in a table. A varray is appropriate for storage of information that will not be subjected to individual queries, whereas a nested table is more appropriate for efficient retrieval of individual members of a collection. In addition, a varray is stored in order and has distinct boundaries, whereas a nested table has neither of these characteristics.

Varrays and LOBs

Within Oracle, varrays must be stored in BLOBs. If a varray has fewer than 4,000 bytes, it could also be stored as an in-line BLOB or in a separate BLOB out of line. When you define an object type with a varray in it, you specify the LOB that will hold the varray with the syntax explained in the following MouseOver:

Typename varray 

CREATE TYPE typename 
AS VARRAY (n) 
OF datatype;

CREATE TYPE Required keywords for type definition.
typename Unique name for type.
AS VARRAY Required keywords for varray definition
n Integer that sets the boundary for the number of entries in the varray
OF Required keyword
datatype Data type specification for varray. This can be a built-in data type or an object type. It cannot be another varray type, a nested table type, or a LOB type.

Within , varrays must be stored in BLOBs. A BLOB can be either in-line,Oracle which means it is stored as part of a database table structure, if it has fewer than 4000 bytes. If the BLOB has more than 4000 bytes, it must be stored out-of-line, which means that the content of the BLOB is stored outside of the table, while the table contains a pointer to the BLOB contents. The schema for the PETSTORE database contains a varray for phone numbers. The varray type is defined with the following syntax. 

CREATE TYPE PHONE_ARRAY AS 
VARRAY(10) OF VARCHAR2(15);

This object type allows for the storage of up to 10 phone numbers, which can be up to 15 characters in length. You could use the PHONE_ARRAY data type in another table or object type, such as the CUSTOMER_TYPE object.
In the next lesson, you will learn about temporary LOBs.