skip to content »

hhaje.pan-yanino.ru

Updating partition key column would cause a partition change

updating partition key column would cause a partition change-79

DBF' SIZE 10M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; CREATE SMALLFILE TABLESPACE "P04" LOGGING DATAFILE '/home/oracle/oradata/testdb/P04.

A simple alter table statement remedies the update problem. Well, tables are partitioned for a reason, and to help ensure data goes where it is supposed to, you should disallow inadvertent updates or row movements.DBF' SIZE 10M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; -- Create tablecreate table HASH_PARTAB_TEST( NUM NUMBER(10), KEYSTRING VARCHAR2(10) PRIMARY KEY)partition by hash (KEYSTRING)( partition P01 tablespace P01, partition P02 tablespace P02, partition P03 tablespace P03, partition P04 tablespace P04, partition P05 tablespace P05); TABLE_NAME ROW_MOVEMENT------------------------------ ----------------HASH_PARTAB_TEST DISABLED declarev_rand number(1) default 0;v_class varchar2(2) default null;begin for i in 1..500 loop select trunc(dbms_random.value('1','6')) into v_rand from dual; case v_rand when 1 then v_class := ' AA'; when 2 then v_class := ' AB'; when 3 then v_class := ' AC'; when 4 then v_class := ' AD'; when 5 then v_class := ' AE'; end case; insert into HASH_PARTAB_TEST(num,keystring) values(i,v_class||trunc(dbms_random.value('11111111','99999999'))); end loop;commit;end;/ alter table HASH_PARTAB_TEST add constraint uk_HASH_PARTAB_TESTunique (num) using index tablespace indx nologging; col class format a5select substr(keystring,1,2) as class,count(*)from HASH_PARTAB_TESTgroup by substr(keystring,1,2); update HASH_PARTAB_TEST set keystring = replace(keystring,' AC',' AA') where keystring like ' AC%'; 82 rows updated.One of the relatively newer features in Oracle concerns the moving of rows.Use this option in cases where you are working with only one particular partition at a time in a partitioned table.Specifying this option boosts performance because you are limiting your access to only one partition of a table instead of the entire table.Why would a row move and who or what controls that movement?

Furthermore, by “move,” what exactly does move mean?

*/ update x.orparttest ( or_partition=p4 ) set num=100; update x.orparttest ( or_partition=p4 ) set num=35; select * from x.orparttest ( or_partition=p4 ); select * from x.orparttest ( or_partition=p8 ); /* delete */ delete from x.orparttest ( or_partition=p4 ); select * from x.orparttest; quit; /* load to an existing table */ data new; do i=31 to 39; num=i; output;end; run; data new2; do i=1 to 9; num=i; output;end; run; proc append base= x.orparttest ( or_partition=p4 ) data= new; run; /* insert should fail 14401, 00000, "inserted partition key is outside specified partition" // *Cause: the concatenated partition key of an inserted record is outside // the ranges of the two concatenated partition bound lists that // delimit the partition named in the INSERT statement // *Action: do not insert the key or insert it in another partition */ proc append base= x.orparttest ( or_partition=p4 ) data= new2; run; /* load to an existing table */ proc append base= x.orparttest ( or_partition=p4 bulkload=yes bl_load_method=truncate ) data= new; run; /* insert should fail 14401 */ proc append base= x.orparttest ( or_partition=p4 bulkload=yes bl_load_method=truncate ) data= new2; run;libname x oracle user=scott pw=tiger path=oraclev9; proc delete data=x.orparttest; run; data x.

ORparttest ( dbtype=(NUM='int') DBCREATE_TABLE_OPTS='partition by range (NUM) (partition p1 values less than (11), partition p2 values less than (21), partition p3 values less than (31), partition p4 values less than (41), partition p5 values less than (51), partition p6 values less than (61), partition p7 values less than (71), partition p8 values less than (81) )' ); do i=1 to 80; NUM=i; output; end; run; The above example also illustrates how a particular partition can be updated.

How would you move records from the old partitioned into the new one?

Short of deleting from one partition and inserting same into a new one, wouldn’t it be easier to be able to perform a single update?

Does a row move to another table, or is row movement constrained to the row’s container (i.e., a table)?