X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Fact Table Partitioning with Oracle BI Applications

Authors:
Patrick Block, Principal Member Technical Staff, Oracle BI Apps Development
Simon Miller, Director OBIA, Oracle Customer Engineering and Advocacy Lab (CEAL team)

Support for table partitioning was introduced to the BI Applications 11.x release series starting with version 11.1.1.8.1.  Partitioning support is largely automated - define the partitions on the fact table directly in the database, and then the BI Apps Fact Table IKMs and the Oracle database does the work.  The Bitmap indexes on the fact table are automatically created as Local (rather than as the default Global) Indexes and are only rebuild the Bitmap Indexes on the partitions that have any data  changes. The combination of having local indexes which may be created in parallel on each partition and only rebuilding the required indexes, considerably reduces the overall time taken to load the fact or aggregate table.

While the documentation on the topic: BI Application Configuration Guide - Review Table Partitioning for Human Resources Analytics, uses an HR Analytics example, this feature is supported for all BI Apps Fact tables. The following blog post details how Fact Partitioning can be implemented.

Partitioning Support

The process for defining the partitions is simple.  An ODI procedure is provided in BI Apps repository that generates a template .SQL file for implementing partitions on a particular table - edit this .SQL file to reflect the desired partition strategy and execute it.  After that, everything is automatically taken care of.



Partitions can be introduced at any time - prior to an initial load or after several subsequent incremental loads.  We strongly recommend initially loading the DW tables in an unpartitioned state.  Once the tables are loaded, determine an appropriate partition strategy based on your data profile, then implement the partitions per this document.  Performing an initial load on partitioned tables can take longer than performing an initial load on unpartitioned tables - the Oracle database has to spend extra cycles creating multiple new partitions and determining which partition a record should be assigned to.  It is generally faster to perform the initial load then implement the partitions.  On partitioning, the Oracle database will assign the records to the appropriate partitions in bulk rather than on the fly during the initial load.



Once a table is partitioned, subsequent incremental loads should go much faster.  Typically a partitioning strategy should be designed such that an incremental load data is assigned to a single or a few partitions and rebuilding the bitmap indexes on just a single or just a few partitions and therefore is much faster than rebuilding across the entire data set.  This is where most of the E-LT performance improvement comes from.  Note that with an initial load, bitmap indexes are rebuilt across all partitions anyway which is a major reason why you do not see performance improvement during an initial load on partitioned tables.
The following types of partitioning are currently supported (Sub-partitioning is also supported):

Partitioning Type 

Description

Range 

Assigns rows to partitions based on column values falling within a given range.  Commonly used with dates.  For example partitioning by month or year.

Interval 

This is an extension of Range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions.

List 

Similar to partitioning by Range, except that the partition is selected based on columns matching one of a set of discrete values (the list) for each partition.
For example grouping US states into Territory partitions.

Hash 

Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning.  Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key. 

Composite: Range - Range 

Supports partitioning of 2 ranges, e.g. order_date and shipping_date.

Composite: Range - List 

Supports partitioning by range and then a list as the sup-partitioning type.

Composite: Range - Hash 

Supports partitioning by a range and then a hash as the sup-partitioning type.

Composite: Hash - (anything) 

Supports partitioning by hash and then any other partitioning type as the sub-partitioning type.

Configuring Partitioning

A utility is provided to generate a basic database script to enable partitioning for a given fact table.  This script generates a SQL file that enables Range partitioning with an Interval of 1.  A Number column must be provided in the utility as the partitioning key - if another column type needs to be partitioned, edit the generated SQL file to use a different column as the partitioning key.

1. Execute Utility to generate partition script

Execute the IMPLEMENT_DW_TABLE_PARTITIONS scenario found under Components => DW => Oracle => Implement DW Table Partitions. The utility can be run at any time. Either before the table is loaded or after. Any existing Bitmap indexes will be converted to Local indexes.

When executed, you will be prompted with the following options:

Option

Description

SCRIPT_LOCATION

The location where the script should be created if the CREATE_SCRIPT_FILE option is true.

PARTITION_KEY

Specify the column to be used as the Partition Key. As only Interval partitioning is supported by this procedure, the column datatype must be Number (though Date is supported by Oracle, this procedure does not support the Date datatype). Only a single column is supported by Interval partitioning.

TABLE_NAME

Specify the table to be partitioned.

RUN_DDL

Whether you wish to have this run immediately or not. Best to review first.

The procedure will create a file named:

‘BIA_DW_Create_Partitioned_Table_DDL_<Unique Identifer>.sql’


2. Edit SQL File
The script generated implements range partitioning with an interval of 1 on a number column type. If another partitioning strategy is desired or range partitioning with an interval larger than 1 or range partitioning using a Date column type, edit the BIA_DW_Create_Partitioned_Table_DDL_<Unique Identifer>.sql file and alter it as necessary.  Generally, the only change required is to the PARTITION clause.

