Home » SQL & PL/SQL » SQL & PL/SQL » partition tables
partition tables [message #410582] Mon, 29 June 2009 02:24 Go to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
Hi

My database version is
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


There are some 47 tables in our database. 25 tables are LIST partitioned on "Year" column. We have our last 10 years data in these partition tables and its growing each year.
Currently all tables (partitioned/non-partitioned) and indexes are in one tablespace with one datafile.

to improve performance of database i am considering these

1) moving non-parition tables to a seprate tablespace.
2) moving each partition of each partition table to a seprate tablespace. for this i would require to create 25 tablespaces each year.
3) moving all indexes to a seprate tablespace.

I need your expert advice on this. Will above practice would help improve performance.? Some one has better advice the please help me.

Thanks in advance.





Re: partition tables [message #410589 is a reply to message #410582] Mon, 29 June 2009 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you cannot manage them on different disks then don't bother about it (unless you plan to archive or make some partitions read-only).

Regards
Michel

[Updated on: Mon, 29 June 2009 02:50]

Report message to a moderator

Re: partition tables [message #410592 is a reply to message #410589] Mon, 29 June 2009 03:17 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
In our accounting system whenever a financial year ends, it becomes part of history. Although we have two to three queries to get data from previous years including current year, But our main concern is with the current fiscal year. And i do have a plan of managing them on different disks, Like (By creating directory 'D:\oradata\orcl\2010\' or E:\oradata\orcl\2009).

So, do you think i should go for it.? Kindly help with best possible solution.

Thanks

[Updated on: Mon, 29 June 2009 03:17]

Report message to a moderator

Re: partition tables [message #410613 is a reply to message #410592] Mon, 29 June 2009 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best solution is to spread the data among the maximum of disks you can.

Regards
Michel
Re: partition tables [message #410614 is a reply to message #410592] Mon, 29 June 2009 04:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The best possible solution would be to get a RAID array with a decent number of disks in it. Go for a simple Stripe and mirror (Raid 10, I think). Move your existing structure onto that.

Creating additional tablespaces will not enhance the performane at all - it will simply make some administrative tasks easier.
Re: partition tables [message #410619 is a reply to message #410614] Mon, 29 June 2009 05:05 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
Thanks
Re: partition tables [message #410633 is a reply to message #410582] Mon, 29 June 2009 06:54 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
The first thing that comes to mind when I read this post is, "This person is not paying for Oracle at all, much less partitioning."
Re: partition tables [message #410649 is a reply to message #410633] Mon, 29 June 2009 09:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm sure there's a perfectly respectable reason to be paying enough to have partitioning, and yet be running a publically available version of the Db with no upgrade to the latest version.

Re: partition tables [message #410740 is a reply to message #410649] Tue, 30 June 2009 01:21 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
I am considering upgrade to 10g. Are they can be managed better in 10g.?

Please help.

[Updated on: Tue, 30 June 2009 01:22]

Report message to a moderator

Re: partition tables [message #411040 is a reply to message #410740] Wed, 01 July 2009 12:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can what be managed better in 10g?
Previous Topic: oracle Interval partitioning
Next Topic: Internationalization
Goto Forum:
  


Current Time: Fri Dec 09 11:46:01 CST 2016

Total time taken to generate the page: 0.11325 seconds