Printable View
Hi,
I have been reading about partitioning and how it inproves performance of of queries in case of very large tables.
1. My question is how large is large enough to partition. When do you decide that the table needs partioning... when it has grown very large or when you know its gonna be very large in future.
2.what are the performance implications if you partition a small Table
3. What are the issues one will face when dealing with partioned Tables.
Thanks
1. Depends. I've partitioned tables with only 100K rows, and I've not partitioned tables with 200M rows. It really depends on how you are going to access the data.
To me, if a table has transactional data that can be grouped by date, it is the ideal candidate for partitioning.
Test your queries against a properly indexed non-partitioned table vs. a properly indexed partitioned table. Partitioning is not the silver bullet for "large" tables.
2. none.
3. Indexes are the biggest pain with partitioned tables. If you move a partition or drop a partition, some or all of your index will become UNUSABLE and you will have to rebuild.
Hi;
Partitioning is also good for table-data which is categorized in some values, like status-types for application-objects;
3. The administration of partitioned tables is a critical point.
it depends on how you partitition your table. For example if your partition-criteria grows ( date for example ) then you have add partitions from time to time.
Partitioned tables alway use more place on Disk!
if the distribution of the partition-key is not clear, then you have to create scripts to output partition-ddl scanning the taabledata thru the script.
Check if you can access data with bitmapped indexes. Combining some bitmapped indexed coulumns in a query can result fast answertime and little indexfiles.
Oracle should provid a partition-manager, to ease the use of this feature.
In spite of all these points partitioning can be the last way to access large tables fast.
Orca
A big table(More than 512M or more than 1-2 million rows ),frequently used and if it has I/O contention then I will try to do a partition.