You Asked

What is a good size (# of records) to partitition table to really benifit? is 200,000 too small. Thanks!

and Tom said...

It might be 100, it might be 1,000,000,000,000. There is no reason to base this decision on number of rows, or even size of table.

It all comes down to: what are you trying to do and can partitioning help.

Lets say you have a table with 10,000 records in it. There is a status code field in there - and you have only two values and it is fairly evenly distributed. You frequently run aggregation queries that return one row - and the thing in the predicate is "where status = ?". That will be a full scan - and it could be a full scan of 50% of the data if you partitioned by status.

Partitioning is a tool you might use at 100 records, you might not use at 1,000,000 records - and vice versa.

So, depending on what you do with those 200,000 records - it may be a really good idea, a really bad idea, or neither good nor bad idea. It depends.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great feedback

A reader, June 29, 2011 - 11:00 am UTC

Partitioning is not a silver bullet that works in all situations. It will not suddenly make "select .,. from ..." go faster and in some cases can make it worse (factoring in how the indexes are used, the parse factor, etc). Let partitioning be a solution to a specific problem and not a solution to a problem that doesn't exist.

I ask this question more out curiosity.

steve, June 29, 2011 - 11:52 am UTC

Is there a performace issue with having LIST partition of 100 vs 1000 vs 50000 partitions, assuming all had local indexes and the partitioned field is not in the indexes?

I worry that the internal Oracle design on how to identify a single partition out of 50000 might not be internally indexed. Basically, I have always assumed that Oracle's partition design would favour low cardinality.

June 29, 2011 - 12:25 pm UTC

in current releases (10g and above) we only load into the dictionary cache the information we need to satisfy a query at hand. So, if you are using partition elimination (the list partitioned column is always referenced in the predicate) - you should see about the same amount of work done for a 100 partition table as a 50,000 partition table.

however, if you will be accessing 500 times as many partitions with 50,000 partitions as you would with 100 partitions - then all bets are off.

so, "it depends", best to benchmark your situation.

Date column seems most favourable candidate for partitioning

Sayeed Salahuddin, June 29, 2011 - 2:18 pm UTC

I think date column with couple of years of data is the easy candidate for partitioning an OLTP table, especially when it is certain that users would very often query last couple of months of data.

Partitioning the table is to divide and conquer. Breaking big table into small tables and then querying on those tables that contain relevent info, this way the info is returned much faster.

June 29, 2011 - 3:13 pm UTC

you are making lots of assumptions.

You need to have a reason to divide and conquer before deciding how to divide.

Take an employee table - what sense would there be by dividing by date?

You need to state WHY you want to partition, what your goal is - and then and only then can you come up with a reasonable approach.