Partitioning Tables for Human Resource Analytics
The Human Resource application will benefit from table partitioning especially on larger systems where the amount of data is greater.
The main benefits of table partitioning are:
-
Faster ETL, as indexes are rebuilt only over the table partitions that have changed.
-
Faster reports, as partition pruning is a very efficient way of getting to the required data.
Optional or Mandatory
This task is optional, however by default no tables are partitioned.
Applies to
Systems where Oracle Business Analytics Warehouse is implemented on an Oracle database.
Dependencies
No dependencies.
Task
The latest recommendations for table partitioning of Human Resource tables can be found in Tech Notes in My Oracle Support. These should be reviewed before any action is taken.
There is a table partitioning utility provided in ODI which can be used to create partitioned tables. This utility can be run at any time to implement a particular partition strategy on a table. It is re-runnable and can be used to change the strategy if needed. It will backup the existing table, create the partitioned table in its place and copy in the data and indexes.
For example, to implement table partitioning on the table W_WRKFC_EVT_MONTH_F: