(8.0.26)
drop table tab1;
create table tab1
( col1 int primary key
, col2 int
, col3 int
, col4 int
)
partition by range (col1)
( partition p1 values less than (0)
, partition p2 values less than (maxvalue)
);
insert into tab1 values(-1,0,0,0);
select * from tab1 partition(p1);
select * from tab1 partition(p2);
update tab1 set col1 = 1;
select * from tab1 partition(p1);
select * from tab1 partition(p2);
(19c)
drop table tab1 purge;
create table tab1
( col1 int primary key
, col2 int
, col3 int
, col4 int
)
partition by range (col2)
( partition p1 values less than (0)
, partition p2 values less than (maxvalue)
);
insert into tab1 values(0,-1,0,0);
commit;
select * from tab1 partition(p1);
select * from tab1 partition(p2);
update tab1 set col2 = 1;
行1でエラーが発生しました。:
ORA-14402: パーティション・キー列を更新するとパーティションが変更されます。
select table_name,ROW_MOVEMENT
from user_tables
where table_name = 'TAB1';
alter table tab1 enable row movement;
select table_name,ROW_MOVEMENT
from user_tables
where table_name = 'TAB1';
update tab1 set col2 = 1;
select * from tab1 partition(p1);
select * from tab1 partition(p2);
commit;
行移動を有効化すると、パーティション変更のパーティションキー更新が可能となる
(14)
create table tab1
( col1 int primary key
, col2 int
, col3 int
, col4 int
)
partition by range ( col1 )
;
create table tab1p1 partition of tab1 for values from (minvalue) to (0);
create table tab1p2 partition of tab1 for values from (0) to (maxvalue);
insert into tab1 values(-1,0,0,0);
select * from tab1p1;
select * from tab1p2;
update tab1 set col1 = 1;
(2019)
use test
go
drop partition scheme ps1;
drop partition function pf1;
create partition function pf1(int) as range right for values (0);
create partition scheme ps1 as partition pf1 all to ([primary]);
drop table tab1;
create table tab1
( col1 int primary key
, col2 int
, col3 int
, col4 int
)
on ps1(col1)
;
insert into tab1 values(-1,0,0,0);
select top 100 t1.col1,t2.file_id,t2.page_id,t2.slot_id
from tab1 t1
cross apply sys.fn_PhysLocCracker(%%physloc%%) as t2
order by t2.file_id, t2.page_id, t2.slot_id
;
update tab1 set col1 = 1;
select top 100 t1.col1,t2.file_id,t2.page_id,t2.slot_id
from tab1 t1
cross apply sys.fn_PhysLocCracker(%%physloc%%) as t2
order by t2.file_id, t2.page_id, t2.slot_id
;