Skip to Main Content
  • Questions

  • When is data moved during an Oracle partition split?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, S.

Asked: May 10, 2013 - 9:15 am UTC

Last updated: May 10, 2013 - 7:58 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

I need to split an Oracle partition, and I'm confused about whether or not Oracle is going to physically relocate the data when I execute the split. Here's what I intend to do:

alter table SCHEMA.DATATABLE
split partition MAXVAL_PART
at (TO_DATE(' 2013-04-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
into (partition PARTFOREXISTINGROWS
tablespace EXISTINGMAXVALTABLESPACE,
partition MAXVAL_PART
tablespace NEWMAXVALTABLESPACE);The problem is that my current MAXVAL_PART has 320 GB of data in it, and I don't want to physically move this data on the disk. If I cut off the partition at 2013-04-01, then there will be no data in the new partition, but I'm getting conflicting information about whether this will still necessitate a move of all the data. Ideally, Oracle sees that my new MAXVAL partition will be empty, defines it in the new table space, and I'm all done.

Will this command move the data that's already on the disk, or will it leave it in place and just create a new partition?


and Tom said...

we do have optimizations for splitting:

http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin002.htm#VLDBG00304

if the partition satisfies those prerequisites, it will not move the data. We can observe this:

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION maxval_part VALUES LESS THAN ( to_date( '01-jun-2013', 'dd-mon-yyyy' ) )
 12  )
 13  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t (dt,x,y) values ( to_date( '15-mar-2013', 'dd-mon-yyyy' ), 100, 'hello' );

1 row created.

ops$tkyte%ORA11GR2> insert into t (dt,x,y) values ( to_date( '16-mar-2013', 'dd-mon-yyyy' ), 100, 'hello' );

1 row created.

ops$tkyte%ORA11GR2> -- insert into t (dt,x,y) values ( to_date( '16-may-2013', 'dd-mon-yyyy' ), 100, 'hello' );
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table tmp
  2  as
  3  select rowid rid, dt from t;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t
  2  split partition MAXVAL_PART
  3  at (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  4  into (partition PARTFOREXISTINGROWS tablespace users,
  5        partition MAXVAL_PART tablespace users );

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select t.rowid, t.dt, tmp.rid, tmp.dt from t,tmp where t.rowid = tmp.rid;

ROWID              DT        RID                DT
------------------ --------- ------------------ ---------
AAAkZ5AAEAAACAuAAA 15-MAR-13 AAAkZ5AAEAAACAuAAA 15-MAR-13
AAAkZ5AAEAAACAuAAB 16-MAR-13 AAAkZ5AAEAAACAuAAB 16-MAR-13

ops$tkyte%ORA11GR2> select t.rowid, t.dt, tmp.rid, tmp.dt from t,tmp where t.dt = tmp.dt;

ROWID              DT        RID                DT
------------------ --------- ------------------ ---------
AAAkZ5AAEAAACAuAAA 15-MAR-13 AAAkZ5AAEAAACAuAAA 15-MAR-13
AAAkZ5AAEAAACAuAAB 16-MAR-13 AAAkZ5AAEAAACAuAAB 16-MAR-13



note how the rowids did not change at all - the data didn't move anywhere. However, if we uncomment that insert that would cause both partitions to have some data:

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION maxval_part VALUES LESS THAN ( to_date( '01-jun-2013', 'dd-mon-yyyy' ) )
 12  )
 13  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t (dt,x,y) values ( to_date( '15-mar-2013', 'dd-mon-yyyy' ), 100, 'hello' );

1 row created.

ops$tkyte%ORA11GR2> insert into t (dt,x,y) values ( to_date( '16-mar-2013', 'dd-mon-yyyy' ), 100, 'hello' );

1 row created.

ops$tkyte%ORA11GR2> insert into t (dt,x,y) values ( to_date( '16-may-2013', 'dd-mon-yyyy' ), 100, 'hello' );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table tmp
  2  as
  3  select rowid rid, dt from t;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t
  2  split partition MAXVAL_PART
  3  at (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  4  into (partition PARTFOREXISTINGROWS tablespace users,
  5        partition MAXVAL_PART tablespace users );

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select t.rowid, t.dt, tmp.rid, tmp.dt from t,tmp where t.rowid = tmp.rid;

no rows selected

ops$tkyte%ORA11GR2> select t.rowid, t.dt, tmp.rid, tmp.dt from t,tmp where t.dt = tmp.dt;

ROWID              DT        RID                DT
------------------ --------- ------------------ ---------
AAAkaCAAEAAADwSAAA 15-MAR-13 AAAkaAAAEAAADguAAA 15-MAR-13
AAAkaCAAEAAADwSAAB 16-MAR-13 AAAkaAAAEAAADguAAB 16-MAR-13
AAAkaDAAEAAAEASAAA 16-MAY-13 AAAkaAAAEAAADguAAC 16-MAY-13



observe how all the rowids have changed - the data had to physically move...


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Understandings

Rajeshwaran, Jeyabal, May 10, 2013 - 7:38 pm UTC

Tom:
alter table t 
split partition pmax values(7)
into ( partition p_7, partition pmax)

1) if all rows in pmax are eligible to be moved into p_7 or none of the rows in pmax are eligible to be moved into p_7
2) if the partition statisfies those pre-requisties
3) if both (1) and (2) are true then can we say SPLIT partition is very simillar to ADD partition?
4) if either of them are flase, then SPLIT partition will cause row movements?
Tom Kyte
May 10, 2013 - 7:58 pm UTC

all of the rows have to end up in a single partition after the split, and you have to meet the pre-requisites in the above link.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.