| Table Partition - Suggestion required [message #228761] |
Wed, 04 April 2007 02:15  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
DB : Oracle9i Enterprise Edition Release 9.2.0.7.0 - 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
Queries:
1. Is there any better solution than this?
2. How can I automate the data movement from one tablespace to another?
Brayan.
|
|
|
|
| Re: Table Partition - Suggestion required [message #228772 is a reply to message #228761] |
Wed, 04 April 2007 02:52  |
rleishman
Messages: 3728
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 |
michael_bialik
Messages: 621
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.
HTH.
Michael
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
