Oracle® Database Object-Relational Developer's Guide 11g Release 2 (11.2) Part Number E11822-04 |
|
|
PDF · Mobi · ePub |
Comment: Modified on May 20, 2011 11:37 am
REM @tdadobjcol.sql REM examples from Ch 5 Collections connect hr/hr REM 6.06.08 SET PAGES 200 SET LIN 140 SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED ALTER SESSION SET PLSQL_WARNINGS = 'enable:all'; SPOOL tdadobj_col.log SELECT TO_CHAR(SYSDATE, 'fmMonth DD YYYY') Execution_Date from dual; SELECT * FROM V$VERSION; REM ********** examples are grouped, first group require ex 5-1 and 5-3
This chapter explains how to create and manage these collection types: varrays and nested tables.
See Also:
Oracle Database PL/SQL Language Reference for a complete introduction to collectionsThis chapter contains these topics:
Oracle supports the varray and nested table collection data types.
A varray is an ordered collection of elements.
A nested table can have any number of elements and is unordered.
If you need to store only a fixed number of items, or loop through the elements in order, or often need to retrieve and manipulate the entire collection as a value, then use a varray.
If you need to run efficient queries on a collection, handle arbitrary numbers of elements, or perform mass insert, update, or delete operations, then use a nested table. See "Design Considerations for Collections".
This section includes the following topics:
This section shows the creation of a nested table type. Creation of a VARRAY
type is demonstrated in "Varrays".
Example 5-1 demonstrates creating a person_typ
object and a people_typ
as a nested table type of person_typ
objects, which are both used in subsequent examples in this chapter.
Example 5-1 CREATE TYPE person_typ for Subsequent Examples
-- Ex. 5-1 CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20), MAP MEMBER FUNCTION get_idno RETURN NUMBER, MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) ); / CREATE TYPE BODY person_typ AS MAP MEMBER FUNCTION get_idno RETURN NUMBER IS BEGIN RETURN idno; END; MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS BEGIN -- use the put_line procedure of the DBMS_OUTPUT package to display details DBMS_OUTPUT.put_line(TO_CHAR(idno) || ' - ' || name || ' - ' || phone); END; END; / CREATE TYPE people_typ AS TABLE OF person_typ; -- nested table type /
You create an instance of a collection type in the same way that you create an instance of any other object type, namely, by calling the constructor method of the type. The name of a constructor method is simply the name of the type. You specify the elements of the collection as a comma-delimited list of arguments to the method, for example.
( person_typ(1, 'John Smith', '1-650-555-0135'),
Calling a constructor method with an empty list creates an empty collection of that type. Note that an empty collection is an actual collection that happens to be empty; it is not the same as a null collection. See "Design Considerations for Nested Tables" for more information on using nested tables.
You can use a constructor method in a SQL statement to insert values into a nested table.
Example 5-2 first creates a table that contains an instance of the nested table type people_typ
, named people_column
, and then shows how to use the constructor method in a SQL statement to insert values into people_typ
. This example uses a literal invocation of the constructor method.
Example 5-2 Using the Constructor Method to Insert Values into a Nested Table
-- Ex. 5-2 Using the Constructor Method to Insert Values into a Nested Table -- Requires Ex. 5-1 CREATE TABLE people_tab ( group_no NUMBER, people_column people_typ ) -- an instance of nested table NESTED TABLE people_column STORE AS people_column_nt; -- storage table for NT CREATE INDEX people_idno_idx ON people_column_nt(idno); INSERT INTO people_tab VALUES ( 100, people_typ( person_typ(1, 'John Smith', '1-650-555-0135'), person_typ(2, 'Diane Smith', NULL))); SELECT c.idno FROM people_tab p, TABLE(p.people_column) c; DROP INDEX people_idno_idx; DROP TABLE people_tab;
When you declare a table column to be of an object type or collection type, you can include a DEFAULT
clause. This provides a value to use in cases where you do not explicitly specify a value for the column. The DEFAULT
clause must contain a literal invocation of the constructor method for that object or collection.
Example 5-3 shows how to use literal invocations of constructor methods to specify defaults for the person_typ
object and the people_typ
nested table:
Example 5-3 Creating the department_persons Table Using the DEFAULT Clause
-- Ex. 5-3 Creating the department_persons Table Using the DEFAULT Clause example -- requires Ex. 5-1 CREATE TABLE department_persons ( dept_no NUMBER PRIMARY KEY, dept_name CHAR(20), dept_mgr person_typ DEFAULT person_typ(10,'John Doe',NULL), dept_emps people_typ DEFAULT people_typ() ) -- instance of nested table type NESTED TABLE dept_emps STORE AS dept_emps_tab; INSERT INTO department_persons VALUES ( 101, 'Physical Sciences', person_typ(65,'Vrinda Mills', '1-650-555-0125'), people_typ( person_typ(1, 'John Smith', '1-650-555-0135'), person_typ(2, 'Diane Smith', NULL) ) ); INSERT INTO department_persons VALUES ( 104, 'Life Sciences', person_typ(70,'James Hall', '1-415-555-0101'), people_typ() ); -- an empty people_typ table
Note that people_typ()
is a literal invocation of the constructor method for an empty people_typ
nested table.
The department_persons
table can be queried in two ways as shown in Example 5-16 and Example 5-17.
A varray is an ordered set of data elements. All elements of a given varray are of the same data type or a subtype of the declared one. Each element has an index, which is a number corresponding to the position of the element in the array. The index number is used to access a specific element.
When you define a varray, you specify the maximum number of elements it can contain, although you can change this number later. The number of elements in an array is the size of the array.
The following statement creates an array type email_list_arr
that has no more than ten elements, each of data type VARCHAR2(80)
.
-- Declaring a VARRAY Data Type CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80); / DROP TYPE email_list_arr FORCE;
Example 5-4 creates a VARRAY
type that is an array of an object type. The phone_varray_typ
VARRAY
type is used as a data type for a column in the dept_phone_list
table. The INSERT
statements show how to insert values into phone_varray_typ
by invoking the constructors for the varray phone_varray_typ
and the object phone_typ
.
Example 5-4 Creating and Populating a VARRAY Data Type
-- Ex. 5-4 Creating and populating a VARRAY Data Type CREATE TYPE phone_typ AS OBJECT ( country_code VARCHAR2(2), area_code VARCHAR2(3), ph_number VARCHAR2(7)); / CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ; / CREATE TABLE dept_phone_list ( dept_no NUMBER(5), phone_list phone_varray_typ); INSERT INTO dept_phone_list VALUES ( 100, phone_varray_typ( phone_typ ('01', '650', '5550123'), phone_typ ('01', '650', '5550148'), phone_typ ('01', '650', '5550192'))); DROP TABLE dept_phone_list; DROP TYPE phone_varray_typ FORCE; DROP TYPE phone_typ FORCE;
Creating an array type, as with a SQL object type, does not allocate space. It defines a data type, which you can use as:
The data type of a column of a relational table.
An object type attribute.
The type of a PL/SQL variable, parameter, or function return value.
A varray is normally stored inline, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a BLOB
. See "Storage Considerations for Varrays".
You can create a VARRAY
type of XMLType
or LOB
type for procedural purposes, such as in PL/SQL or view queries. However, database storage for varrays of these types is not supported. Thus you cannot create an object table or an object type column of a varray type of XMLType
or LOB
type.
See Also:
Oracle Database SQL Language Reference for information and examples on theSTORE
AS
LOB
clause of the CREATE
TABLE
statementA nested table is an unordered set of data elements, all of the same data type. No maximum is specified in the definition of the table, and the order of the elements is not preserved. You select, insert, delete, and update in a nested table just as you do with ordinary tables using the TABLE
expression.
A nested table can be viewed as a single column. If the column in a nested table is an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type.
To declare nested table types, use the CREATE
TYPE
... AS
TABLE
OF
statement. For example:
CREATE TYPE people_typ AS TABLE OF person_typ;
A table type definition does not allocate space. It defines a type, which you can use as:
The data type of a column of a relational table.
An object type attribute.
A PL/SQL variable, parameter, or function return type.
Elements of a nested table are actually stored in a separate storage table.
Oracle stores nested table data in a single storage table associated with the object table for both nested table types that are columns in a relational table or attributes in an object table. The storage table contains a column that identifies the parent table row or object that each element of the nested table belongs to. See Figure 9-2, "Nested Table Storage".
The NESTED
TABLE..STORE AS
clause specifies storage names for nested tables. Storage names are used to create an index on a nested table.
Example 5-5 demonstrates creating and populating a nested table, and specifying the nested table storage using the person_typ
object and the people_typ
nested table as defined in Example 5-1.
Example 5-5 Creating and Populating Simple Nested Tables
-- Ex. 5-5 Creating and Populating Simple Nested Tables -- Requires 5-1 CREATE TABLE students ( graduation DATE, math_majors people_typ, -- nested tables (empty) chem_majors people_typ, physics_majors people_typ) NESTED TABLE math_majors STORE AS math_majors_nt -- storage tables NESTED TABLE chem_majors STORE AS chem_majors_nt NESTED TABLE physics_majors STORE AS physics_majors_nt; CREATE INDEX math_idno_idx ON math_majors_nt(idno); CREATE INDEX chem_idno_idx ON chem_majors_nt(idno); CREATE INDEX physics_idno_idx ON physics_majors_nt(idno); INSERT INTO students (graduation) VALUES ('01-JUN-03'); UPDATE students SET math_majors = people_typ (person_typ(12, 'Bob Jones', '650-555-0130'), person_typ(31, 'Sarah Chen', '415-555-0120'), person_typ(45, 'Chris Woods', '415-555-0124')), chem_majors = people_typ (person_typ(51, 'Joe Lane', '650-555-0140'), person_typ(31, 'Sarah Chen', '415-555-0120'), person_typ(52, 'Kim Patel', '650-555-0135')), physics_majors = people_typ (person_typ(12, 'Bob Jones', '650-555-0130'), person_typ(45, 'Chris Woods', '415-555-0124')) WHERE graduation = '01-JUN-03'; SELECT m.idno math_id, c.idno chem_id, p.idno physics_id FROM students s, TABLE(s.math_majors) m, TABLE(s.chem_majors) c, TABLE(s.physics_majors) p; DROP INDEX math_idno_idx; DROP INDEX chem_idno_idx; DROP INDEX physics_idno_idx;
A convenient way to access the elements of a nested table individually is to use a nested cursor or the TABLE
function. See "Querying Collections".
A nested table can be stored in a different tablespace than its parent table. In Example 5-6, the nested table is stored in the system
tablespace:
Example 5-6 Specifying a Different Tablespace for Storing a Nested Table
-- Ex. 5-6 Specifying a Different Tablespace for Storing a Nested Table
-- Requires Ex. 5-1, must remove code in Ex. 5-2 if created
CREATE TABLE people_tab (
people_column people_typ )
NESTED TABLE people_column STORE AS people_column_nt (TABLESPACE system);
If the TABLESPACE
clause is not specified, then the storage table of the nested table is created in the tablespace where the parent table is created. For multilevel nested tables, Oracle creates the child table in the same tablespace as its immediately preceding parent table.
You can issue an ALTER
TABLE..
MOVE
statement to move a table to a different tablespace. If you do this on a table with nested table columns, only the parent table moves; no action is taken on the storage tables of the nested table. To move a storage table for a nested table to a different tablespace, issue ALTER
TABLE..
MOVE
on the storage table. For example:
-- Alter table move tablespace example -- not sample schema ALTER TABLE people_tab MOVE TABLESPACE system; -- moving table ALTER TABLE people_column_nt MOVE TABLESPACE example; -- moving storage table DROP TABLE people_tab;
Now the people_tab
table is in the system
tablespace and the nested table storage is stored in the example
tablespace.
When the element type of a VARRAY
type or nested table type is a variable character, or a RAW
or numeric type, you can increase the size of the variable character or RAW
type, or increase the precision of the numeric type. A new type version is generated for the VARRAY
type or nested table type.
You make these changes using an ALTER
TYPE..MODIFY
statement, which has these options:
INVALIDATE
: Invalidates all dependent objects
CASCADE
: Propagates the change to its type and table dependents
See Also:
"ALTER TYPE Statement for Type Evolution" for further description ofINVALIDATE
and CASCADE
Example 5-7 increases the sizes of a VARRAY
and a nested table element type.
Example 5-7 Increasing the Size of an Element Type in a VARRAY and Nested Table
-- Ex. 5-7 Increasing the Size of an Element Type in a VARRAY and Nested Table CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80); / ALTER TYPE email_list_arr MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE; CREATE TYPE email_list_tab AS TABLE OF VARCHAR2(30); / ALTER TYPE email_list_tab MODIFY ELEMENT TYPE VARCHAR2(40) CASCADE;
The ALTER
TYPE
... MODIFY
LIMIT
syntax allows you to increase the number of elements of a VARRAY
type. If the number is increased, a new type version is generated for the VARRAY
type, and becomes part of the type change history.
The ALTER
TYPE
... MODIFY
LIMIT
statement has these options:
INVALIDATE
: Invalidates all dependent objects
CASCADE
: Propagates the change to its type and table dependents
Example 5-8 Increasing the VARRAY Limit Size
-- Ex. 5-8 Increasing the VARRAY Limit Size -- if you have already creating following types, drop them. DROP TYPE email_list_tab FORCE; DROP TYPE email_list_arr FORCE; CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80); / CREATE TYPE email_list_typ AS OBJECT ( section_no NUMBER, emails email_list_arr); / CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ; / ALTER TYPE email_varray_typ MODIFY LIMIT 100 INVALIDATE; DROP TYPE email_varray_typ FORCE;
When a VARRAY
type is altered, changes are propagated to the dependent tables. See "Propagating VARRAY Size Change".
To create a varray of LOB references, first define a VARRAY
type of type REF
email_list_typ
. Note: email_list_typ
was defined in Example 5-8. Next, create a table dept_email_list
and define a column email_addrs
of the array type in it.
Example 5-9 Creating a VARRAY Containing LOB References
-- Ex. 5-9 Creating a VARRAY Containing LOB References example
-- Requires Ex. 5-8
CREATE TYPE ref_email_varray_typ AS VARRAY(5) OF REF email_list_typ;
/
CREATE TABLE dept_email_list (
dept_no NUMBER,
email_addrs ref_email_varray_typ)
VARRAY email_addrs STORE AS LOB dept_emails_lob3;
DROP TABLE dept_email_list;
DROP TYPE ref_email_varray_typ FORCE;
DROP TYPE email_list_typ FORCE;
Multilevel collection types are collection types whose elements are themselves directly or indirectly another collection type. Possible multilevel collection types are:
Nested table of nested table type
Nested table of varray type
Varray of nested table type
Varray of varray type
Nested table or varray of a user-defined type that has an attribute that is a nested table or varray type
Like single-level collection types, multilevel collection types:
Can be used as columns in a relational table or with object attributes in an object table.
Require that both the source and the target be of the same declared data type for assignment.
This section contains the following topics:
A nested table type column or object table attribute requires a storage table to store rows for all its nested tables as described in "Storing Elements of Nested Tables". With a multilevel nested table collection of nested tables, you must specify nested-table storage clauses for both the inner set and the outer set of nested tables.
Example 5-10 creates the multilevel collection type nt_country_typ
, a nested table of nested tables. The example models a system of corporate regions in which each region has a nested table collection of the countries, and each country has a nested table collection of its locations. This example requires the regions
, countries
, and locations
tables of the Oracle HR
sample schema.
See Also:
Oracle Database Sample Schemas for information on using sample schemasIn Example 5-10, the SQL statements create the table region_tab
, which contains the column countries
, whose type is a multilevel collection, nt_country_typ
. This multilevel collection is a nested table of an object type that has the nested table attribute locations
. Separate nested table clauses are provided for the outer countries
nested table and for the inner locations
nested table.
Example 5-10 Multilevel Nested Table Storage
-- Ex. 5-10 Multilevel Nested Table Storage -- Requires the HR sample schema CREATE TYPE location_typ AS OBJECT ( location_id NUMBER(4), street_address VARCHAR2(40), postal_code VARCHAR2(12), city VARCHAR2(30), state_province VARCHAR2(25)); / CREATE TYPE nt_location_typ AS TABLE OF location_typ; -- nested table type / CREATE TYPE country_typ AS OBJECT ( country_id CHAR(2), country_name VARCHAR2(40), locations nt_location_typ); -- inner nested table / CREATE TYPE nt_country_typ AS TABLE OF country_typ; -- multilevel collection type / CREATE TABLE region_tab ( region_id NUMBER, region_name VARCHAR2(25), countries nt_country_typ) -- outer nested table NESTED TABLE countries STORE AS nt_countries_tab (NESTED TABLE locations STORE AS nt_locations_tab); DROP TABLE region_tab;
In Example 5-10 you can refer to the inner nested table locations
by name because this nested table is a named attribute of an object. However, if the inner nested table is not an attribute of an object, it has no name. The keyword COLUMN_VALUE
is provided for this case.
Example 5-11 shows keyword COLUMN_VALUE
used in place of a name for an inner nested table.
Example 5-11 Multilevel Nested Table Storage Using the COLUMN_VALUE Keyword
-- Ex. 5-11 Multilevel Nested Table Storage Using the COLUMN_VALUE Keyword CREATE TYPE inner_table AS TABLE OF NUMBER; / CREATE TYPE outer_table AS TABLE OF inner_table; / CREATE TABLE tab1 ( col1 NUMBER, -- inner nested table, unnamed col2 outer_table) NESTED TABLE col2 STORE AS col2_ntab (NESTED TABLE COLUMN_VALUE STORE AS cv_ntab); DROP TABLE tab1; DROP TYPE outer_table FORCE; DROP TYPE inner_table FORCE;
Example 5-12 shows how to specify physical attributes for the storage tables in the nested table clause.
Example 5-12 Specifying Physical Attributes for Nested Table Storage
-- Ex. 5-12 Specifying Physical Attributes for Nested Table Storage -- Requires Ex. 5-10 -- drop the following if you have previously created it DROP TABLE region_tab FORCE; CREATE TABLE region_tab ( region_id NUMBER, region_name VARCHAR2(25), countries nt_country_typ) NESTED TABLE countries STORE AS nt_countries_tab ( (PRIMARY KEY (NESTED_TABLE_ID, country_id)) ORGANIZATION INDEX COMPRESS NESTED TABLE locations STORE AS nt_locations_tab);
Every nested table storage table contains a column, referenceable by NESTED_TABLE_ID
, that keys rows in the storage table to the associated row in the parent table. A parent table that is itself a nested table has two system-supplied ID columns:
A system-supplied ID column that is referenceable by NESTED_TABLE_ID
, which keys its rows back to rows in its parent table.
A system-supplied ID column that is hidden and referenced by the NESTED_TABLE_ID
column in its nested table children.
In Example 5-12, nested table countries
is made an index-organized table (IOT) by adding the ORGANIZATION
INDEX
clause and assigning the nested table a primary key in which the first column is NESTED_TABLE_ID
. This column contains the ID of the row in the parent table with which a storage table row is associated. Specifying a primary key with NESTED_TABLE_ID
as the first column and index-organizing the table causes Oracle database to physically cluster all the nested table rows that belong to the same parent row, for more efficient access.
If you do not specify a primary key with a NESTED_TABLE_ID
column, then the database automatically creates a b-tree index on the NESTED_TABLE_ID
column for better performance.
Each nested table needs its own table storage clause, so you must have as many nested table storage clauses as you have levels of nested tables in a collection. See "Nested Table Storage".
Multilevel varrays are stored in one of two ways, depending on whether the varray is a varray of varrays or a varray of nested tables.
In a varray of varrays, the entire varray is stored inline in the row unless it is larger than approximately 4000 bytes or LOB
storage is explicitly specified.
In a varray of nested tables, the entire varray is stored in a LOB, with only the LOB locator stored in the row. There is no storage table associated with nested table elements of a varray.
You can explicitly specify LOB storage for varrays. The following example does this for a nested table of varray elements.
Example 5-13 shows explicit LOB
storage specified for a varray of varray type.
Example 5-13 Specifying LOB Storage for a VARRAY of VARRAY Type
-- Ex. 5-13 Specifying LOB Storage for a VARRAY Type -- Requires Ex. 5-8, drop following if created DROP TYPE email_varray_typ FORCE; CREATE TYPE email_list_typ2 AS OBJECT ( section_no NUMBER, emails email_list_arr); / CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ2; / CREATE TABLE dept_email_list2 ( dept_no NUMBER, email_addrs email_varray_typ) VARRAY email_addrs STORE AS LOB dept_emails_lob2;
See "Storage Considerations for Varrays". See also Oracle Database SecureFiles and Large Objects Developer's Guide.
Example 5-14 Specifying LOB Storage for a Nested Table of VARRAYs
-- Ex. 5-14 Specifying LOB Storage for a Nested Table of VARRAY Elements
-- drop the following types if you have created them
DROP TYPE email_list_typ FORCE;
DROP TABLE dept_email_list FORCE;
DROP TYPE email_list_arr FORCE;
CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/
CREATE TYPE email_list_typ AS TABLE OF email_list_arr;
/
CREATE TABLE dept_email_list (
dept_no NUMBER,
email_addrs email_list_typ)
NESTED TABLE email_addrs STORE AS email_addrs_nt
(
VARRAY COLUMN_VALUE STORE AS LOB
dept_emails_lob);
DROP TABLE dept_email_list2;
DROP TABLE dept_email_list;
DROP TYPE email_varray_typ FORCE;
DROP TYPE email_list_typ FORCE;
DROP TYPE email_list_typ2 FORCE;
DROP TYPE email_list_arr FORCE;
Example 5-14 shows the COLUMN_VALUE
keyword used with varrays. See Example 5-11 for discussion of this keyword and its use with nested tables.
Multilevel collection types are created by calling the constructor of the respective type, just like single-level collections and other object types. The constructor for a multilevel collection type is a system-defined function that has the same name as the type and returns a new instance of it. Constructor parameters have the names and types of the attributes of the object type.
Example 5-15 shows the constructor call for the multilevel collection type nt_country_typ
. The nt_country_typ
constructor calls the country_typ
constructor, which calls the nt_location_typ
, which calls the location_typ
constructor.
Note:
nt_country_typ
is a multilevel collection because it is a nested table that contains another nested table as an attribute.Example 5-15 Using Constructors for Multilevel Collections
-- Ex. 5-15 Using Constructors for Multilevel Collections example -- Requires 5-10 and HR sample schema INSERT INTO region_tab VALUES(1, 'Europe', nt_country_typ( country_typ( 'IT', 'Italy', nt_location_typ ( location_typ(1000, '1297 Via Cola di Rie','00989','Roma', ''), location_typ(1100, '93091 Calle della Testa','10934','Venice','') ) ), country_typ( 'CH', 'Switzerland', nt_location_typ ( location_typ(2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve'), location_typ(3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE') ) ), country_typ( 'UK', 'United Kingdom', nt_location_typ ( location_typ(2400, '8204 Arthur St', '', 'London', 'London'), location_typ(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford'), location_typ(2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester') ) ) ) );
This section describes the operations on collection data types.
This section contains the following topics:
There are two general ways to query a table that contains a collection type as a column or attribute.
Nest the collections in the result rows that contain them.
Distribute or unnest collections so that each collection element appears on a row by itself.
The following queries use the department_persons
table shown in Example 5-3. The column dept_emps
is a nested table collection of person_typ
type. The dept_emps
collection column appears in the SELECT
list like an ordinary scalar column. Querying a collection column in the SELECT
list this way nests the elements of the collection in the result row that the collection is associated with.
Example 5-16 shows the query retrieving the nested collection of employees.
Example 5-16 Nesting Results of Collection Queries
-- Ex. 5-16 Nesting Results of Collection Queries -- Requires Ex. 5-1 and Ex. 5-3 SELECT d.dept_emps FROM department_persons d;
DEPT_EMPS(IDNO, NAME, PHONE)
-------------------------------------------------------------
PEOPLE_TYP(PERSON_TYP(1, 'John Smith', '1-650-555-0135'),
PERSON_TYP(2, 'Diane Smith', '1-650-555-0135'))
The results are also nested if an object type column in the SELECT
list contains a collection attribute, even if that collection is not explicitly listed in the SELECT
list itself. For example, the query SELECT
*
FROM
department_persons
produces a nested result.
Not all tools or applications can deal with results in a nested format. To view Oracle collection data using tools that require a conventional format, you must unnest, or flatten, the collection attribute of a row into one or more relational rows. You can do this using a TABLE
expression with the collection. TABLE
expressions enable you to query a collection in the FROM
clause like a table. In effect, you join the nested table with the row that contains the nested table.
TABLE
expressions can be used to query any collection value expression, including transient values such as variables and parameters.
See Also:
Oracle Database SQL Language Reference for further information on theTABLE
expression and unnesting collectionsThe query in Example 5-17, like that of Example 5-16, retrieves the collection of employees, but here the collection is unnested.
Example 5-17 Unnesting Results of Collection Queries
-- Ex. 5- 17 Unnesting Results of Collection Queries -- Requires Ex. 5-1 and 5-3 SELECT e.* FROM department_persons d, TABLE(d.dept_emps) e;
IDNO NAME PHONE
---------- ------------------------------ ---------------
1 John Smith 1-650-555-0135
2 Diane Smith 1-650-555-0135
Example 5-17 shows that a TABLE
expression can have its own table alias. A table alias for the TABLE
expression appears in the SELECT
list to select columns returned by the TABLE
expression.
The TABLE
expression uses another table alias to specify the table that contains the collection column that the TABLE
expression references. The expression TABLE(d.dept_emps)
specifies the department_persons
table as containing the dept_emps
collection column. To reference a table column, a TABLE
expression can use the table alias of any table appearing to the left of it in a FROM
clause. This is called left correlation.
In the example, the department_persons
table is listed in the FROM
clause solely to provide a table alias for the TABLE
expression to use. No columns from the department_persons
table other than the column referenced by the TABLE
expression appear in the result.
The following example produces rows only for departments that have employees.
-- Using TABLE to retrieve rows with employees example SELECT d.dept_no, e.* FROM department_persons d, TABLE(d.dept_emps) e;
To get rows for departments with or without employees, you can use outer-join syntax:
-- Using TABLE to retrieve rows with no employees example SELECT d.dept_no, e.* FROM department_persons d, TABLE(d.dept_emps) (+) e;
The (+) indicates that the dependent join between department_persons
and e.dept_emps
should be NULL
-augmented. That is, there will be rows of department_persons
in the output for which e.dept_emps
is NULL
or empty, with NULL
values for columns corresponding to e.dept_emps
.
The examples in "Unnesting Results of Collection Queries" show a TABLE
expression that contains the name of a collection. Alternatively, a TABLE
expression can contain a subquery of a collection.
Example 5-18 returns the collection of employees whose department number is 101
.
Example 5-18 Using a Table Expression Containing a Subquery of a Collection
-- Ex. 5-18 Using a Table Expression Containing a Subquery of a Collection -- Requires Ex. 5-1 and 5-3 SELECT * FROM TABLE(SELECT d.dept_emps FROM department_persons d WHERE d.dept_no = 101);
Subqueries in a TABLE
expression have these restrictions:
The subquery must return a collection type.
The SELECT
list of the subquery must contain exactly one item.
The subquery must return only a single collection; it cannot return collections for multiple rows. For example, the subquery SELECT
dept_emps
FROM
department_persons
succeeds in a TABLE
expression only if table department_persons
contains just a single row. If the table contains more than one row, the subquery produces an error.
Example 5-19 shows a TABLE
expression used in the FROM
clause of a SELECT
embedded in a CURSOR
expression.
Unnesting queries can be also used with multilevel collections, both varrays and nested tables. Example 5-20 shows an unnesting query on a multilevel nested table collection of nested tables. From the table region_tab
where each region has a nested table of countries
and each country has a nested table of locations
, the query returns the names of all regions
, countries
, and locations
.
Example 5-20 Unnesting Queries with Multilevel Collections Using the TABLE Function
-- Ex. 5-20 Unnesting Queries with Multilvel Collections Using the TABLE Function -- Requires Ex. 5-10 and 5-15 SELECT r.region_name, c.country_name, l.location_id FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l; -- the following query is optimized to run against the locations table SELECT l.location_id, l.city FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;
The output should be as follows:
REGION_NAME COUNTRY_NAME LOCATION_ID ------------------------- ---------------------------------------- ----------- Europe Italy 1000 Europe Italy 1100 Europe Switzerland 2900 Europe Switzerland 3000 Europe United Kingdom 2400 Europe United Kingdom 2500 Europe United Kingdom 2600 7 rows selected.
LOCATION_ID CITY ----------- ------------------------------ 1000 Roma 1100 Venice 2900 Geneva 3000 Bern 2400 London 2500 Oxford 2600 Stretford 7 rows selected.
Because no columns of the base table region_tab
appear in the second SELECT
list, the query is optimized to run directly against the locations
storage table.
Outer-join syntax can also be used with queries of multilevel collections. See "Viewing Object Data in Relational Form with Unnesting Queries".
Oracle supports the following DML operations on collections:
Inserts and updates that provide a new value for the entire collection
Individual or piecewise updates of nested tables and multilevel nested tables, including inserting, deleting, and updating elements
Oracle does not support piecewise updates on VARRAY
columns. However, VARRAY
columns can be inserted into or updated as an atomic unit.This section contains these topics:
For piecewise operations on nested table columns, use the TABLE
expression.
The TABLE
expression uses a subquery to extract the nested table, so that the INSERT
, UPDATE
, or DELETE
statement applies to the nested table rather than the top-level table.
CAST
operators are also helpful. With them, you can do set operations on nested tables using SQL notation, without actually storing the nested tables in the database.
See Also:
The DML statements in Example 5-21 demonstrate piecewise operations on nested table columns.
Example 5-21 Piecewise Operations on Collections
-- Ex. 5-21 Piecewise Operations on Collections -- Requires Ex. 5-1 and 5-3 INSERT INTO TABLE(SELECT d.dept_emps FROM department_persons d WHERE d.dept_no = 101) VALUES (5, 'Kevin Taylor', '1-408-555-0199'); UPDATE TABLE(SELECT d.dept_emps FROM department_persons d WHERE d.dept_no = 101) e SET VALUE(e) = person_typ(5, 'Kevin Taylor', '1-408-555-0199') WHERE e.idno = 5; DELETE FROM TABLE(SELECT d.dept_emps FROM department_persons d WHERE d.dept_no = 101) e WHERE e.idno = 5;
Example 5-22 shows VALUE
used to return object instance rows for updating:
Example 5-22 Using VALUE to Update a Nested Table
-- Ex. 5-22 Using the VALUE Function to return objects for updating example -- Requires Ex. 5-1, 5-3 UPDATE TABLE(SELECT d.dept_emps FROM department_persons d WHERE d.dept_no = 101) p SET VALUE(p) = person_typ(2, 'Diane Smith', '1-650-555-0148') WHERE p.idno = 2;
Piecewise DML is possible only on multilevel nested tables, not on multilevel varrays. You can perform DML operation atomically on both VARRAYs and nested tables multilevel collections as described in "Collections as Atomic Data Items".
Example 5-23 shows a piecewise insert operation on the countries
nested table of nested tables. The example inserts a new country, complete with its own nested table of location_typ
:
Example 5-23 Piecewise INSERT on a Multilevel Collection
-- Ex. 5-23 Piecewise INSERT on a Multilevel Collection -- Requires Ex. 5-10 and 5-15 INSERT INTO TABLE( SELECT countries FROM region_tab r WHERE r.region_id = 2) VALUES ( 'CA', 'Canada', nt_location_typ( location_typ(1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario')));
Example 5-24 performs a piecewise insert into an inner nested table to add a location for a country. Like the preceding example, this example uses a TABLE
expression containing a subquery that selects the inner nested table to specify the target for the insert.
Example 5-24 Piecewise INSERT into an Inner Nested Table
-- Ex. 5-24 Piecewise INSERT into an Inner Nested Table -- Requires Ex. 5-10 and 5-15 INSERT INTO TABLE( SELECT c.locations FROM TABLE( SELECT r.countries FROM region_tab r WHERE r.region_id = 2) c WHERE c.country_id = 'US') VALUES (1700, '2004 Lakeview Rd', '98199', 'Seattle', 'Washington'); SELECT r.region_name, c.country_name, l.location_id FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l; DROP TABLE region_tab; DROP TYPE nt_country_typ FORCE; DROP TYPE country_typ FORCE; DROP TYPE nt_location_typ FORCE; DROP TYPE location_typ FORCE;
This section discusses atomical changes to nested tables and VARRAY
s.
Note: While nested tables can also be changed in a piecewise fashions, varrays cannot.
Example 5-25 shows how you can manipulate SQL varray object types with PL/SQL statements. In this example, varrays are transferred between PL/SQL variables and SQL tables. You can insert table rows containing collections, update a row to replace its collection, and select collections into PL/SQL variables.
However, you cannot update or delete individual varray elements directly with SQL; you have to select the varray from the table, change it in PL/SQL, then update the table to include the new varray. You can also do this with nested tables, but nested tables have the option of doing piecewise updates and deletes.
new, was ex 3-17, is integrated
Example 5-25 Using INSERT, UPDATE, DELETE, and SELECT Statements With Varrays
-- Ex. 5-25 using PL/SQL varrays with DML statements CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30); / CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var); BEGIN INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance')); INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping')); INSERT INTO depts VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales')); COMMIT; END; / DECLARE new_dnames dnames_var := dnames_var('Benefits', 'Advertising', 'Contracting', 'Executive', 'Marketing'); some_dnames dnames_var; BEGIN UPDATE depts SET dept_names = new_dnames WHERE region = 'Europe'; COMMIT; SELECT dept_names INTO some_dnames FROM depts WHERE region = 'Europe'; FOR i IN some_dnames.FIRST .. some_dnames.LAST LOOP DBMS_OUTPUT.PUT_LINE('dept_names = ' || some_dnames(i)); END LOOP; END; / DROP TABLE depts; DROP TYPE dnames_var;
The section "Constructors for Multilevel Collections" shows how to insert an entire multilevel collection with an INSERT
statement. Multilevel collections (both VARRAY
and nested tables) can also be updated atomically with an UPDATE
statement. For example, suppose v_country
is a variable declared to be of the countries
nested table type nt_country_typ
.
Example 5-26 updates region_tab
by setting the countries
collection as a unit to the value of v_country
.
Example 5-26 Using UPDATE to Insert an Entire Multilevel Collection
-- Ex. 5-26 Using UPDATE to Insert an Entire Multilevel Collection
-- Requires Ex. 5-10 and 5-15
INSERT INTO region_tab (region_id, region_name) VALUES(2, 'Americas');
DECLARE
v_country nt_country_typ;
BEGIN
v_country := nt_country_typ( country_typ(
'US', 'United States of America', nt_location_typ (
location_typ( 1500,'2011 Interiors Blvd','99236','San Francisco','California'),
location_typ(1600,'2007 Zagora St','50090','South Brunswick','New Jersey'))));
UPDATE region_tab r
SET r.countries = v_country WHERE r.region_id = 2;
END;
/
-- Invocation:
SELECT r.region_name, c.country_name, l.location_id
FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l
WHERE r.region_id = 2;
The PL/SQL BULK
COLLECT
clause is an alternative to using DML statements, which can be time consuming to process. You can return an entire result set in one operation.
In Example 5-27, BULK
COLLECT
is used with a multilevel collection that includes an object type.
comment 3-18, has been integrated
Example 5-27 Using BULK COLLECT with Collections
-- Ex. 5-27 Using BULK COLLECT with VARRAYs
-- unrelated to other examples in this chapter
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
INSERT INTO depts
VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
COMMIT;
END;
/
DECLARE
TYPE dnames_tab IS TABLE OF dnames_var;
v_depts dnames_tab;
BEGIN
SELECT dept_names BULK COLLECT INTO v_depts FROM depts;
DBMS_OUTPUT.PUT_LINE(v_depts.COUNT); -- prints 3
END;
/
DROP TABLE depts;
DROP TYPE dnames_var;
The conditions listed in this section allow comparisons of nested tables, including multilevel nested tables. There is no mechanism for comparing varrays. The SQL examples in this section use the nested tables created in Example 5-5, and contain the objects created in Example 5-1.
The equal (=) and not equal (<>) conditions determine whether the input nested tables are identical or not, returning the result as a Boolean value.
Two nested tables are equal if they have the same named type, have the same cardinality, and their elements are equal. Elements are equal depending on whether they are equal by the elements own equality definitions, except for object types which require a map method. Equality is determined in the existing order of the elements, because nested tables are unordered.
comment examples 5-28 and 5-29 below output is "no rows selected" because of the where clause.
Example 5-28 Using an Equality Comparison with Nested Tables
-- Ex. 5-28 Using an Equality Comparison with Nested Tables PROBLEM -- Requires Ex. 5-1 and 5-5 SELECT p.name FROM students, TABLE(physics_majors) p WHERE math_majors = physics_majors;
In Example 5-28, the nested tables contain person_typ
objects, which have an associated map method. See Example 5-1. Since the two nested tables in the WHERE
clause are not equal, no rows are selected.
The IN
condition checks whether or not a nested table is in a list of nested tables, returning the result as a Boolean value. NULL is returned if the nested table is a null nested table.
The SUBMULTISET
[OF]
condition checks whether or not a nested table is a subset of another nested table, returning the result as a Boolean value. The OF
keyword is optional and does not change the functionality of SUBMULTISET
.
This condition is implemented only for nested tables.
See Also:
"Multiset Operations for Nested Tables"The MEMBER
[OF]
or NOT
MEMBER
[OF]
condition tests whether or not an element is a member of a nested table, returning the result as a Boolean value. The OF
keyword is optional and has no effect on the output.
In Example 5-31, the person_typ
is an element of the same type as the elements of the nested table math_majors
.
Example 5-31 Using MEMBER OF on a Nested Table
-- Ex. 5-31 Using MEMBER OF on a Nested Table -- Requires Ex. 5-1 and 5-5 SELECT graduation FROM students WHERE person_typ(12, 'Bob Jones', '1-650-555-0130') MEMBER OF math_majors;
Example 5-32 presents an alternative approach to the MEMBER
OF
condition, which performs more efficiently for large collections.
The IS
[NOT]
EMPTY
condition checks whether a given nested table is empty or not empty, regardless of whether any of the elements are NULL. If a NULL is given for the nested table, the result is NULL. The result is returned as a Boolean value.
This section describes multiset operators for nested tables. Multiset operations are not available for varrays.
The SQL examples in this section use the nested tables created in Example 5-5 and the objects created in Example 5-1.
See Also:
"Functions and Operators Useful with Objects" for a description of additional operations
Oracle Database SQL Language Reference.for more information about using operators with nested tables
The CARDINALITY
function returns the number of elements in a nested table. The return type is NUMBER
. If the nested table is a null collection, NULL
is returned.
Example 5-35 Determining the CARDINALITY of a Nested Table
-- Ex. 5-35 Determining the CARDINALITY of a Nested Table -- Requires Ex. 5-1 and 5-5 SELECT CARDINALITY(math_majors) FROM students;
For more information about the CARDINALITY
function, see Oracle Database SQL Language Reference.
The COLLECT
function is an aggregate function which creates a multiset from a set of elements. The function takes a column of the element type as input and creates a multiset from rows selected. To get the results of this function, you must use it within a CAST
function to specify the output type of COLLECT
. See "CAST" for an example of the COLLECT
function.
For more information about the COLLECT
function, see Oracle Database SQL Language Reference.
The MULTISET
EXCEPT
operator inputs two nested tables and returns a nested table whose elements are in the first nested table but not the second. The input nested tables and the output nested table will all be of the same nested table type.
The ALL
or DISTINCT
options can be used with the operator. The default is ALL
.
With the ALL
option, for ntab1
MULTISET
EXCEPT
ALL
ntab2
, all elements in ntab1
other than those in ntab2
are part of the result. If a particular element occurs m
times in ntab1
and n
times in ntab2
, the result shows (m
- n
) occurrences of the element if m
is greater than n
, otherwise, 0
occurrences of the element.
With the DISTINCT
option, any element that is present in ntab1
and is also present in ntab2
is eliminated, irrespective of the number of occurrences.
Example 5-36 Using the MULTISET EXCEPT Operation on Nested Tables
-- Ex. 5-36 Using the MULTISET EXCEPT Operation on Nested Tables -- Requires Ex. 5-1 and 5-5 SELECT math_majors MULTISET EXCEPT physics_majors FROM students WHERE graduation = '01-JUN-03';
For more information about the MULTISET
EXCEPT
operator, see Oracle Database SQL Language Reference.
The MULTISET
INTERSECT
operator returns a nested table whose values are common to the two input nested tables. The input nested tables and the output nested table are all type name equivalent.
There are two options associated with the operator: ALL
or DISTINCT
. The default is ALL
. With the ALL
option, if a particular value occurs m
times in ntab1
and n
times in ntab2
, the result contains the element MIN
(m
, n
) times. With the DISTINCT
option, the duplicates from the result are eliminated, including duplicates of NULL
values if they exist.
Example 5-37 Using the MULTISET INTERSECT Operation on Nested Tables
-- Ex. 5-37 Using the MULTISET INTERSECT Operation on Nested Tables -- Requires Ex. 5-1 and 5-5 SELECT math_majors MULTISET INTERSECT physics_majors FROM students WHERE graduation = '01-JUN-03';
For more information about the MULTISET
INTERSECT
operator, see Oracle Database SQL Language Reference.
The MULTISET
UNION
operator returns a nested table whose values are those of the two input nested tables. The input nested tables and the output nested table are all type name equivalent.
There are two options associated with the operator: ALL
or DISTINCT
. The default is ALL
. With the ALL
option, all elements in ntab1
and ntab2
are part of the result, including all copies of NULL
s. If a particular element occurs m
times in ntab1
and n
times in ntab2
, the result contains the element (m
+ n
) times. With the DISTINCT
option, the duplicates from the result are eliminated, including duplicates of NULL
values if they exist.
Example 5-38 Using the MULTISET UNION Operation on Nested Tables
-- Ex. 5-38 Using the MULTISET UNION DISTINCT Operation on Nested Tables -- Requires Ex. 5-1 and 5-5 SELECT math_majors MULTISET UNION DISTINCT physics_majors FROM students WHERE graduation = '01-JUN-03';
PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-650-555-0130'),
PERSON_TYP(31, 'Sarah Chen', '1-415-555-0120'),
PERSON_TYP(45, 'Chris Woods', '1-408-555-0128'))
-- Ex. 5-38a Using the MULTISET UNION ALL Operation on Nested Tables -- Requires Ex. 5-1 and 5-5 SELECT math_majors MULTISET UNION ALL physics_majors FROM students WHERE graduation = '01-JUN-03';
PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-650-555-0130'),
PERSON_TYP(31, 'Sarah Chen', '1-415-555-0120'),
PERSON_TYP(45, 'Chris Woods', '1-408-555-0128'),
PERSON_TYP(12, 'Bob Jones', '1-650-555-0130'),
PERSON_TYP(45, 'Chris Woods', '1-408-555-0128'))
For more information about the MULTISET
UNION
operator, see Oracle Database SQL Language Reference.
The POWERMULTISET
function generates all non-empty submultisets from a given multiset. The input to the POWERMULTISET
function can be any expression which evaluates to a multiset. The limit on the cardinality of the multiset argument is 32.
Example 5-39 Using the POWERMULTISET Operation on Multiset
-- Ex. 5-39 Using the POWERMULTISET Operation on Multiset -- Requires Ex. 5-1 and 5-5 SELECT * FROM TABLE(POWERMULTISET( people_typ ( person_typ(12, 'Bob Jones', '1-650-555-0130'), person_typ(31, 'Sarah Chen', '1-415-555-0120'), person_typ(45, 'Chris Woods', '1-415-555-0124'))));
For more information about the POWERMULTISET
function, see Oracle Database SQL Language Reference.
The POWERMULTISET_BY_CARDINALITY
function returns all non-empty submultisets of a nested table of the specified cardinality. The output is rows of nested tables.
POWERMULTISET_BY_CARDINALITY(x, l)
is equivalent to TABLE(POWERMULTISET(x))
p
where CARDINALITY(value(p))
=
l
, where x
is a multiset and l is the specified cardinality.
The first input parameter to the POWERMULTISET_BY_CARDINALITY
can be any expression which evaluates to a nested table. The length parameter must be a positive integer, otherwise an error is returned. The limit on the cardinality of the nested table argument is 32.
Example 5-40 Using the POWERMULTISET_BY_CARDINALITY Function
-- Ex. 5-40 Using the POWERMULTISET_BY_CARDINALITY Function -- Requires Ex. 5-1 and 5-5 SELECT * FROM TABLE(POWERMULTISET_BY_CARDINALITY( people_typ ( person_typ(12, 'Bob Jones', '1-650-555-0130'), person_typ(31, 'Sarah Chen', '1-415-555-0120'), person_typ(45, 'Chris Woods', '1-415-555-0124')),2));
For more information about the POWERMULTISET_BY_CARDINALITY
function, see Oracle Database SQL Language Reference.
The SET
function converts a nested table into a set by eliminating duplicates, and returns a nested table whose elements are distinct from one another. The nested table returned is of the same named type as the input nested table.
Example 5-41 Using the SET Function on a Nested Table
-- Ex.5-41 Using the SET Function on a Nested Table -- Requires Ex. 5-1 and 5-5 SELECT SET(physics_majors) FROM students WHERE graduation = '01-JUN-03';
For more information about the SET
function, see Oracle Database SQL Language Reference.
-- clean up for example testing DROP TABLE students; DROP TABLE department_persons; PURGE RECYCLEBIN; DROP TYPE email_list_arr FORCE; DROP TYPE people_typ FORCE; DROP TYPE person_typ FORCE; SPOOL OFF COMMIT;
fyi...section moved fr chap 8
Partitioning addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Oracle extends partitioning capabilities by letting you partition tables that contain objects, REF
s, varrays, and nested tables. Varrays stored in LOB
s are equipartitioned in a way similar to LOB
s. See also Oracle Database SecureFiles and Large Objects Developer's Guide.
feature 16545 below
Janis: discuss with Drew re: markers
With Oracle Database 11g release 1 (11.1), nested tables are equipartitioned, meaning that the partitioning of storage tables associated with nested tables corresponds to that of the top level base tables. The keyword LOCAL
is the default and indicates this behavior. Previous releases stored unpartitioned nested tables with partitioned base tables. To obtain this behavior, specify the GLOBAL
keyword. To partition your existing non-partitioned nested tables, see the discussion of online redefinition in Oracle Database VLDB and Partitioning Guide.
Generally, maintenance operations are carried out on the top level (or parent table) and cascade to the associated nested tables. However, you must perform the following operations directly on the nested table partition:
Modifying default attributes, that is, changing default physical attributes of a partition.
Modifying a partition, that is, changing the physical attributes of a table partition.
Moving a partition, that is, moving contents of a table partition into another segment or another tablespace.
Renaming a partition, that is, assigning a new name to a table partition.
/ end 16545
See Also:
For further information on equipartitioningExample 5-42 partitions the purchase order table along zip codes (ToZip
), which is an attribute of the ShipToAddr
embedded column object. The LineItemList_nt
nested table illustrates storage for the partitioned nested table.
Example 5-42 Partitioning a Nested Table That Contains Objects
CREATE TYPE StockItem_objtyp AS OBJECT ( StockNo NUMBER, Price NUMBER, TaxRate NUMBER); / CREATE TYPE LineItem_objtyp AS OBJECT ( LineItemNo NUMBER, Stock_ref REF StockItem_objtyp, Quantity NUMBER, Discount NUMBER); / CREATE TYPE Address_objtyp AS OBJECT ( Street VARCHAR2(200), City VARCHAR2(200), State CHAR(2), Zip VARCHAR2(20)) / CREATE TYPE LineItemList_nt as table of LineItem_objtyp; / CREATE TYPE PurchaseOrder_ntyp AS OBJECT ( PONo NUMBER, OrderDate DATE, ShipDate DATE, OrderForm BLOB, LineItemList LineItemList_nt, ShipToAddr Address_objtyp, MAP MEMBER FUNCTION ret_value RETURN NUMBER, MEMBER FUNCTION total_value RETURN NUMBER); / CREATE TABLE PurchaseOrders_ntab of PurchaseOrder_ntyp LOB (OrderForm) store as (nocache logging) NESTED TABLE LineItemList STORE AS LineItemList_ntab PARTITION BY RANGE (ShipToAddr.zip) (PARTITION PurOrderZone1_part VALUES LESS THAN ('59999') LOB (OrderForm) store as ( storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)) NESTED TABLE LineItemList store as LineitemZone1_part( storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)), PARTITION PurOrderZone2_part VALUES LESS THAN ('79999') LOB (OrderForm) store as ( storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)) NESTED TABLE LineItemList store as LineitemZone2_part( storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)), PARTITION PurOrderZone3_part VALUES LESS THAN ('99999') LOB (OrderForm) store as ( storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)) NESTED TABLE LineItemList store as LineitemZone3_part( storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)) ) /