Large Objects   «Prev  Next»

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

Large varrays and LOBs

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: 
CREATE TYPE typename AS VARRAY (n) 
OF datatype;

  1. Required keywords for type definition
  2. Unique name for type
  3. Required keywords for varray definition
  4. Integer that sets the boundary for the number of entries in the varray
  5. Required keyword
  6. 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.

Large Varrays Lobs
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.