I will have several tables with 6,000,000 rows in my database. As an example, this is one of the tables:
Our queries are going to be over the field FIELD1_PK on the where clause and that number is generated using a sequence. That table will start with 5M rows, and will increase with 3M rows/month.
My question is the type of partition that I should use considering that if I have 2 partitions, all of The new values will go to the right side of the partition.
Should I use hash partition over that field? not partition at all?
The first question to answer is "what do you expect to gain from partitioning?"
If you are just looking for a way of load balancing across multiple devices, you canachieve that very easily, and without the complexities of partitioning, with locally managed TS's with uniform extents.
Originally posted by tamilselvan ===================================
Our queries are going to be over the field FIELD1_PK on the where clause and that number is generated using a sequence.
Seq Number is not a good candidate for partition key, because seq number does not have any attribute value. It is just used to enforce PK.
If it's used as a predicate, then there's nothing wrong with using it as the partitioning key. Assuming that there is some benefit to partitioning in this case.
I am unclear in the reason you want to partition the artificial PK. If it is for administrative reasons then it might be worthwhile. However, I don't think you will be gaining any performance.
Being the PK, most of your index hits (if not all) will not be range scans, therefore partitioning will be of little benefit. As others have mentioned, If you are looking to distribute your I/O, there are easier ways to do it.