| Pivoting or Cross Tab Report [message #301221] |
Tue, 19 February 2008 12:13  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
All,
I know that this question has been asked a zillion times in every single forums I have visited but I still thought it is worth asking your opinions on this. First let me explain the scenario.
Structure of the summary and history table is something like this
create table summary (
id_no number,
val1 number,
val2 number,
period date );
create table history (
id_no number,
py_jan_val1 number,
py_jan_val2 number,
cy_jan_val1 number,
cy_jan_val2 number,
....
py_dec_val1 number,
py_dec_val2 number );
I forgot to mention it is a rolling period of 24 Months and py means Previous Year and cy means Current Year.
We need to populate/build a history table from the summary table and it gets built over a period (on a monthly basis). On an average summary table will contain 25+ Million records per period and this summary table is partitioned on the period. History table is list partitioned.
So between period there could be Additions (i.e) new id_no per period could be 500K, deletion per period again could be 500K and the common id_no between periods will be in millions. But in the history table I would like to retain all the id_no.
So the approach which I am following right now is having a temporary table which holds the same structure as history table. Truncate the temporary table, Populate the temporary table by doing a full outer join with the summary table, select the values for the historic periods from history table and select the values for the current period from the summary table. Then doing an exchange partition with the history table.
I hope I have explained the process clearly and it's working. But I would like to know is there any other way of doing it ?
Your thoughts on this approach will be much appreciated.
Regards
Raj
P.S : Added some clarifications about the rolling period and some descriptions about the naming conventions.
[Updated on: Tue, 19 February 2008 12:26] Report message to a moderator |
|
|
|
| Re: Pivoting or Cross Tab Report [message #301485 is a reply to message #301221] |
Wed, 20 February 2008 17:51  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Rebuilding and exchanging the table is the right approach.
The full-outer-join will be the most expensive operation on such large sources.
If you were to hash-partition the two tables on ID, then Oracle could perform the full-outer-join as a partition-wise join. This would give you much better performance.
Also make sure you are populating the table with direct-path INSERT (INSERT /*+APPEND*/), and building indexes at the end.
Ross Leishman
|
|
|
|
| Re: Pivoting or Cross Tab Report [message #301775 is a reply to message #301485] |
Thu, 21 February 2008 12:14 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Ross,
Thanks for your time, patience and suggestion. It's much appreciated. Yes we are doing the direct path insert , dropping and recreating the indexes before and after populating the table respectively. And I agree with you regarding the performance bottleneck, Oracle is spending nearly half of the time in performing the full outer join. Will try your suggestion and keep the forum posted about the outcome.
Cheers
Raj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
