Database DataWarehousing (MOSC)

MOSC Banner

A Tablespace for each partition?

edited Nov 18, 2011 4:26AM in Database DataWarehousing (MOSC) 5 commentsAnswered
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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center