Partitioning Guidelines For Large Fact Tables
3-16
Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users
To implement support for partitioned tables in Oracle Business Analytics Data
Warehouse, you need to update the DAC metadata and manually convert the
candidates into partitioned tables in the target database.
To deploy partitioned fact tables
1.
Partition the large fact tables. For more information, see
Section 3.8.2, "Partitioning
Large Fact Tables."
2.
Configure DAC to support ETL for partitioned tables. For more information, see
Section 3.8.3, "Configuring DAC to Support Partitioned Tables."
3.8.2 Partitioning Large Fact Tables
If you have large fact tables that are affecting performance, you can maximize
performance by partitioning the fact tables as described in this section.
The procedures in this section use an example that converts the fact table W_WRKFC_
EVT_MONTH_F into a partitioned table and uses range partitioning by year.
To partition large fact tables
1.
Identify a partitioning key and decide on a partitioning interval.
Choosing the correct partitioning key is the most important factor for effective
partitioning, since it defines how many partitions will be involved in Web queries
or ETL updates. Review the following guidelines for selecting a column for a
partitioning key:
Identify eligible columns of type DATE for implementing range partitioning.
Connect to the Oracle BI Server repository and check the usage or
dependencies on each column in the logical and presentation layers.
Analyze the summarized data distribution in the target table by each potential
partitioning key candidate and data volumes per time range, month, quarter
or year.
Based on the compiled data, decide on the appropriate partitioning key and
partitioning range for your future partitioned table.
The recommended partitioning range for most implementations is a month;
however, you may want to consider implementing quarterly or yearly
partitioning ranges.
These guidelines assume the majority of incremental ETL volume data is made
up of new records, which would be stored in one of the two latest partitions.
Depending on the range granularity you chose, it is recommended that you
rebuild local indexes for the most impacted of the latest partitions, as
described below:
Monthly range. It is recommended that you maintain the two latest
partitions, that is, the current and previous partition.
Quarterly range. It is only necessary to maintain the current partition.
Yearly range. It is recommended that you maintain the current partition.
2.
Create a partitioned table.
You can pre-create a partitioned table prior to the initial load, or load data into the
regular table and then create its partitioned copy and migrate the summarized
data. If you have already completed the initial load into a regular table and then
decided to partition it, you do not need to re-run the initial load.