How to use Materialized View For W_GL_BALANCE_A for better performance
Last updated on SEPTEMBER 27, 2016
Applies to:Business Intelligence Applications Consumer - Version 18.104.22.168 [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 22.214.171.124) 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?
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms