oracle
oracle 8.0中没有行移动功能，在这个版本中，必须先删除该行，再重新将其插入
测试环境oracle 11.2.0.3.0
-- Create table
create table GH_RANGE_EXAMPLE
(
id VARCHAR2(100),
range_date DATE not null
)
partition by range (RANGE_DATE)
(
partition RANGE_15 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace TS_ZYK_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition RANGE_16 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace TS_ZYK_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition RANGE_MAX values less than (MAXVALUE)
tablespace TS_ZYK_DATA
pctfree 10
initrans 1
maxtrans 255
);
SQL> select * from gh_range_example t;
ID RANGE_DATE
-------------------------------------------------------------------------------- -----------
100 2015/6/16 1
100 2016/6/15 1
SQL> update gh_range_example t set t.id=t.id+1;
2 rows updated
SQL> select * from gh_range_example t;
ID RANGE_DATE
-------------------------------------------------------------------------------- -----------
101 2015/6/16 1
101 2016/6/15 1
SQL> update gh_range_example t set t.range_date=trunc(sysdate) where t.range_date < trunc(sysdate)-10;
update gh_range_example t set t.range_date=trunc(sysdate) where t.range_date < trunc(sysdate)-10
ORA-14402: updating partition key column would cause a partition change
SQL> select rowid from gh_range_example t where t.range_date < trunc(sysdate)-10;
ROWID
--------------------------------------------------------------------------------
AAADfLAAGAAABq3AAA
SQL> alter table gh_range_example enable row movement;
Table altered
SQL> update gh_range_example t set t.range_date=trunc(sysdate) where t.range_date < trunc(sysdate)-10;
1 row updated
SQL> commit;
Commit complete
SQL> select rowid from gh_range_example t where t.range_date = trunc(sysdate);
ROWID
--------------------------------------------------------------------------------
AAADfMAAGAAACU6AAA
SQL>
行rowid发生变化，代表索引也是重新变化
如果遇到ORA-14402: updating partition key column would cause a partition change
开启 row movement;
