Skip to Main Content
  • Questions

  • partitioned tables and tablespaces on ASM

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Aaron.

Asked: April 29, 2009 - 12:13 pm UTC

Last updated: April 29, 2009 - 9:47 pm UTC

Version: 11.1.0.7

Viewed 1000+ times

You Asked

Hi Tom,
I have a 2 node 11g RAC with ASM, running on an EMC Clarion SAN, Unbreakable Linux 5. One of our developers would like me to create a separate tablespace for each partition in a partitioned table. He thinks that the reading of file header information will be faster. With the way ASM works, I don't see this as a significant advantage, at least not enough to to outweigh the my overhead of managing potentially hundreds of tablepsaces. I used to do it this way back in 9i with OCFS because I still had my datafiles on seperate ocfs formatted disks, but now with ASM the point was to simplify all of that and manage the striping and rebalancing for me.
My question is this:
given the layers and functionality of SAN and ASM are there any significant advantages associated with creating each partition of a partitioned table on a separate tablespace in my architecture?

thanks!

and Tom said...

.. He thinks that the reading of file header information will be faster. ...

stop right there. That has nothing to do with anything. Tell him "no, tablespaces are not about performance like that, we use tablespaces to make our life more enjoyable, more pleasant. Tablespaces are about organizing things."

A tablespace has a one to many relationship with a "file", there is no "file header" associated with a tablespace.

ASM, UFS, NTFS, Raw partitions - all the same. It wouldn't matter in any of them.



You would only put the partitions in separate tablespaces because it makes something else you need to do EASIER.

Eg: Suppose you partition by date (monthly). Suppose after a period of time you stop writing to a partition (eg: it is an AUDIT trail, you don't write to last months partition ever again). Now, it would make sense to perhaps put a couple months of audit information into a tablespace (say 4 months - just picked a number out of the air). In five months, you have created a new tablespace and are writing into it. You can now take the old tablespace with 4 old partitions in it and make that tablespace read only and back it up for the LAST TIME ever. You would be using tablespaces to segregate read only from read write partitions in order to reduce your backup needs.

That would be a legitimate use and reasoning for multiple tablespaces.

"reading of file header information" is so far off target - so way far off target.

Rating

  (1 rating)

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

Comments

thanks, Tom

Aaron Hirsh, April 29, 2009 - 5:27 pm UTC

Tom, thanks for the response. I've always looked at the intent of tablespaces as being a logical structure to make things easier to maintain as well. I just needed a sanity check. I couldn't see any strength in his argument, if by file headers he's talking about the speed to locate data while scanning the bitmap indexes of the locally managed tablespace, that still doesn't make sense. I don't he's got a good grasp on how far removed we are from the physical storage once I present the LUNS to the OS, create ASM disks, disk groups, mount them on ASM instance,etc. thanks again for the support.
Aaron

Tom Kyte
April 29, 2009 - 9:47 pm UTC

... to locate data while scanning the bitmap
indexes of the locally managed tablespace, that still doesn't make sense. ...

correct - because we don't look at those at all during retrieval.


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.