Purpose of Table Partition Improved performance and ease of data management.
Multiple Data Source Star supports multiple P6 databases as the data source.
By default, the related tables are value partitioned based on the DATASOURCE_ID. The Partitioned tables are visible by viewing \scripts\create_star_tables_part.sql and searching for tables with PARTITION.
History Data For P6 Reporting Database 3.2 you can control history interval and levels settings on the project level in P6. History data can grow quickly. For example, if you select Activity as the History Level, this will automatically set the History Interval for Activity and Resource Assignment history to Daily, which can effect the ETL process' performance.
By default, these tables are interval-partitioned based on the PERIOD_END_DATE value, then sub-partitioned by the value of the DATASOURCE_ID. Oracle recommends you allocate of separate tablespace for each partition. The 3 history tables are:
Project history:
W_PROJECT_HISTORY_F
WBS history:
W_WBS_HISTORY_F
Activity history:
W_ACTIVITY_HISTORY_F
The indexes on these tables are LOCAL indexes per each partition.
Slowly Changing Data for projects with Activity as the History Level This will automatically set the History Interval for Activity and Resource Assignment history to Daily, which can effect the ETL process' performance.By default, these tables are interval partitioned based on the effective_end_date value, then sub-partitioned by the value of the DATASOURCE_ID. The tables related to this feature end in _HD and _HF.
Number of Partitions are dynamically determined for the interval partition. For datasource partitioning, 3 are created by default. An example to add more datasource partitions is given below.
Value Partition Addition:
alter table W_ACTIVITY_D add partition P4 values (4) tablespace star_hst1;
Interval Partition Management The merging of partitions may be applicable in some environments if the record counts found in each partition are relatively small. In the INSTALL_HOME/script directory a history_partition_management.sql script is provided. This script has examples on how to merge partitions based on an upper thresholds and a function to drop empty partitions if any were created in error. This script serves as a template in partition management.