| arsavad |
Posted: Sep 18, 2007 11:00:47 AM |
Total Post: 5
Joined: May, 2007
|
I did partitioning on a table having size more than 2GB..I did a range
partitioning on month column..But it was surprising that the cost was
increased (and execution time also), when I run a query on this
partitioned table than the unpartitioned..But I read from documents that
it will improve performance..Can anyone help me with examples..This is a
little bit urgent. |
|
|
 Mohammed Taj |
| Posted: Sep 18, 2007 12:32:19 PM |  |
|
Total Post: 694
Joined: Jul, 2007
|
Hi,
this is insufficient information.
please provide more.
1. what is your oracle version
2. what is your execution plan with partitioning.
3. what is your execution plan with non partitioning.
Regards
Mohammed Taj
http://dbataj.blogspot.com
|
|
|
|
|
| Oracle Virus |
| Posted: Sep 21, 2007 08:22:59 AM | |
|
Total Post: 50
Joined: Jan, 2007
|
Hi,
Partitioning will obviously increase the performance compared to non-partitioned table. But there are some pre-requisites which you have to self-validate to decide why you are going for partition and what type of partition will help you.
What problem i found with the generic information you posted here is, you created a partition on a table and your query is not filtering based on the partition you created. so the performance is degraded / not improved.
What exactly I mean to say is, if your query is using date/month column in the where clause then only you can see performance increase. If you fire a query like "select sum(sales_value) from ft_sales where to_date(sale_date)= '01/01/2007'" you can see markable performance increase. The same way if you are firing a query like "select sum(sales_value) from ft_sales where sales_value > 50000" you will see performace degradation / no improvement.
The reason behind this is you are no where using the date column for filtration so it has to search all the partitions for searching sales_value > 50000 where as in my first query it will go to particular partition where that date range will fall in.
Any questions please revert back.
Thank You,
Aneel Kanuri.
|
|
|
|
|
| dbavinod |
| Posted: Oct 19, 2007 05:28:25 AM | |
|
Total Post: 90
Joined: Sep, 2006
|
Hello
YEs it improves perf but not in all cases
"Partitioning improves query performance". In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. this technique (called partition pruning can provide order-of-magnitude gains in performance.
so it depends on right dicision on right time for right object
Vinod
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
