Home » RDBMS Server » Performance Tuning » doubt on paritions
doubt on paritions [message #220802] Thu, 22 February 2007 03:25 Go to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Hi,
i have a table which has 12 partitions (each with 4 million rows). This table stored data for each month in a partition.
My query will mostly consist of the date.

Is it advisable to store data (data and indexes) for all partitions in one tablespace or should i store different tablespaces? Which option is good for better performance.

Thanks
Giridhar
Re: doubt on paritions [message #220807 is a reply to message #220802] Thu, 22 February 2007 04:04 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
If you are using RAID(stripe) then it (almost) doesn't matter
otherwise - split data and indexes to different tablespaces/datafiles/disks.

HTH.
Re: doubt on paritions [message #220823 is a reply to message #220807] Thu, 22 February 2007 05:03 Go to previous messageGo to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Thanks for the information.
May i know if there is any specific reason for your suggestion to
"split data and indexes to different tablespaces/datafiles/disks"?

Thanks again.
Giridhar
Re: doubt on paritions [message #220843 is a reply to message #220823] Thu, 22 February 2007 06:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just to add,
>>May i know if there is any specific reason for your suggestion to
table data and index are always accessed in serial. Not parallel. So performance wise there is no advantage in placing data/index in different tablespaces. What really matters is , placing in the different disks so that IO is distributed.
If you are using RAID then everything is same in background.
Read about it.


Re: doubt on paritions [message #220881 is a reply to message #220843] Thu, 22 February 2007 10:24 Go to previous messageGo to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Thanks Mahesh for your reply.

I also would like to know the advantages to keep the data from different partitions in different tablespaces.

Can i say

1) easy to maintain
2) Same advantage as partitioning like more availability, even if one tablespace is offline, other partitions are available
3) Easy for backup as old partitions can be made read only.

Is there anything specific to query performance or DML performance that we need to keep the data from different partitions to keep in different tablespaces.

Thanks again.

Regards,
Giridhar

Re: doubt on paritions [message #220883 is a reply to message #220881] Thu, 22 February 2007 10:29 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As you said,
it is for ease of administration,backup and archiving.
If you can use different disks (for datafiles), that should be fine (else you cannot do anything about it Smile
Previous Topic: while block is being read.....
Next Topic: db statistics after db abnormal shutdown
Goto Forum:
  


Current Time: Sun Dec 04 12:46:10 CST 2016

Total time taken to generate the page: 0.10010 seconds