Skip to Main Content
  • Questions

  • One tablespace for each table partition?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Feng.

Asked: May 19, 2008 - 7:34 pm UTC

Last updated: May 21, 2008 - 10:23 am UTC

Version: 10.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,

Sorry if I did double posting as I couldn't find the first question under "my question".

We are building a large Internet application based on Oracle. We have over 50 tables that need to be partitioned for availability, scalability reason. The number of partitions for most of partitioned tables are 8, and rest are 16, 32 or 64 depending on size of the tables.

We are wondering whether we should put each partition into its own tablespace or not. Someone thinks it's hard to manage by doing this as there will be almost 1000 tablespaces and someone thinks sharing tablespace means less availability during media failure as one corrupted file may impact multiple objects. What would you recommend? Do you have a upper limit of number of data files per DB? BTW we are using 64 bit 10.2.0.3 on RHEL 4.

Thanks in advance.

Feng

and Tom said...

... Sorry if I did double posting as I couldn't find the first question under "my question". ...

because it is "my question" while I am answering it - it becomes "your question" when I am done :)


Tablespaces are all about managing objects.

If a single file goes offline, that might - stress MIGHT - take the tablespace with it for recovery purposes (with the advent of block level recovery way back years ago, this is not true - the level of recovery is a block and doing a block level recovery does not entail taking the tablespace offline - nor even the file itself).

So, if you use rman (and if you don't, you are really missing out big time, big big time), you have access to block level recovery.

there is some validity to the "we want more tablespaces to reduce our exposure to widespread outages". Your requirements would drive that. Can you use a partitioning scheme coupled with a tablespace management scheme that prevents wide spread outages if a single tablespace goes offline. You don't need a tablespace per partition or table to accomplish that - you might keep data that is queried together frequently in the same tablespace - so that if that tablespace is unavailable - it doesn't really matter that it took other data with it - since the other data would need to be joined to the data that failed anyway.

You would have to develop this scheme. Managing 1,000 tablespaces isn't that hard - if you have a plan, a set of rules to create things in the right place (and it is doubtful you need 1,000 tablespaces - some smaller number is probably right - each tablespace having a slice of the data from many tables)

Limits are addressed in the Server Reference Guide:
http://docs.oracle.com/cd/B19306_01/server.102/b14237/limits002.htm#i287915

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!

Feng Qu, May 20, 2008 - 1:55 pm UTC

As usual, thanks for the quick response.

Will your answer stay same if our system is a high volume OLTP system running on 6 to 8 nodes RAC?
Tom Kyte
May 20, 2008 - 3:47 pm UTC

the number of nodes has no bearing on my tablespace layout for availability in the event of media failure - I don't see a connection between the two.

The Number of Partitions?

steve, May 21, 2008 - 9:43 am UTC

I was wondering is the a limit on number of partitions?

would "insert" time suffer or "select" performance suffer if you had a 1000 partitions on a table with as many as 200 million rows.

As this would remove the need of a index on the partitioned column of the table. Plus over time all the data relating to that column would be placed within the same sub-partition/tablespace.

Tom Kyte
May 21, 2008 - 10:23 am UTC

Limits are (once again) addressed in the server reference manual:

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/limits003.htm

...
would "insert" time suffer or "select" performance suffer if you had a 1000
partitions on a table with as many as 200 million rows.

....

the answers to that are:

a) neither would suffer, both would improve
b) both would suffer
c) insert would improve, select would suffer
d) insert would suffer, select would improve
e) neither would experience any change
f) insert would not change, select would be faster
g) insert would not change, select would be slower
h) insert would suffer, select would not change
i) insert would be faster, select would not change


I can come up with an example of EACH in turn.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.