Home » RDBMS Server » Performance Tuning » Impact of table partitioning on DML/Index creation Time (merged)
Impact of table partitioning on DML/Index creation Time (merged) [message #417732] Tue, 11 August 2009 05:14 Go to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
Hi,

I have a huge table (business_transactions) in my datawarehouse which contains around 99M records. I have been trying to work on the performance of this table and tried to LIST partition this table Based on a column transaction_type which almost equally divides this table into 4 parts. Previously there was a bitmap index on this column which i have now removed since it does not seem to get included in any of the selects anymore.

Post partitioned i have seen significant improvement in queries that access this table, some of which now fetch results in 4-5 times faster.

BUT post partition the following problems have come up that squares down the efficiency :

1. Populating this table (using a merge stmt for around 200K records) now takes more than double the time previously.(from 3.08mins to 8.58 mins)
2. Index creation (have 9 indexes) now takes more time
3. Table anlayze (dbms_stats.gather_stats) now takes 25 mins instead of 16mins previously.

I have checked the explain plans for the 2 merge statements for before and after partition tables they are exactly the same.

IS DML / Index creation / Analyze slower on partitioned tables?

Thanks a Lot !

NITESH
Impact of table partitioning on DML/Index creation Time [message #417763 is a reply to message #417732] Tue, 11 August 2009 07:47 Go to previous messageGo to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
Hi,

IS it true that creating a List Partition on a Table cause DML operations on this table, index creation and analyze table to take longer than the time taken otherwise?


Thanks !!!
Nitesh
Re: Impact of table partitioning on DML/Index creation Time [message #417769 is a reply to message #417763] Tue, 11 August 2009 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends.
So sentence is wrong.

Regards
Michel
Re: List Partition [message #417785 is a reply to message #417732] Tue, 11 August 2009 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/417763/136107/#msg_417763

Do not cross/multi-post

In almost every case where partitioning is deployed, the fact that partitions exist, they will speed up queries or they will speed up maintenance (creation or removal) of data.
Rarely do partitions speed up both queries & maintenance.
So you need to choose which operation needs to be "fast"; while accepting the reality that the other will likely be slow.
Re: List Partition [message #417794 is a reply to message #417785] Tue, 11 August 2009 09:56 Go to previous messageGo to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
Bravo BlackSwan ... apologies for the cross post..

i loved the way you put the answer and i couldnt agree more, its always a trade off ... unfortunately my greedy Users wouldnt settle with this explanation Sad so will have to think more on this on my own... possibly a functional solution ..

Thanks anyway
Re: List Partition [message #418081 is a reply to message #417794] Wed, 12 August 2009 21:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Operations that perform full scans should not (theoretically) be greatly impacted. The slower analyze is a bit hard to explain unless perhaps it is running in parallel and thrashing your system.

Indexed access will vary depending on whether you globally or locally partition indexes, and what types of index scans you perform. Based on the slower MERGE, I suspect you have created LOCAL index partitions. A global non-partitioned index or globally partitioned index on selective indexes will help.

The best way to tell would be to get a trace of operations on both the partitioned table and the non-partitioned table.

Why did you partition it in the first place? what was slow?

Ross Leishman
Re: List Partition [message #418145 is a reply to message #418081] Thu, 13 August 2009 02:57 Go to previous messageGo to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
Quote:
Indexed access will vary depending on whether you globally or locally partition indexes, and what types of index scans you perform.


- All are global indexes. Although i was planning to make a few local indexes too, but as of now all are global.


Quote:
Why did you partition it in the first place? what was slow?


- The table is used in a Data warehouse application, new data gets refreshed every day incrementally, also once a month all the data is cleaned and re- populated. This is where the Merge/Inserts- index creation and gather_stats happen to it.

Also data from this table is then populated to other tables in different Data Marts daily - this is where the select come into picture.

My aim was to gain on the overall performance. Post partitioning im gaining as much on the selects as i am losing on the refresh Sad .
Re: Impact of table partitioning on DML/Index creation Time (merged) [message #418231 is a reply to message #417732] Thu, 13 August 2009 09:10 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Post partitioning im gaining as much on the selects as i am losing on the refresh
Which proves again that There Is No Such Thing As A Free Lunch
or you can't get something for nothing.
Previous Topic: ORA-600 and ORA-7445 error in Oracle 10g R2
Next Topic: Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL
Goto Forum:
  


Current Time: Thu Dec 08 12:40:37 CST 2016

Total time taken to generate the page: 0.08490 seconds