Hi,
We've Oracle Financial application running on 9.2.8 version databae.

There is a table called GL_BALANCES which has balance information for each
code_combination_id,currency_code, set_of_books_id, period. The structure looks like something like
this.

CODE_COMBINATION_ID NUMBER ( for ex. 1201212)
PERIOD_NAME VARCHAR2(30) ( for ex. SEP-07)
PERIOD_YEAR NUMBER ( for ex. 2007)
CURRENCY_CODE VARCHAR2(30) ( for ex. USD or EUR..)
SET_OF_BOOKS_ID NUMBER ( for ex. 1001, 1002.. )
ACTUAL_FLAG VARCHAR2(1) ( A (actual), B (budget))
PERIOD_NET_CR number ( for ex. 100.01)
PERIOD_NET_DR number ( for ex. 200.01)
BEGIN_BALANCE_CR number ( for ex. 200.00)
BEGIN_BALANCE_DR number ( for ex. 400.00)

from this table , we pivot the data in the following format
code_combination_id,period_year, currency_code,actual_flag, Jan_begin_balance_dr,
jan_begin_balance_cr,..12 month buckets..

We've 7 mil code combinations and depends on the calendar month we need to fill the monthly bucket
from Jan to current period. So when we run it this month Oct.07 , We need to look up 70 million
records( 7 million records/per month for 10 months) and do the pivot. The query takes too long.
Even though the data may be changing for current period and previous period ( oct.07 and sep.07),
we still need to fill up buckets from Jan to Oct.

I tried Materalized view ., it's not accepting FAST REFRESH mode, because of complex query. ( pivot
query has max(decode..) clause.

Any Idea to make this process faster?

Thank you in advance.

ops$tkyte%ORA10GR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA10GR2> alter table emp add constraint emp_pk primary key(empno);

Table altered.

ops$tkyte%ORA10GR2> create materialized view log on emp with rowid(hiredate,sal) including new values;

Materialized view log created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view emp_mv
  2  refresh fast
  3  as
  4  select trunc(hiredate,'mm') month, sum(sal) sum_sal, count(*)
  5    from emp
  6   group by trunc(hiredate,'mm')
  7  /

Materialized view created.