A Tablespace for each partition?
Hi,
Just looking for a bit of advice really. We are embarking on an
excercise to partition approximately 9 of the largest tables in our database. If I can use one of our
tables as an example.....
TableA has 2.7 million records
TableA has 79 columns, one of which is CreateDate and one of which is a unique identyfier of that record (the PrimaryKey).
TableA has 12 Indexes (2 of which are CreateDate and PrimaryKey)
The PrimaryKey of TableA is referenced by 20 Foreign key constraints
My initial plan was to interval partition this table by CreateDate in periods of 1 month and to hash partition based on the PrimaryKey, but this would mean potentially 120 partitions therfore I am having second thoughts. I read that it is best to have a partition in its own tablespace and this adds another level of complexity because even with interval partitioning you have to ensure that the tablespace exists in advance, otherwise the tablespaces are used in a round-robin fashion.
TableA has 2.7 million records
TableA has 79 columns, one of which is CreateDate and one of which is a unique identyfier of that record (the PrimaryKey).
TableA has 12 Indexes (2 of which are CreateDate and PrimaryKey)
The PrimaryKey of TableA is referenced by 20 Foreign key constraints
My initial plan was to interval partition this table by CreateDate in periods of 1 month and to hash partition based on the PrimaryKey, but this would mean potentially 120 partitions therfore I am having second thoughts. I read that it is best to have a partition in its own tablespace and this adds another level of complexity because even with interval partitioning you have to ensure that the tablespace exists in advance, otherwise the tablespaces are used in a round-robin fashion.
0