My Oracle Support Banner

How to use Materialized View For W_GL_BALANCE_A for better performance (Doc ID 1491054.1)

Last updated on FEBRUARY 18, 2019

Applies to:

Business Intelligence Applications Consumer - Version [AN 1900] and later
Information in this document applies to any platform.


The tech note for perf tuning OBI apps that was release in May 2012 (BI Apps706_Perf_Tech_Note_v8.pdf, page 55) provides guidance on improving data load performance into W_GL_BALANCE_A (financial analytics using materialized View (MV) technique. There are issues with this tech note and we are looking for help to make this technique work since the W_GL_BALANCE_A table gets rebuilt as is and it takes 2.5 hours on our nightly refresh (W_GL_BALANCE_F has 160+M rows in it).

Some details on our experiences on the MV technique:
The MV is built upon 3 base tables W_GL_BALANCE_F, W_GL_ACCOUNT_D, W_GLACCT_GRPACCT_TMP and subsequently the MV log's have been built for the 3 base tables to accommodate fast refresh during the nightly incremental data load.

The problem we are encountering now is that one of the base table which is W_GLACCT_GRPACCT_TMP is being truncated and re-loaded by the task before (PLP_GLGrpAcct_Derive) which is resulting in the MV(W_GL_BALANCE_A) becoming invalid for fast-refresh.

Do you have updates notes here to help make this work? Or perhaps an alternative technique?


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.