| partition tables [message #410582] |
Mon, 29 June 2009 02:24  |
nasir_mughal
Messages: 122
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 #410592 is a reply to message #410589] |
Mon, 29 June 2009 03:17  |
nasir_mughal
Messages: 122
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 #410614 is a reply to message #410592] |
Mon, 29 June 2009 04:41 |
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 #410633 is a reply to message #410582] |
Mon, 29 June 2009 06:54 |
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 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
