Home » SQL & PL/SQL » SQL & PL/SQL » Question on Exchange partition (10g)
Question on Exchange partition [message #346437] Mon, 08 September 2008 10:09 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
I've look onto exchanging partition which just accepts a table as the source of records, I have this scenario, please advise for any workarounds or if this is possible:

I have table A (partitioned) then I need to copy a partition from table B (partitioned). Is the exchange partition option valid here? Is there like 'alter table a exchange parition a_p1 with TABLE b PARTITION b_p1...'. My boss just ask me if that's possible and if that will be faster than inserting each record.

Thank you very much.
Re: Question on Exchange partition [message #346438 is a reply to message #346437] Mon, 08 September 2008 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


What happened when you tried to do so?

Are you incapable or unwilling to Read Then Fine SQL Reference Manual?
Re: Question on Exchange partition [message #346440 is a reply to message #346437] Mon, 08 September 2008 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If exchange partition is possible it is far much faster than inserting rows.

Regards
Michel
Re: Question on Exchange partition [message #346473 is a reply to message #346440] Mon, 08 September 2008 12:24 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
@Michel,
Yes it is, but based on the docs I've read it requires a TABLE as a source. And also it deletes the contents of the source table. thanks, I'll further check any other workaround.
Re: Question on Exchange partition [message #346479 is a reply to message #346473] Mon, 08 September 2008 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the principle of exchanging.
It does not delete any content, it exchanges them.

There is no row movement, the segments themselves are exchanged. This is only updates in the dictionary.

Regards
Michel
Re: Question on Exchange partition [message #346483 is a reply to message #346479] Mon, 08 September 2008 12:53 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Got confused on that. You mean the "pointer" for that partition have been point to the location of the source table? Then what happens to the source table (i.e. the name of the table), where does it point to now (which causes it to contain no rows)?
Re: Question on Exchange partition [message #346485 is a reply to message #346483] Mon, 08 September 2008 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It "points" on the old partition.

Regards
Michel
Re: Question on Exchange partition [message #346488 is a reply to message #346485] Mon, 08 September 2008 13:09 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
So it really just depends on the dictionary tables, it doesn't have any like "hard-link" on it. Just curious if the partitions are independent to each other why can't it be "swap" to other partitions in other tables?
Re: Question on Exchange partition [message #346490 is a reply to message #346488] Mon, 08 September 2008 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a full test case:
SQL> create table t (col number, val varchar2(20))
  2  /

Table created.

SQL> create table tp (col number, val varchar2(20))
  2  partition by list (col)
  3  (partition p1 values (1),
  4   partition p2 values(2))
  5  /

Table created.

SQL> insert into t values (2, 'Not partitioned');

1 row created.

SQL> insert into tp values (2, 'Partitioned');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;
       COL VAL
---------- --------------------
         2 Not partitioned

1 row selected.

SQL> select * from tp partition (p2);
       COL VAL
---------- --------------------
         2 Partitioned

1 row selected.

SQL> select object_name, subobject_name, object_id, data_object_id 
  2  from user_objects 
  3  where object_name in ('T','TP') 
  4  order by 1, 2
  5  /
OBJECT_NAME SUBOBJECT_NAME  OBJECT_ID DATA_OBJECT_ID
----------- -------------- ---------- --------------
T                               50710          50710
TP          P1                  50712          50712
TP          P2                  50713          50713
TP                              50711

4 rows selected.

SQL> alter table tp exchange partition p2 with table t;

Table altered.

SQL> select * from t;
       COL VAL
---------- --------------------
         2 Partitioned

1 row selected.

SQL> select * from tp partition (p2);
       COL VAL
---------- --------------------
         2 Not partitioned

1 row selected.

SQL> select object_name, subobject_name, object_id, data_object_id 
  2  from user_objects 
  3  where object_name in ('T','TP') 
  4  order by 1, 2
  5  /
OBJECT_NAME SUBOBJECT_NAME  OBJECT_ID DATA_OBJECT_ID
----------- -------------- ---------- --------------
T                               50710          50713
TP          P1                  50712          50712
TP          P2                  50713          50710
TP                              50711

4 rows selected.

As you can see objects T and TP(P2) just swap their segments (DATA_OBJECT_ID).

Quote:
why can't it be "swap" to other partitions in other tables?

Maybe because code is not written yet.

Regards
Michel

[Updated on: Mon, 08 September 2008 13:44]

Report message to a moderator

Re: Question on Exchange partition [message #346494 is a reply to message #346490] Mon, 08 September 2008 13:39 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
@Michel,
Thank you very much again for the time. Sorry I'm just slow to catch-up =) Still having some problems understanding. What I'm trying to do is this (if this is correct):


create table tp1(col number, val varchar2(20))
partition by list(col)
(partition p1 values (1),
 partition p2 values(2)
 )

create table tp2(col number, val varchar2(20))
partition by list(col)
(partition p1 values (1),
 partition p2 values(2)
 )

insert into tp2 values(1, 'Partitioned Table 2')

insert into tp2 values(2, 'Partitioned Table 2')

SQL> select *
  2  from tp2
  3  /

       COL VAL
---------- --------------------
         1 Partitioned Table 2
         2 Partitioned Table 2


/* Here, what I'm trying to do is exchange the p2 partition in tp1 by using the p2 partition from tp2, 
but don't know how to do it. 
If you could give some explanation on it, thank you very much.
 */
SQL> alter table tp1 exchange partition p2 with table tp2;
alter table tp1 exchange partition p2 with table tp2
                                                 *
ERROR at line 1:
ORA-14095: ALTER TABLE EXCHANGE requires a non-partitioned, non-clustered table

SQL> alter table tp1 exchange partition p2 with table tp2 partition p2;
alter table tp1 exchange partition p2 with table tp2 partition p2
                                                     *
ERROR at line 1:
ORA-14094: invalid ALTER TABLE EXCHANGE PARTITION option


[Updated on: Mon, 08 September 2008 13:41]

Report message to a moderator

Re: Question on Exchange partition [message #346495 is a reply to message #346494] Mon, 08 September 2008 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said and as the error message also says, you can exchange ONLY with a non partition table.

Regards
Michel
Re: Question on Exchange partition [message #346496 is a reply to message #346495] Mon, 08 September 2008 13:49 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks again for that. Last question if its ok, is there any good reason why oracle didn't allow a "partition to partition" exchange?
Re: Question on Exchange partition [message #346498 is a reply to message #346496] Mon, 08 September 2008 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It didn't code it yet maybe because no customer asked for that feature or it thought it was not a high priority as you can do it via an intermediate non partitioned table.

Regards
Michel
Re: Question on Exchange partition [message #346500 is a reply to message #346498] Mon, 08 September 2008 13:56 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Quote:
intermediate non partitioned table.


You mean normal heap tables? Thanks again!

Hmmm didn't knew customers could request for features =)

[Updated on: Mon, 08 September 2008 13:57]

Report message to a moderator

Re: Question on Exchange partition [message #346503 is a reply to message #346500] Mon, 08 September 2008 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
didn't knew customers could request for features

Yes, I have many going on.

Regards
Michel
Re: Question on Exchange partition [message #346506 is a reply to message #346503] Mon, 08 September 2008 14:26 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Wooowww! How is that done? You send an email or something? Is it like you request a feature and some samples of how you want it to be used? Did some of your request got "done"?
Re: Question on Exchange partition [message #346523 is a reply to message #346506] Mon, 08 September 2008 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
On Metalink, you start a SR with type enhancement request describing the feature, its insterest, its business need.
If analyst thinks it is good enough, it opens an internal enhancement request which will be reviewed by a commitee which may plan it for a furture version.
Very long process indeed.
Some of mine, with level "Very desirable feature" are in "internal review" status since a couple of years.
It depends of the difficulty to develop, integrate and test it, and also on the number of customers asking for the same thing.

Regards
Michel

[Updated on: Mon, 08 September 2008 15:03]

Report message to a moderator

Re: Question on Exchange partition [message #346526 is a reply to message #346523] Mon, 08 September 2008 15:09 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hmmm that's something new! Wish we have metalink accounts too =(
Re: Question on Exchange partition [message #346607 is a reply to message #346526] Tue, 09 September 2008 02:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Without metalink, what were you planning to do when your production database dies spectacularly?
Re: Question on Exchange partition [message #346694 is a reply to message #346607] Tue, 09 September 2008 07:19 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi Sir JRowbottom =)
Don't know with the DBA's =) I tried asking for metalink accounts at them, they said they don't have it.
Previous Topic: regarding external tables
Next Topic: The Trouble with Triggers.
Goto Forum:
  


Current Time: Thu Dec 08 18:05:47 CST 2016

Total time taken to generate the page: 0.15863 seconds