Home » RDBMS Server » Server Administration » Partition Exchange in Hash Partitioned Table (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
Partition Exchange in Hash Partitioned Table [message #547185] Tue, 13 March 2012 05:13 Go to next message
manubatham20
Messages: 473
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

While trying partition exchange feature of Oracle with 2 hash partitioned tables, I come to know that I can't directly exchange partitions between 2 partitioned tables Sad

I have two hash partitioned tables , so to move partition data from one table to another will include-

1) Exchange from partitioned table to non-partitioned table.
2) exchange from non-partitioned table to new partitioned table.

But I am not sure in which hash partition my data will go in new partitioned table (data need to be moved has single key value on basis of which tables are partitioned), please help.


Thank you!!
Re: Partition Exchange in Hash Partitioned Table [message #547186 is a reply to message #547185] Tue, 13 March 2012 05:19 Go to previous messageGo to next message
John Watson
Messages: 4803
Registered: January 2010
Location: Global Village
Senior Member
Hash partitioning gives you a random distribution of rows between partitions. Exchanging a partition would be senseless. What do you actually want to do?
Re: Partition Exchange in Hash Partitioned Table [message #547244 is a reply to message #547186] Tue, 13 March 2012 09:31 Go to previous message
manubatham20
Messages: 473
Registered: September 2010
Location: Champaign, IL
Senior Member

It was just a question in my mind.

In actual I want to take backup of partitions of a list partitioned table for performance improvement. For that I have to convert my hash partitioned table to list partitioned table first.

My question is, moving partition of list partitioned table to backup table (emptying partition), will make the performance of select query better anyhow (select query is using locally partitioned index)?

[EDIT]
One more thing to add, if there is only one key value (on the basis of which partitioning has been done) per partition in my hash table and most of the indexes [4/6] are locally partitioned.

Thank you.

[Updated on: Tue, 13 March 2012 12:26]

Report message to a moderator

Previous Topic: about XDB service issue
Next Topic: Tns protocol adaptor error
Goto Forum:
  


Current Time: Wed Nov 26 11:32:02 CST 2014

Total time taken to generate the page: 0.06796 seconds