I need some help. In our database I have a table which has an average rowsize of 271 (from analyzed statistics). This will contain approx 200 million rows . So the Tablesize comes around approx 57 GB.
I would like to partition this table. So to ensure good performance how many partition I should have?.. I am also not sure about the attribute on which I should partition it. But that may be I can do it on a date attribute which is also being used in the queries executed on this table but I am not sure about the distribution of data on this date. So can I go for hash partitioning of the table on this date attribute. I currently have about 13 tablespaces for data.
Do I need to have more partition than this number.i.e do I need more tablespaces?.......What kind of partitioning is advisable? I am using Oracle 18.104.22.168.0
Thanks in advance.
Partitioning strategy depends on the kinda queries you are running. Each Partitioning has its own advantages and disadvantages, you have to justify the strategy from business sense which minimise effort for the maintenance of partitions.
You said you would like to partition on Date , number of partitions depends on how you are partitioning it with date (monthly, quarterly, yearly). Also you mentioned hash partitioning, what conclusions made you to choose hash partitioning with date as partitioning column ? Read the documentation before making decision. Range would be better with date than hash partitiong, again it all depends on how you want your data to be distributed across paritions and how you are querying that data.
Thanks for the reply reddy
But the thing is thst even if I partition the table on a date.
My queries are not being done on that date attribute. So even if I am able to partition the table on that date still it would help?