When is data moved during an Oracle partition split?

Question

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?

This article says Oracle will detect the empty partition and do a fast split http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm

But I see in a number of places that a split means all data gets moved to new storage. Which is it?

Answer 1

It won't move the data.

Test case:

SQL> CREATE TABLESPACE myts1 DATAFILE '/u01/app/oracle/oradata/ORA112/myts1_01.dbf' size 50M;

Tablespace created.

SQL> CREATE TABLESPACE mytsmax DATAFILE '/u01/app/oracle/oradata/ORA112/mytsmax_01.dbf' size 50M;

Tablespace created.

SQL> CREATE TABLE mytable (id NUMBER, dt DATE)
PARTITION BY RANGE (dt)
  (PARTITION mytablep1 VALUES LESS THAN (TO_DATE('2013-01-01', 'YYYY-MM-DD')) TABLESPACE myts1,
   PARTITION mytablep2 VALUES LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD')) TABLESPACE myts1,
   PARTITION mytablep3 VALUES LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD')) TABLESPACE myts1,
   PARTITION mytablep4 VALUES LESS THAN (MAXVALUE) TABLESPACE myts1
  );  

Table created.

I inserted some data. The results:

SQL> select dt, count(*) 
  2  from mytable
  3  group by dt;

DT      COUNT(*)
--------- ----------
11-NOV-12     262272
11-JAN-13     262272
11-FEB-13     262272
11-MAR-13     262272

Now, the best way of working out if a row has moved is to look at its rowid. The rowid for a given row is based on the location of the row in a datafile and block. Therefore, if the rowid changes it means the row has moved! Documentation link.

So, create a table to hold the rowids of the existing rows in the table, and insert them:

SQL> create table myrowids ( r rowid );

Table created.

SQL> insert into myrowids ( select rowid from mytable );

1049088 rows created.

SQL> commit;

Perform the split:

SQL> ALTER TABLE mytable
SPLIT PARTITION mytablep4
AT ( TO_DATE('2013-04-01', 'YYYY-MM-DD') )
INTO ( PARTITION mytablep4 TABLESPACE myts1, PARTITION mytablep5 TABLESPACE mytsmax );   

Table altered.

SQL>

Check that the rows haven't moved (could have used an EXISTS instead):

SQL> select count(*) from myrowids where r not in ( select rowid from mytable );

  COUNT(*)
----------
     0

SQL>

They haven't!