| Purpose |
|---|
| Scope |
|---|
| Details |
|---|
| References |
|---|
Applies to:
Oracle Warehouse Builder – Version 11.2.0.3 and later
Information in this document applies to any platform.
Purpose
This document describes the new OWB 11.2.0.3 options for Partitioning:
OWB 11.2.0.3 now supports all the partitioning options of the Oracle Database 11gR2.
Scope
OWB 11.2.0.3 and higher
Details
Overview
OWB 11.2 – Partitioning options supported in Table Objects before OWB 11.2.0.3:
• Range Partitions
• Hash Partitions (referred to as Hash, Hash by Quantity in OWB)
• List Partitions
• Composite Range Partitions (referred to as Range-Hash, Range-Hash Quantity, Range-Hash and Range-List in OWB)
OWB 11.2.0.3 – Partition options added:
• System Partition (referred to as System, System by Quantity in OWB)
• Reference partition
• Range-Range Partition
• Composite Hash Partition (Hash-*)
• Composite List Partition (List-*)
OWB 11.2.0.3 – enhanced features:
• Support STORE IN clause for Range interval
• Support more properties in STORAGE clause for all the partitions
• Support table_partition_description clause for all the partitions except Hash (By Quantity)
• Support table_compression and key_compression for Hash By Quantity partition
• Support partitioning_storage_clause for Range/List/Hash subpartition and Hash partition
• Support NULL in list_values_clause for List partition/subpartition
Feature details
Partitioning User Interface
Configuration Properties
Partitions group: two new properties have been added:
- MAXSIZE: Specify the maximum size of the storage element. Use K, M, G, T, P, E to specify size. Specify UNLIMITED if you do not want to limit the disk space of the storage element.
- FLASH_CACHE: This property lets you override the automatic buffer cache policy and specify how specific schema objects are cached in flash memory. Specify KEEP if you want the schema object buffers to remain cached in the flash cache as long as the flash cache is large enough. Specify NONE to ensure that the schema object buffers are never cached in the flash cache. Specify DEFAULT if you want the schema object buffers to be written to the flash cache when they are aged out of main memory, and then be aged out of the flash cache with the standard buffer cache replacement algorithm.
Logical Properties
The following properties have been added at Table level for System and Reference partitioning. These properties are only used in OMBPlus, and are not shown in properties inspector panel:
| Property Name | Values | Description |
|---|
NON_KEY_PARTITIONING_TYPE | SYSTEM
SYSTEM BY QUANTITY
REFERENCE | If user needs to define a System or Reference patition, this property should be specified.
If this property is set, existing Partition Key(s) for other partitioning methods will be ignored even if not dropped
PARTITION_BY_REFERENCE_CONSTRAINT | (applicable constraint names)
This property is used to specify the reference constraint name for Reference partition
PARTITION_BY_SYSTEM_COUNT | (Integer, default 1) | This property is used to specify the quantity of partitions if partition type is set to System Partition by quantity
- System Partition
User Interface
Validation Errors and Warnings
Warning message if the current target location version is not 11g or later
Generated Code Example
_CREATE TABLE "SYSTEM_1"
(
"C1" NUMBER(4)
)
PARTITION BY SYSTEM
(
PARTITION "PART_1"
,
PARTITION "PART_2"
)_;
Scripting Syntax Examples
Since System and Reference partition are not defined on partition keys, user needs to specify the partition type by settting the new property of table named “NON_KEY_PARTITIONING_TYPE” to ‘SYSTEM’ or ‘REFERENCE’’. Meanwhile, user can also get the non-key partition type by retrieving this property value.
_OMBCREATE TABLE ‘SYSTEM_1’
SET PROPERTIES (NON_KEY_PARTITIONING_TYPE) VALUES (‘SYSTEM’)
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION ‘PART_1’
ADD PARTITION ‘PART_2’
OMBRETRIEVE TABLE ‘SYSTEM_1’ GET PROPERTIES (NON_KEY_PARTITIONING_TYPE)
OMBRETRIEVE TABLE ‘SYSTEM_1’ GET PARTITIONS
OMBRETRIEVE TABLE ‘SYSTEM_1’ PARTITION ‘PART_1’
GET PROPERTIES (BUSINESS_NAME)
OMBALTER TABLE ‘SYSTEM_1’
ADD PARTITION ‘PART_3’
MODIFY PARTITION ‘PART_1’ RENAME TO ‘PART_11’
DELETE PARTITION ‘PART_2’__
_OMBALTER TABLE ‘SYSTEM_1’
MODIFY PARTITION ‘PART_3’ SET PROPERTIES (BUSINESS_NAME) VALUES (‘PART_33’)
OMBALTER TABLE ‘SYSTEM_1’
MODIFY PARTITION ‘PART_3’ MOVE TO POSITION 1_
- System By Quantity Partition
User Interface
Validation Errors and Warnings
Error message if the quantity value is less than 1 or greater than 1024K-1
Warning message if the current target location version is not 11g or later
Generated Code Example
_CREATE TABLE "SYSTEM_BY_Q_1"
(
"C1" NUMBER(4)
)
PARTITION BY SYSTEM
PARTITIONS 2
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘SYSTEM_1’
SET PROPERTIES (NON_KEY_PARTITIONING_TYPE, PARTITION_BY_SYSTEM_COUNT)
VALUES (‘SYSTEM BY QUANTITY’, 2)
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4) _
- Reference Partition
User Interface
Values in constraint dropdown list are the constraints of current table.
To specify partitions, select the “Partitions” row, then click the “Add” button.
Validation Errors and Warnings
Either remove all partitions of {0} (In this case, Oracle uses the names from the parent table), or correct the number of partitions you have.
Error message if reference partition constraint is not specified.
All columns of the partitioning foreign key must be constrained NOT NULL with enabled, validated, and not deferrable constraints. Furthermore, a virtual column cannot be part of the partitioning foreign key. Correct the statement to specify a supported partitioning foreign key.
Generated Code Example
_CREATE TABLE "REFERENCE_1"
(
"C1" VARCHAR2(7) NOT NULL,
CONSTRAINT "FK_REFERENCE_1" FOREIGN KEY
("C1")
REFERENCES "REFERENCE_1_PARENT"("C1")
)
PARTITION BY REFERENCE ("FK_REFERENCE_1")
(
PARTITION "PART_1"
,
PARTITION "PART_2"
)
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE 'REFERENCE_1'
SET PROPERTIES (NON_KEY_PARTITIONING_TYPE, PARTITION_BY_REFERENCE_CONSTRAINT)
VALUES ('REFERENCE', 'FK_1')
ADD COLUMN 'C1' SET PROPERTIES (DATATYPE, LENGTH, NOT_NULL) VALUES ('VARCHAR2', 7, 'true')
ADD FOREIGN_KEY 'FK_1'
SET REF COLUMNS ('C1')
SET REF PRIMARY_KEY 'PK_1' OF TABLE 'REFERENCE_PARENT_1'
ADD PARTITION 'PART_1'
ADD PARTITION 'PART_2'_
- Range-Range Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11g or later
All the existing validation rules for Range partition will also be applied to *-Range partitions.
Generated Code Example
_CREATE TABLE "RANGE_RANGE_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY RANGE ("C1")
SUBPARTITION BY RANGE ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1 VALUES LESS THAN (200)
,
SUBPARTITION SP_DEFAULT VALUES LESS THAN (MAXVALUE)
)
(___PARTITION "P1" VALUES LESS THAN (10000)
(
SUBPARTITION P1_CP1 VALUES LESS THAN (100)
,
SUBPARTITION P1_CP2 VALUES LESS THAN (MAXVALUE)
)
,
PARTITION "PDEFAULT" VALUES LESS THAN (MAXVALUE)
)
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘RANGE_RANGE_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘RANGE’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘RANGE’)
ADD PARTITION ‘P1’ SET PROPERTIES (VALUES_LESS_THAN) VALUES (‘10000’)
ADD SUBPARTITION ‘P1_CP1’ OF PARTITION ‘P1’
SET PROPERTIES (VALUES_LESS_THAN) VALUES (‘100’)
ADD SUBPARTITION ‘P1_CP2’ OF PARTITION ‘P1’
SET PROPERTIES (VALUES_LESS_THAN) VALUES (‘MAXVALUE’)
ADD PARTITION ‘PDEFAULT’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘MAXVALUE’)
ADD TEMPLATE_SUBPARTITION ‘SP1’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘200’)
ADD TEMPLATE_SUBPARTITION ‘SP_DEFAULT’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘MAXVALUE’) _
- Hash-Range Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11gR2 or greater.
All the existing validation rules for Range partition will also be applied to *-Range partitions.
Generated Code Example (Refer to the System Partition section)
_CREATE TABLE "HASH_RANGE_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY HASH ("C1")
SUBPARTITION BY RANGE ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1 VALUES LESS THAN (200)
,
SUBPARTITION SP_DEFAULT VALUES LESS THAN (MAXVALUE)
)
(
PARTITION "P1"
,
PARTITION "P2"
)___;_
Scripting Syntax Examples
_OMBCREATE TABLE ‘HASH_RANGE_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘HASH’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘RANGE’)
ADD PARTITION ‘P1’
ADD PARTITION ‘P2’
ADD TEMPLATE_SUBPARTITION ‘SP1’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘200’)
ADD TEMPLATE_SUBPARTITION ‘SP_DEFAULT’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘MAXVALUE’) _
- Hash-Hash Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11gR2 or greater.
All the existing validation rules for Hash partition will also be applied to *- Hash partitions.
Generated Code Example
_CREATE TABLE "HASH_HASH_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY HASH ("C1")
SUBPARTITION BY HASH ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1
,
SUBPARTITION SP2
)
(
PARTITION "P1"
,
PARTITION "P2"
)___;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘HASH_HASH_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘HASH’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘HASH’)
ADD PARTITION ‘P1’
ADD PARTITION ‘P2’
ADD TEMPLATE_SUBPARTITION ‘SP1’
ADD TEMPLATE_SUBPARTITION ‘SP2 _
- Hash-Hash By Quantity Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11gR2 or greater
All the existing validation rules for Hash By Quantity partition will also be applied to *- Hash By Quantity partitions.
Generated Code Example
_CREATE TABLE "HASH_HASH_BY_Q_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY HASH ("C1")
SUBPARTITION BY HASH ("C2")
SUBPARTITIONS 2
(
PARTITION "P1"
,
PARTITION "P2"
)
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘HASH_HASH_BY_Q_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘HASH’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE, HASH_QUANTITY)
VALUES (‘HASH BY QUANTITY’, ‘2’)
ADD PARTITION ‘P1’
ADD PARTITION ‘P2_’
- Hash-List Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11gR2 or greater.
All the existing validation rules for List partition will also be applied to *- List partitions.
Generated Code Example
__CREATE TABLE "HASH_LIST_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY HASH ("C1")
SUBPARTITION BY LIST ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1 VALUES ('ABC')
,
SUBPARTITION SP_DEFAULT VALUES (DEFAULT)
)
(
PARTITION "P1"
,
PARTITION "P2"
)
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘HASH_LIST_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘HASH’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘LIST’)
ADD PARTITION ‘P1’
ADD PARTITION ‘P2’
ADD TEMPLATE_SUBPARTITION ‘SP1’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘‘‘ABC’’’)
ADD TEMPLATE_SUBPARTITION ‘SP_DEFAULT’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘DEFAULT’) _
- Hash By Quantity -Range Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11gR2 or greater.
All the existing validation rules for Range partition will also be applied to *-Range partitions.
Generated Code Example
_CREATE TABLE "HASH_BY_Q_RANGE_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY HASH ("C1")
SUBPARTITION BY RANGE ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1 VALUES LESS THAN (200)
,
SUBPARTITION SP_DEFAULT VALUES LESS THAN (MAXVALUE)
)
PARTITIONS 2
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘HASH_BY_Q_RANGE_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE, HASH_QUANTITY) VALUES
(‘HASH BY QUANTITY’, ‘2’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘RANGE’)
ADD TEMPLATE_SUBPARTITION ‘SP1’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘200’)
ADD TEMPLATE_SUBPARTITION ‘SP_DEFAULT’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘MAXVALUE’) __
_
- Hash By Quantity -Hash Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11gR2 or greater.
All the existing validation rules for Hash partition will also be applied to *-Hash partitions.
Generated Code Example
_CREATE TABLE "HASH_BY_Q_HASH_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY HASH ("C1")
SUBPARTITION BY HASH ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1
,
SUBPARTITION SP2
)
PARTITIONS 2
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘HASH_BY_Q_HASH_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE, HASH_QUANTITY)
VALUES (‘HASH BY QUANTITY’, ‘2’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘HASH’)
ADD TEMPLATE_SUBPARTITION ‘SP1’
ADD TEMPLATE_SUBPARTITION ‘SP2 _
- Hash By Quantity -Hash By Quantity Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11gR2 or greater.
Generated Code Example
_CREATE TABLE "HASH_BY_Q_HASH_BY_Q_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY HASH ("C1")
SUBPARTITION BY HASH ("C2")
SUBPARTITIONS 2
PARTITIONS 2
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘HASH_BY_Q_HASH_BY_Q_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE, HASH_QUANTITY)
VALUES (‘HASH BY QUANTITY’, ‘2’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE, HASH_QUANTITY)
VALUES (‘HASH BY QUANTITY’, ‘2’) _
- Hash By Quantity -List Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11gR2 or greater.
Generated Code Example
_CREATE TABLE "HASH_BY_Q_LIST_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY HASH ("C1")
SUBPARTITION BY LIST ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1 VALUES ('ABC')
,
SUBPARTITION SP_DEFAULT VALUES (DEFAULT)
)
PARTITIONS 2
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘HASH_BY_Q_LIST_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE, HASH_QUANTITY)
VALUES (‘HASH BY QUANTITY’, ‘2’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘LIST’)
ADD TEMPLATE_SUBPARTITION ‘SP1’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘‘‘ABC’’’)
ADD TEMPLATE_SUBPARTITION ‘SP_DEFAULT’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘DEFAULT’) _
- List-Range Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11g or later.
Generated Code Example
_CREATE TABLE "LIST_RANGE_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY LIST ("C1")
SUBPARTITION BY RANGE ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1 VALUES LESS THAN (200)
,
SUBPARTITION SP_DEFAULT VALUES LESS THAN (MAXVALUE)
)
(
PARTITION "P1" VALUES (‘ABC’)
(
SUBPARTITION P1_CP1 VALUES LESS THAN (100)
,
SUBPARTITION P1_CP2 VALUES LESS THAN (MAXVALUE)
)
,
PARTITION "PDEFAULT" VALUES (DEFAULT)
)
;_
Scripting Syntax Examples (Refer to the System Partition section)
_
O__MBCREATE TABLE ‘LIST_RANGE_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘LIST’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘RANGE’)
ADD PARTITION ‘P1’ SET PROPERTIES (VALUES_EQUAL_TO) VALUES (‘‘‘ABC’’’)
ADD SUBPARTITION ‘P1_CP1’ OF PARTITION ‘P1’
SET PROPERTIES (VALUES_LESS_THAN) VALUES (‘100’)
ADD SUBPARTITION ‘P1_CP2’ OF PARTITION ‘P1’
SET PROPERTIES (VALUES_LESS_THAN) VALUES (‘MAXVALUE’)
ADD PARTITION ‘PDEFAULT’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘DEFAULT’)
ADD TEMPLATE_SUBPARTITION ‘SP1’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘200’)
ADD TEMPLATE_SUBPARTITION ‘SP_DEFAULT’ SET PROPERTIES (VALUES_LESS_THAN) ___VALUES (‘MAXVALUE’) _
- List-Hash Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11g or later.
Generated Code Example
_CREATE TABLE "LIST_HASH_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY LIST ("C1")
SUBPARTITION BY HASH ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1
,
SUBPARTITION SP2
)
(
PARTITION "P1" VALUES (‘ABC’)
(__
__SUBPARTITION P1_CP1
,
SUBPARTITION P1_CP2
)
,
PARTITION "PDEFAULT" VALUES (DEFAULT)
)
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘LIST_HASH_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘LIST’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘HASH’)
ADD PARTITION ‘P1’ SET PROPERTIES (VALUES_EQUAL_TO) VALUES (‘‘‘ABC’’’)
ADD SUBPARTITION ‘P1_CP1’ OF PARTITION ‘P1’
ADD SUBPARTITION ‘P1_CP2’ OF PARTITION ‘P1’
ADD PARTITION ‘PDEFAULT’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘DEFAULT’)
ADD TEMPLATE_SUBPARTITION ‘SP1’
ADD TEMPLATE_SUBPARTITION ‘SP2’ _
- List- Hash By Quantity Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11g or later.
Generated Code Example
_CREATE TABLE "LIST_HASH_BY_Q_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY LIST ("C1")
SUBPARTITION BY HASH ("C2")
SUBPARTITIONS 2
(
PARTITION "P1" VALUES (‘ABC’)
(
SUBPARTITION P1_CP1
,
SUBPARTITION P1_CP2
)
,
PARTITION "PDEFAULT" VALUES (DEFAULT)
SUBPARTITIONS 4
)
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘LIST_HASH_BY_Q_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN ‘C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘LIST’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE, HASH_QUANTITY) VALUES
(‘HASH BY QUANTITY’, ‘2’)
ADD PARTITION ‘P1’ SET PROPERTIES (VALUES_EQUAL_TO) VALUES (‘‘‘ABC’’’)
ADD SUBPARTITION ‘P1_CP1’ OF PARTITION ‘P1’
ADD SUBPARTITION ‘P1_CP2’ OF PARTITION ‘P1’
ADD PARTITION ‘PDEFAULT’ SET PROPERTIES (VALUES_EQUAL_TO, HASH_QUANTITY)
VALUES (‘DEFAULT’, ‘4’) _
- List-List Partition
User Interface
Validation Errors and Warnings
Warning message if current target location version is not 11g or later.
Generated Code Example
_CREATE TABLE "LIST_LIST_1"
(
"C1" NUMBER(4),
"C2" NUMBER(4)
)
PARTITION BY LIST ("C1")
SUBPARTITION BY LIST ("C2")
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1 VALUES (‘BCD’)
,
SUBPARTITION SP_DEFAULT VALUES (DEFAULT)
)___(
PARTITION "P1" VALUES (‘ABC’)
(
SUBPARTITION P1_CP1 VALUES (‘BCD’)
,
SUBPARTITION P1_CP2 VALUES (DEFAULT)
)
,
PARTITION "PDEFAULT" VALUES (DEFAULT)
)
;_
Scripting Syntax Examples (Refer to the System Partition section)
_OMBCREATE TABLE ‘LIST_LIST_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD COLUMN 'C2’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘LIST’)
ADD SUBPARTITION_KEY ‘C2’ SET PROPERTIES (TYPE) VALUES (‘LIST’)
ADD PARTITION ‘P1’ SET PROPERTIES (VALUES_EQUAL_TO) VALUES (‘‘‘ABC’’’)
ADD SUBPARTITION ‘P1_CP1’ OF PARTITION ‘P1’
SET PROPERTIES (VALUES_EQUAL_TO) VALUES (‘BCD’)
ADD SUBPARTITION ‘P1_CP2’ OF PARTITION ‘P1’
SET PROPERTIES (VALUES_EQUAL_TO) VALUES (‘DEFAULT’)
ADD PARTITION ‘PDEFAULT’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘DEFAULT’)
ADD TEMPLATE_SUBPARTITION ‘SP1’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘BCD’)
ADD TEMPLATE_SUBPARTITION ‘SP_DEFAULT’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘DEFAULT’) _
- Enhancement – Support STORE IN clause for Range interval
The property Partition Tablespace List (PARTITION_TABLESPACE_LIST) of table is used to generate the STORE IN clause.
Generated Code
_CREATE TABLE "RANGE_INTERVAL_1"
(
"C1" NUMBER(4)
)
PARTITION BY RANGE ("C1") INTERVAL (200) STORE IN ( TBS1,TBS2 )
(
PARTITION "P1" VALUES LESS THAN (100)
,
PARTITION "PDEFAULT" VALUES LESS THAN (1000)
)
;_
Scripting Syntax Example
_OMBCREATE TABLE ‘RANGE_INTERVAL_1’
SET PROPERTIES (PARTITION_TABLESPACE_LIST) VALUES (‘TBS1,TBS2’)
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE, INTERVAL)
VALUES (‘RANGE’, ‘200’)
ADD PARTITION ‘P1’ SET PROPERTIES (VALUES_LESS_THAN) VALUES (‘100’)
ADD PARTITION ‘PDEFAULT’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘1000’) __
Validation Errors and Warnings
Warning message if current target location version is not 11g or later.
**
- Enhancement – Support more properties in STORAGE clause for all the partitions
OWB supports three new properties in the STORAGE clause.
This storage clause is shared by Table, Materialized View, Partition and some other objects.
The ENCRYPT property may only be specified during CREATE TABLESPACE.
As a result, ENCRYPT for partitions is not supported.
Generated Code
When the target database is 11g, MAXSIZE is generated as well as other properties for 10g. When the target database is 11gR2, FLASH_CACHE is generated as well as other properties for 11g.
Sample generated DDL scripts (for 11gR2):
_CREATE TABLE "STORAGE_1"
(
"C1" NUMBER(4)
)
PARTITION BY RANGE ("C1")
(
PARTITION "P1" VALUES LESS THAN (100)
STORAGE( MAXSIZE UNLIMITED FLASH_CACHE KEEP)
,
PARTITION "PDEFAULT" VALUES LESS THAN (MAXVALUE)
)
;_
Scripting Syntax Example
_OMBCREATE TABLE ‘STORAGE_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION) VALUES (‘NUMBER’, 4)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘RANGE’)
ADD PARTITION ‘P1’ SET PROPERTIES
(VALUES_LESS_THAN, MAXSIZE, FLASH_CACHE)
VALUES (‘100’, ‘UNLIMITED’, ‘KEEP’)
ADD PARTITION ‘PDEFAULT’ SET PROPERTIES (VALUES_LESS_THAN)
VALUES (‘MAXVALUE’)_
- Enhancement – Support table_partition_description clause for all the partitions except Hash (By Quantity)
The table_partition_description defines the physical and storage characteristics of a partition.
- Enhancement –Support table_compression and key_compression for Hash By Quantity partition
Table_compression and key_compression clauses are used by both table and partition and have been added to the Hash By Quantity partition scripts generation.
Additional information is available in Note 1348013.1: OWB 11.2.0.3 New Feature – Table Compression
- Enhancement –Support partitioning_storage_clause for Range/List/Hash subpartition and Hash partition
The following warning message is received if the current target location version is not 11g or later:
**
- Enhancement –Support NULL in list_values_clause for List partition/subpartition
It is not allowed to set list partition value to NULL in OWB 11.2.0.2 or previous versions. i.e. a VLD-9015 error is generated if one of the list partitions has NULL value:
In OWB 11.2.0.3, NULL is supported as list partition value.
Generated Code Example
_CREATE TABLE "LIST_NULL_1"
(
"C1" VARCHAR2(7)
)
PARTITION BY LIST ("C1")
(
PARTITION "P1" VALUES (NULL)
,
PARTITION "PDEFAULT" VALUES (DEFAULT)
)
;_
Scripting Syntax Example
_OMBCREATE TABLE ‘LIST_NULL_1’
ADD COLUMN ‘C1’ SET PROPERTIES (DATATYPE, PRECISION)
VALUES (‘VARCHAR’, 7)
ADD PARTITION_KEY ‘C1’ SET PROPERTIES (TYPE) VALUES (‘LIST’)
ADD PARTITION ‘P1’ SET PROPERTIES
(VALUES_EQUAL_TO) VALUES (‘NULL’)
ADD PARTITION ‘PDEFAULT’ SET PROPERTIES (VALUES_EQUAL_TO)
VALUES (‘DEFAULT’)_
Compatibility and Migration
- If accessing the 11.2.0.3 Repository with a 11.2.0.2 client:
- Extra non-translated properties might be shown in the configuration panel (for instance 8i.PARTITIONS.FLASH_CACHE)
- Newly added properties in 11.2.0.3 can also be set/get in the 11.2.0.2 client, but these properties will not be used during validation/generation.
- Impact on the Upgrade deployment:
The Change Management pack does not support partition upgrade. >
However OWB supports to upgrade the following physical properties of Range partition for Partition Exchange Loading feature:>
PCTFREE, PCTUSED, INITRANS, MAXTRANS, COMPRESS, NOCOMPRESS, TABLESPACE, LOGGING, NOLOGGING, ROWDEPENDENCIES, NOROWDEPENDENCIES, CACHE, NOCACHE, MONITORING, NOMONITORING,>
STORAGE(INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, PCTINCREASE, FREELISTS, FREELIST GROUPS, BUFFER_POOL).>
>
Potential validation warning message :>
>
>