Pet -- Thanks for the question regarding "Pivot Query on heavy volume Table", version 9.2.0.8
Submitted on 5-Nov-2007 22:29 Central time zone
Last updated 7-Nov-2007 18:16
You Asked
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.
and we said...
you should be able to create a fast refresh materialized view that requires only 10 records be retrieved.
Do not create the materialized view with the pivot, just aggregate the data to the MONTH level, keeping the current structure intact.
And then query the materialized view to pivot the data.
for example, instead of querying EMP and pivoting the sum of sal by month, we can create a materialized view and then pivot that instead
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.
November 7, 2007 - 9am Central time zone
Reviewer: Pet from USA
Table is already having data which are aggregated by month.
It has 7 million records for a month , and for an entire year , it has 7*12 = 96 million records.
We need the output of 7 million rows with each monthly buckets ( 12 buckets ) as columns, filled
with corresponding month data.
For ex.
code_combination_id,
set_of_books_id,currency_code,period_year,jan_begin_balance_dr,jan_begin_balance_cr,jan_period_net_c
r,jan_period_net_dr,feb_begin_balance... repeat for 12 months
1201212,1001,USD,2007,100.00, 200.00,300.00,400.00,.....
1201213,1001,USD,2007,200.00,204.00,400.00,500.00,....
Followup November 7, 2007 - 6pm Central time zone:
Pet - you do see how contradictory this might appear to the rest of us???
Table is already having data which are aggregated by month.
If the tale is already aggregated by month - well, ummm, hrrmmmm - where do these 7 million records per month come from??? or is your report 7 million lines LONG????
if so -
a) I question the usefulness of this thing - in fact I'll go as far as to call it a TPS report squared. (If you do not understand that reference, google office space tps report)
b) I don't see how you'd expect it to be really fast - but - it should not be horribly slow either - a nice big full scan and sort aggregate, given proper pga settings, this should be doable in a reasonable amount of time.
Don't do it (the MV) then ...
November 7, 2007 - 1pm Central time zone
Reviewer: A writer
Tom was suggesting MVing the aggregation and then pivot the smaller result ... which makes sense.
"Table is already having data which are aggregated by month".
"make this faster" ... don't do it all.
Batch Process
November 7, 2007 - 1pm Central time zone
Reviewer: Pet from USA
Just to clarify.
It's a batch process to pivot the data and push it to a datamart. The process runs for 45 minutes
to pivot 96 million records to 7 million records and insert into a staging table. So having a view
may not help me.
We don't have a MV right now, I mentioned MV because I tried that option.
Any suggestion to improve the performance? Followup November 7, 2007 - 6pm Central time zone:
trace it and see where you spend the time. are you spending your time in temp, maybe larger pga settings. are you spending your time full scanning? maybe looking at making IO more efficient.
find where you spend your time and then... there you go.
maybe pivot no loger THE factor ...
November 7, 2007 - 4pm Central time zone
Reviewer: A writer
I mentioned MV because I tried that option
fast refresh MV .. it seemed you wanted the pivoted data available in real-time.
write rather than read side. Insert /*+ append */ , if applicable to your circumstances?