The SQL generated by this procedure includes the following Partition clause:

Advanced Implementations

Monthly Interval on Date Wid column

You may wish to leverage range partitioning for W_AP_HOLDS_F on the HOLD_DATE_WID column with an interval of every month. HOLD_DATE_WID is a number column in the format YYYYMMDD.

Update the script as follows, using an interval of 100 (Date Wid columns are numbers in the format of YYYYMMDD where the hundredth position represents the month) and establish an appropriate transition point for the initial partition:

PARTITION BY RANGE (HOLD_DATE_WID) INTERVAL(100) 
  (PARTITION p0 VALUES LESS THAN (20100101))

Partitioning with Virtual Columns

If a table does not have an appropriate column for the desired partitioning but a portion of a column does have the required information, you can create a virtual column and configure this column as your partition key. For example, the W_GL_BALANCE_F.BALANCE_DT_WID is used to join to the Fiscal Calendar dimension. The key is fairly complex but a portion of this column is appropriate for partitioning. For example, this column may contain the value '201020110120000' which includes the date value '20110120' in the YYYYMMDD format.

First, add a virtual column to the fact table:

ALTER TABLE W_GL_BALANCE_F add BALANCE_DT_V 
  AS (TO_NUMBER(SUBSTR(BALANCE_DT_WID, 5, 8)));

Next, edit the partition script

PARTITION BY RANGE (BALANCE_DT_V) INTERVAL(100) 
  (PARTITION p0 VALUES LESS THAN (20100101))

Finally, execute the script on the data warehouse database with a user who has sufficient privileges.

Updateable Partition Keys

It is possible to select a column that can be updated as the partition key. This requires that row movement be enabled for the table.  Without row movement enabled, you will get the following error message if an attempt is made to update the partition key:

‘ORA-14402: updating partition key column would cause a partition change’

By default, row movement is enabled in the SQL script when the table is partitioned.  No additional steps are required to support updateable partition keys.

Join the discussion

Comments ( 2 )
  • Gergo Bacskai Friday, September 18, 2015

    Hi,

    Pls change from:

    ALTER TABLE W_GL_BALANCE_F add BALANCE_DT_V

    AS (TO_NUMBER(SUBSTR(BALANCE_DT_WID, 5, 8)));

    to:

    ALTER TABLE W_GL_BALANCE_F add BALANCE_DT_V

    AS (TO_NUMBER(SUBSTR(BALANCE_DT_WID, 4, 8)));

    as the balance_dt_wid looks like 12320150131000

    substr(4,8) = 20150131

    substr(5,8) = 01501310

    Regards

    Gergo


  • Srikanth Kasaya Friday, July 1, 2016

    Creating a partition on W_GL_BALANCE_F with partition key on BALANCE_DT_V

    gives the following error when the out of the box ETL SILOS_SIL_GLBALANCEFACT is run in incremental (BIAPPS 11.1.1.8.1)

    ODI-1228: Task SIL_GLBalanceFact.W_GL_BALANCE_F (Integration) fails on the target ORACLE connection BIAPPS_DW.

    Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "S"."BALANCE_DT_V": invalid identifier

    The code it tried to Execute

    INSERT INTO TST_DW.I$_PART_32827711_5

    (

    BALANCE_DT_V,

    partition_high_value,

    partition_name,

    partition_exists,

    partition_split,

    source_type

    )

    select

    X.partition_key

    ,case when X.partition_key < X.interval_start_number then P.high_value_number else X.high_value_number_calc end high_value_number

    ,P.partition_name

    ,(case when X.partition_key < X.interval_start_number or X.high_value_number_calc = P.high_value_number then 'Y' else 'N' end) partition_exists

    ,(case when X.partition_key >= X.interval_start_number and P.partition_name is not null and X.high_value_number_calc <> P.high_value_number then 'Y' else 'N' end) partition_split

    ,'FLOW'

    from

    (select distinct

    S.BALANCE_DT_V partition_key

    ,S.BALANCE_DT_V + to_number(T.interval) - mod(S.BALANCE_DT_V-T.interval_start_number, to_number(T.interval)) high_value_number_calc

    ,T.table_name

    ,T.interval_start_number

    from

    TST_DW.I$_32827711_5 S

    ,

    TST_DW.W_ETL_PART_TABLES

    T

    where T.table_name = 'W_GL_BALANCE_F'

    and S.IND_UPDATE IN ('I','U')

    ) X

    LEFT OUTER JOIN

    TST_DW.W_ETL_PART_TABLE_PARTS

    P

    ON (P.table_name = X.table_name and

    NVL(P.low_value_number, 0) <= X.partition_key and

    P.high_value_number > X.partition_key)

    A better solution would be to use BALANCE_DT_KEY as a Partition Key


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.