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 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:
CALENDARPAUDITPROJWBSPROJECTREFRDELRSRCROLEASGNMENTSPREADUDFVALUE
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 automatically directs 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
CALENDARisCLNDR_TYPE. The partition is by listCA_BASE,CA_PROJECT, andCA_RSRC. - The partition key for
PAUDITisAUSIT_TS. The partition is by rangeINTERVALof(1, 'day'). - The partition key for
PROJWBSisPROJ_NODE_FLAG. The partition is by listYorN. - The partition key for
PROJECTisORIG_PROJ_ID. The partition is by list null ordefault (not null). - The partition key for
REFERDELisDELETE_DATE. The partition is by rangeINTERVALof(1, 'day'). - The partition key for
RSRCROLEASGNMENTSPREADisROLLEDUP_RECORD. The partition is by listYorN. - The partition key for
UDFVALUEisTABLE_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'.