Start Ora 14402 updating partition

Ora 14402 updating partition

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.

SQL file for implementing partitions on a particular table - edit this .

SQL file to reflect the desired partition strategy and execute it.

create table orders 2 ( 3 order_id number not null constraint pk$orders primary key using index, 4 order_date date not null 5 ) 6 partition by range (order_date) interval (numtoyminterval(1,'month')) 7 ( 8 partition p2014_01 values less than (to_date('01-feb-2014','DD-MON-YYYY')) 9 ) 10 ; Table created.

[email protected] create table line_items 2 ( 3 order_id number not null, 4 pos number not null, 5 item_id number not null, 6 quantity number not null, 7 constraint pk$line_items primary key (order_id, pos) using index, 8 constraint fk$order_id foreign key (order_id) references orders (order_id) 9 ) 10 partition by reference (fk$order_id); Table created.

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.

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. An ODI procedure is provided in BI Apps repository that generates a template .

This script generates a SQL file that enables Range partitioning with an Interval of 1. Any existing Bitmap indexes will be converted to Local indexes.

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. Execute Utility to generate partition script Execute the IMPLEMENT_DW_TABLE_PARTITIONS scenario found under Components = Implement DW Table Partitions. When executed, you will be prompted with the following options: Specify the column to be used as the Partition Key.

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.