Home » RDBMS Server » Server Administration » How to offline a Partitioning?
How to offline a Partitioning? [message #229252] Fri, 06 April 2007 04:36 Go to next message
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 #229254 is a reply to message #229252] Fri, 06 April 2007 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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
Re: How to offline a Partitioning? [message #229259 is a reply to message #229254] Fri, 06 April 2007 04:54 Go to previous messageGo to next message
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 #229263 is a reply to message #229259] Fri, 06 April 2007 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, optimizer does not care about the tablespace state.

Regards
Michel
Re: How to offline a Partitioning? [message #229265 is a reply to message #229263] Fri, 06 April 2007 05:10 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64102
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

Previous Topic: help on 10g
Next Topic: how to shutdown heavy loaded oracle machine (urgunt)
Goto Forum:
  


Current Time: Fri Dec 02 20:53:47 CST 2016

Total time taken to generate the page: 0.51283 seconds