Home » RDBMS Server » Performance Tuning » Pivoting or Cross Tab Report (Unix, Oracle 10g)
Pivoting or Cross Tab Report [message #301221] Tue, 19 February 2008 12:13 Go to next message
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member

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.



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 Go to previous messageGo to next message
Messages: 3724
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 Go to previous message
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member

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.


Previous Topic: Execution time differences between environments
Next Topic: SQL getting timed out.
Goto Forum:

Current Time: Thu Oct 27 14:31:17 CDT 2016

Total time taken to generate the page: 0.06347 seconds