Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Challenge: Partitioning is a wrong idea
> mikharakiri_nosp..._at_yahoo.com wrote:
Not just rumours - I'm still finding people who have partitioned when they shouldn't have touched the technology because they were told:
"Partitioning - fantastic. Slice your tables into 1,000 partitions and your queries go like a rocket."
>> Some time ago I asked what are the benefits of partitioned table
>> compared to partitioned view. Now I ask why partition a table *at all*?
>> Is there logical difference between large and small table? Next, why
>> partition by a certain column and not the other one? What range to
>> choose? OK, monkey type DBA is not supposed to ask this kind of
>> questions, but the idea of "scientific method" popularised by Jonathan
>> and the others recently certainly legitimaze it.
>>
It's a bit like the potential benefit of IOTs really. For the right application, the inconvenience, administrative overhead and performance penalties on one type of action are far outweighed by the benefits for another -- if you can find the right application.
>> It is undeniable that partitioning concept introduces extra
>> complications. You have to be aware of many extra technicalities:
>> what is partition prunning, what is partition wise join, etc.
>>
Not to mention - how do you figure out the best way to get appropriate statistics at all levels to ensure that the optimizer doesn't go ga-ga on some queries.
>> Returning to the "scientific method" theme, a single test case can
>> settle the issue once and forever. Create large table, fill in with the
>> data, and show how much does it take to accomplish a certain
>> administrative tasks in both cases. I claim that for any administrative
>> task you suggest, I would find a way to accomplish it in reasonable
>> time with normal table. In other words: partitioning buys up
> *nothing*.
You can't say "a single test case can settle the issue" until you have a hypothesis to test. If you want to say "partitioned tables are a waste of time" I can describe a scenario and build a test case that will show that they can be a very good idea. If you want to say that "you should always partition tables over X gigabytes" I can describe a scenario and build a test case that will show that that could be a total disaster.
So to answer the question - "why partition at all ?" The plus points I would be looking for are:
The minus points I would look for are:
Is there a rationale column (set) for partitioning. Are most queries really likely to be that restrictive How complicated is the administration going to be How much difference is it going to make
to performance to fitting within the available resource limits to administrative complexity to exposure to risk
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated March 9th 2005Received on Thu Apr 07 2005 - 17:00:35 CDT
![]() |
![]() |