Is ROW MOVEMENT expensive ?

Create NON Partition table

SQL> create table non_part (x number, y number);

Table created.

SQL>

Create Primary Key on this table.

SQL> alter table non_part add primary key(x);

Table altered.

SQL>

Let's create Partitioned table


SQL> CREATE TABLE part
2 ( x NUMBER,
3 y number)
4 PARTITION BY RANGE (y)
5 ( PARTITION part_y_1 VALUES LESS THAN (1),
6 PARTITION part_y_2 VALUES LESS THAN (2),
7 PARTITION part_y_3 VALUES LESS THAN (MAXVALUE)
8 );



Table created.

Elapsed: 00:00:00.09
SQL>

alter table part add primary key (x)
SQL> /

Table altered.

Elapsed: 00:00:00.89

Let's populate the tables now....


declare
i number:=0;
begin
for i in 1..100000
loop
insert into non_part values (i,1);
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL>

declare
i number:=0;
begin
for i in 1..100000
loop
insert into part values (i,1);
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL>



SQL> select count(1) from non_part;

COUNT(1)
----------
100000

SQL>


SQL> select count(1) from part partition(part_y_1);

COUNT(1)
----------
0

1* select count(1) from part partition(part_y_2)
SQL> /

COUNT(1)
----------
100000

1* select count(1) from part partition(part_y_3)
SQL> /

COUNT(1)
----------
0


SQL> update non_part
2 set y = 3;

100000 rows updated.

Elapsed: 00:01:15.12

So, it took 1 minute and 15 seconds to update 100000 rows.


SQL> update part
2 set y=3;

100000 rows updated.

Elapsed: 00:04:50.19


SQL> select count(1) from part partition(part_y_3);

COUNT(1)
----------
100000



So, the conclusion is that it took almost 4 times as much time to update 100k rows in a partition table with row movement and we made sure all 100k rows were moved.

So each row that took .69 millisecond would take 2.7 millisecond. Generally, in an OLTP system this isn't good but your application should be purely subjective.