Re: Challenge: Partitioning is a wrong idea

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 7 Apr 2005 22:00:35 +0000 (UTC)
Message-ID: <d34ai2$2ak$1@hercules.btinternet.com>

> mikharakiri_nosp..._at_yahoo.com wrote:

>> I'm returning to this topic because of rumors that "interval
>> partitiong" would be the next new shiny administrative toy.
>>

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:

Free precision on queries - example: you partition by day, so each partition represents a date. If you know that your queries are ALWAYS going to be partitioning_date between {X} and {Y} and {other conditions} If you have several b-tree indexes to meet the other conditions - where would you put the date column in those indexes to minimise I/O the resource usage to address the queries. With date-based partitioning, none of those indexes needs the date - which is likely to be a huge space saving - and the problem of column order in the indexes goes away.
Benefits of read-only tablespaces - reduction in requirements for backup. Again relevant for the date based partitioning. As a recent fringe benefit, read-only partitions could first be compressed independently of the rest of the data for maximum space saving - trading CPU for I/O costs when querying.
Bulk loading of data - if you have to insert (or bulk update, or bulk delete) then Oracle's readconsistency mechanisms mean your changes have to be rolled back if other users can see the same data blocks at the same time. Partitioning allows you to do the DML out of sight, then swap the updated data into place with a reduced impact on other users.

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 2005

Received on Thu Apr 07 2005 - 17:00:35 CDT