Partitioning Guidelines For Large Fact Tables
Preinstallation and Deployment Requirements for Oracle BI Applications
optimize certain queries. By turning off enforcement, the database load should not
be negatively affected.
Analyze application for occurrences of highly skewed data that is indexed. Create
histogram statistics for these indexes to enable the optimizer to better perform
To increase data throughput between Oracle BI Server and Oracle, change SDU
and TDU settings in listener.ora. The default is 2 KB and can be increased to 8 KB.
On the server side, edit the listener.ora file. Under the particular SID_LIST entry,
modify SID_DESC as follows:
SID_DESC = (SDU=16384)(TDU=16384)
ORACLE_HOME = /.....)
SID_NAME = SOLAP)
Make sure the temporary tablespace has adequate space.
Set the number of log file groups to 4.
Set the size of each log file to 10 MB.
On the client side, edit the tnsnames.ora file. Modify the TNS alias by adding
SDU= and TDU= as follows:
ADDRESS = (PROTOCOL = TCP)(HOST=myhost)(PORT=1521))
3.8 Partitioning Guidelines For Large Fact Tables
This section explains how to use partitioning to maximize performance in your Oracle
BI Applications deployment. It contains the following topics:
Section 3.8.1, "Introduction to Partitioning Large Fact Tables"
Section 3.8.2, "Partitioning Large Fact Tables"
Section 3.8.3, "Configuring DAC to Support Partitioned Tables"
3.8.1 Introduction to Partitioning Large Fact Tables
Taking advantage of range and composite range-range partitioning for fact tables
reduces index and statistics maintenance time during ETL processes as well as
improves Web query performance. Because the majority of inserts and updates impact
the last partition(s), you only need to disable local indexes on a few impacted
partitions, and then rebuild disabled indexes after the load and compute statistics on
updated partitions only. Online reports and dashboards should also render results
faster, since the optimizer builds more efficient execution plans using partitioning
Large fact tables, with more than 20 million rows, can be suitable for partitioning. To
build an optimal partitioned table with reasonable data distribution, you can consider
partitioning by month, quarter, year, and so on. You can either identify and partition
target fact tables before the initial load or convert the populated tables into partitioned
objects after the full load.