Home » RDBMS Server » Performance Tuning » Table Partition - Suggestion required
Table Partition - Suggestion required [message #228761] Wed, 04 April 2007 02:15 Go to next message
Messages: 315
Registered: June 2002
Senior Member

DB : Oracle9i Enterprise Edition Release - 64bit Production
OS : HP-Unix
Datawarehouse Environment.

I have few tables, the data is around 50GB in each table. The tables and queries are tuned to get good performance. But, I want to have it Range-Partitioned since data is growing rapidly.

We upload data-monthwise(only month-end data) i.e 30-Nov-2006,31-Dec-2006,31-Jan-2007, 28-Feb-2007, 31-Mar-2007...etc.

Actually, we need to access last six months data for reporting and last months data for running some process. I'm planning to have following workaround for good performance.

Assuming current month-end - 30-Apr-2007

Date --> Tablespace
Olderthan 6 months --> History
30-Nov-2006 --> TS5
31-Dec-2006 --> TS4
31-Jan-2007 --> TS3
28-Feb-2007 --> TS2
31-Mar-2007 --> TS1
30-Apr-2007 --> TS0

When I load 31-May-2007, May data should go to TS0, Apr Data should goto TS1... and Nov'06 Data should goto History


1. Is there any better solution than this?
2. How can I automate the data movement from one tablespace to another?

Re: Table Partition - Suggestion required [message #228772 is a reply to message #228761] Wed, 04 April 2007 02:52 Go to previous messageGo to next message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why do you want each partition in a separate tablespace?

Do you think it will perform better? It won't - benchmark it and prove it to yourself.

Valid reasons for separating partitions into different tablespaces are:
- as part of a backup strategy
- as part of an archiving strategy
- as part of a read-only tablespace strategy for historic data

If you don't have any of these requirements, put them all in the same tablespace.

If you do want separate tablespaces for backup/archive/read-only reasons, the best-practice is to name the tablespace explicitly for the time-period of data it will contain. Eg. Store the 200703 partition in the 200703 tablespace, or store 200701-200712 partitions in the 2007 tablespace.

Don't muck about with moving partitions between tablespaces; you will create a maintenance nightmare far worse than the problem you think you are solving.

Ross Leishman
Re: Table Partition - Suggestion required [message #228773 is a reply to message #228761] Wed, 04 April 2007 02:53 Go to previous message
Messages: 611
Registered: July 2006
Senior Member
How long you have to keep history?

Why don't you make it simple?

Just create LIST partition for each date.
In that case:
1. You will NOT have to move all your data each month.
2. Using WHERE clause will take care of partition pruning and access only relevant partitions anyway.
3. You just have to create/exchange new partition once a month.

Previous Topic: Procedure Cost Problem
Next Topic: hint usage.
Goto Forum:

Current Time: Thu Oct 27 20:23:31 CDT 2016

Total time taken to generate the page: 0.19513 seconds