Partitioning Oracle or Oracle Autonomous Database Tables for P6 EPPM Schema

Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

Each partition of a table or index must have the same logical attributes (for example, column names, datatypes, and constraints); however, each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for applications that manage large volumes of data.

Table partitioning is a manual process that can be performed after you set up the P6 EPPM Database. Oracle Primavera does not provide any tools or utilities to configure table partitioning. Partitioning of an Oracle or Oracle Autonomous Database table within a schema should be performed by the Oracle DBA.

For more information about Oracle Database partitioning, refer to the Database Performance Tuning Guide and Database Administrator's Guide.

For more information about partitioning a non-partioned table, refer to How to Partition a Non-partitioned / Regular / Normal Table (Doc ID 1070693.6) on My Oracle Support.

Supported Tables for Partitioning

The following tables are supported for partitioning:

CALENDAR
PAUDIT
PROJWBS
PROJECT
REFRDEL
RSRCROLEASGNMENTSPREAD
UDFVALUE

Supported Partitioning Type

The LIST and RANGE partitioning types are supported. List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. Range partitioning enables you to specify a range of values for the partitioning key in the description of each partition. Rows with values matching the specified partition range map to the partition.

Table Partition Keys

The partitioning key is comprised of one or more columns that determine the partition where each row will be stored. Oracle and Oracle Autonomous Database automatically direct insert, update, and delete operations to the appropriate partition through the use of the partitioning key. The following list includes the partition key for each table:

The partition key for CALENDAR is CLNDR_TYPE. The partition is by list CA_BASE, CA_PROJECT, and CA_RSRC.
The partition key for PAUDIT is AUSIT_TS. The partition is by range INTERVAL of (1, 'day').
The partition key for PROJWBS is PROJ_NODE_FLAG. The partition is by list Y or N.
The partition key for PROJECT is ORIG_PROJ_ID. The partition is by list null or default (not null).
The partition key for REFERDEL is DELETE_DATE. The partition is by range INTERVAL of (1, 'day').
The partition key for RSRCROLEASGNMENTSPREAD is ROLLEDUP_RECORD. The partition is by list Y or N.
The partition key for UDFVALUE is TABLE_NAME.

TABLE_NAME is a de-normalized column and duplicates values from the UDFTYPE table. You can create a trigger to sync UDFVALUE.TABLE_NAME values with UDFTYPE.TABLE_NAME. The following script can be used to create a new trigger when partitioning UDFVALUE:

-- new trigger

CREATE OR REPLACE TRIGGER "DN_TABLE_NAME_UDFVALUE"

BEFORE INSERT OR UPDATE OF UDF_TYPE_ID ON UDFVALUE

FOR EACH ROW

DECLARE

BEGIN

select TABLE_NAME into :new.TABLE_NAME from UDFTYPE where udf_type_id = :new.udf_type_id;

end;

You can also enhance the login process and open project queries by changing the SETTINGS.SETTING_VALUE from N to Y in the SETTINGS table where SETTINGS.SETTINGS_NAME='UDFVALUE_DENORM'.

Last Published Thursday, October 3, 2024