Tuesday, September 7, 2010

Two way Exchange in Exchange partition

Not something entirely learned new today - but rather a revisited "something I learned" coupled with an "I didn't necessarily expect it in this case". It is the old exchange partitioning issue.

Referring to Exchange partition and thought that data will be either swapped from partition segment to table segments  or from table to partition segments but not the both, but that is false Its actually a two way swapping.

Here is a snippet of the code again:

rajesh@10GR2> create table t(x)
  2  partition by range(x)
  3  (
  4     partition p1 values less than(5)
  5  )
  6  as
  7  select 1 from dual;

Table created.

Elapsed: 00:00:00.15
rajesh@10GR2>
rajesh@10GR2> create table t1 (x) as select 2 from dual;

Table created.

Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> select * from T;

         X
----------
         1

Elapsed: 00:00:00.03
rajesh@10GR2> select * from T1;

         X
----------
         2

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> alter table t exchange partition p1 with table t1 without validation;

Table altered.

Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2> select * from T;

         X
----------
         2

Elapsed: 00:00:00.01
rajesh@10GR2> select * from T1;

         X
----------
         1

Elapsed: 00:00:00.01
rajesh@10GR2>

No comments:

Post a Comment