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
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:
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.