| How to offline a Partitioning? [message #229252] |
Fri, 06 April 2007 04:36  |
sweeann
Messages: 7
Registered: April 2007
|
Junior Member |
|
|
Hi,
My first post, and hope to get some feedback and help from you all.
I have a problem here, I have a database running on 10gR2 EE, and a table which is >50 millions rows of records, and my batch is running very slow when it touches to this table.
I read somewhere that we can actually offline an individual partition for maintenance purposes, so I was thinking if I partition this table, and do some selective online/offline of the partitions, i.e. all partitions should be online during online time only, but during batch, I will offline some partiton to increase the performance.
Do you all think this is workable? I have yet to try it out, need to find sufficient info before trying it out.
Anyway, I have been looking around for the command to online/offline a partition, but not able to find the command else where, anyway, can help me before I start on my project?
Many thanx.
|
|
|
|
|
|
| Re: How to offline a Partitioning? [message #229259 is a reply to message #229254] |
Fri, 06 April 2007 04:54  |
sweeann
Messages: 7
Registered: April 2007
|
Junior Member |
|
|
| Michel Cadot wrote on Fri, 06 April 2007 17:47 |
You can't find such a command because it does not exist.
You can offline/online a tablespace that may underlie a partition but Oracle optimizer does not take this fact into account and your query does not run faster if the tablespace is offline or online.
Nevertheless, if your batch only works on a subset of your table, maybe partitionning it can enhance your performances (notice the "maybe").
But there may be many ways to enhance your batch.
Regards
Michel
|
I see.... no wonder I am not able to find the command to offline a partition.
Anyway, what if I offline the tablespace with those intended-to-be-offline partitions, and then perform an update statistics of that table, will the Optimizer able to have a more accurate execution plan?
Many thanx.
|
|
|
|
|
|
| Re: How to offline a Partitioning? [message #229265 is a reply to message #229263] |
Fri, 06 April 2007 05:10 |
sweeann
Messages: 7
Registered: April 2007
|
Junior Member |
|
|
| Michel Cadot wrote on Fri, 06 April 2007 18:06 | No, optimizer does not care about the tablespace state.
Regards
Michel
|
Ah... din know that.
Now, another wild idea, what if I create a view with selected range of data (i.e. 10 million) from the original >50 millions record table, and create a private synonym for the batch to call the view, do you all think this will limit the number of records my batch processes?
|
|
|
|
| Re: How to offline a Partitioning? [message #229271 is a reply to message #229265] |
Fri, 06 April 2007 06:23 |
 |
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
View is irrelevant.
View is just a query.
If you think so create a "temporary" table.
But 50M rows is not so much you surely have another problem to investigate.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
