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.
The following tables are supported for partitioning:
PAUDIT
REFRDEL
PROJWBS
PROJECT
UDFVALUE
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.
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:
PAUDIT
is AUSIT_TS
. The partition is by range INTERVAL
of (1, 'day')
.REFERDEL
is DELETE_DATE
. The partition is by range INTERVAL
of (1, 'day')
.PROJWBS
is PROJ_NODE_FLAG
. The partition is by list Y
or N
.PROJECT
is ORIG_PROJ_ID
. The partition is by list null or default (not null)
.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'
.
Legal Notices
Copyright © 1999, 2016,
Oracle and/or its affiliates. All rights reserved.
Last Published Friday, September 30, 2016