OraFAQ Forum: Performance Tuning » doubt on paritions

Home » RDBMS Server » Performance Tuning » doubt on paritions

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

doubt on paritions [message #220802]

Thu, 22 February 2007 03:25

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

Report message to a moderator

Re: doubt on paritions [message #220807 is a reply to message #220802]

Thu, 22 February 2007 04:04

michael_bialik
Messages: 621
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.

Report message to a moderator

Re: doubt on paritions [message #220823 is a reply to message #220807]

Thu, 22 February 2007 05:03

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

Report message to a moderator

Re: doubt on paritions [message #220843 is a reply to message #220823]

Thu, 22 February 2007 06:25

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: doubt on paritions [message #220881 is a reply to message #220843]

Thu, 22 February 2007 10:24

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

Report message to a moderator

Re: doubt on paritions [message #220883 is a reply to message #220881]

Thu, 22 February 2007 10:29

Mahesh Rajendran
Messages: 10707
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

Report message to a moderator

Previous Topic:	while block is being read.....
Next Topic:	db statistics after db abnormal shutdown

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 10:39:57 CDT 2024