By JeffG-Oracle on Aug 02, 2012
1) Partition columns must be a subset of the primary key columns
2) Each partition should have its own tablespace. All partitions of a partitioned object must reside in tablespaces of a single block size.
3) Each of the large tables should have their own tablespace.
4) Set the following parameters so that the Analytical Engine can find the partition on which any combination resides:
PartitionColumnItem Specifies the name of the column that partitions the data by item.
PartitionColumnLoc Specifies the name of the column that partitions the data by location.
Note: When the SALES_DATA table is not partitioned by a level column, you need to set:
update init_params_0 set value_string = column name
where pname in ('PartitionColumnItem', 'PartitionColumnLoc');
5) Compute the optimal PCTFREE, PCTUSED and INITRANS values for the tables.
6) Ensure that the Schema statistics are up to date.
7) When creating partitions, consider your main worksheet levels. Does your primary key follow the worksheet levels? Partitions should also follow your worksheet levels and primary key. If you have several worksheets that have different levels, way your options according to use.
Also, please review the following MyOracleSupport Documents:
Oracle Demantra Implementing Partitions for Performance (Doc ID 1227173.1)
Demantra Performance Overview and Recommendations High Impact Discussion Points (Doc ID 1162795.1)
Partitioned Sales_data Table But Engine Run Is Slower (Doc ID 1331482.1)