We have 4 instances RAC on 22.214.171.124 and ASM.
We have multiple tables having billions of records and need to decide in how many partition a table should be partitioned to get the optimum performance.
In the given situation I have below 2 questions.
1. Which column/s (type) are best candidate for partition?
2. Which type of partition is best?
Is there any already proved method with statistics to decide the same?
On the type, I would say best is the ones that generates new partitions automatically.
Partitions could be done by other fields, like numeric ones, but then the strategy seems to be arbitrary, because separate the data in numeric ranges, in terms of query does not help much to select the least possible number of partitions.