Describe 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;
Required keywords for type definition
Unique name for type
Required keywords for varray definition
Integer that sets the boundary for the number of entries in the varray
Required keyword
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.