For the purposes of this discussion, the term Collection Tables is used for the following two categories: (1) ordered collection tables inside XMLType tables or columns, and (2) nested tables inside object tables or columns.
Partitioning when using XMLType or object tables and columns follows the basic rules for partitioning. When you partition Collection Tables, Oracle Database uses the partitioning scheme of the base table. Also, Collection Tables are automatically partitioned when the base table is partitioned. DML against a partitioned nested table behaves in a similar manner to that of a reference partitioned table.
The statement in Example 4-25 creates a nested table partition. Additional examples are shown in the following section.
For an example of issuing a query against a partitioned nested table and using the EXPLAIN PLAN to improve performance, see "Collection Tables".
Oracle Database provides a LOCAL keyword to equipartition a Collection Table with a partitioned base table. This is the default behavior in this release. The default in earlier releases was not to equipartition the Collection Table with the partitioned base table. Now you must specify the GLOBAL keyword to store an unpartitioned Collection Table with a partitioned base table. See Oracle Database SQL Language Reference for more information. Also, to convert your existing nonpartitioned collection tables to partitioned, use online redefinition, as illustrated in "Evolving a Nonpartitioned Table into a Partitioned Table".
Out-of-line (OOL) table partitioning is supported. However, you cannot create two tables of the same XML schema that has out-of-line tables. This restriction means that exchange partitioning cannot be performed for schemas with OOL tables because it is not possible to have two tables of the same schema.
Example 4-25 Creating a nested table partition
CREATE TABLE print_media_part ( product_id NUMBER(6), ad_id NUMBER(6), ad_composite BLOB, ad_sourcetext CLOB, ad_finaltext CLOB, ad_fltextn NCLOB, ad_textdocs_ntab TEXTDOC_TAB, ad_photo BLOB, ad_graphic BFILE, ad_header ADHEADER_TYP) NESTED TABLE ad_textdocs_ntab STORE AS textdoc_nt PARTITION BY RANGE (product_id) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200));