Partitioning strategy Help

[dpafumi]

I will have several tables with 6,000,000 rows in my database. As an example, this is one of the tables:
TABLE_BIG
FIELD1_PK NUMBER(10)
FIELD2 NUMBER(10)
FIELD3 VARCHAR2(256)
CERATEDATE DATE

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?

Thanks,
Diego

[SANJAY_G]

As you said your quries will be mostly using FIELD1_PK in WHERE clause. I range partition by FIELD1_PK will be a good option.

[slimdave]

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.

[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.

[slimdave]

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.

[tamilselvan]

What kind of info will you derive from seq number?

Tamil

[marist89]

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.

[mike9]

Could you be a bit more specific about the queries executed.
If your queries are going to be over the the field FIELD1_PK do you query only 1 row at the time? If not how many?