| Impact of table partitioning on DML/Index creation Time (merged) [message #417732] |
Tue, 11 August 2009 05:14  |
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
|
|
|
|
|
|
|
|
|
|
|
|
| Re: List Partition [message #418081 is a reply to message #417794] |
Wed, 12 August 2009 21:23  |
rleishman
Messages: 3728
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
so will have to think more on this on my own... possibly a functional solution ..