Excellent!
Prakash Bala, May 21, 2003 - 12:01 pm UTC
Didn't know that there was such a clause in order to enable the row movement
Excellent Tom!!!
A reader, September 23, 2003 - 6:17 pm UTC
Learnt a new thing today and implemented it too!!!
is enable row movement very costy?
A reader, January 13, 2004 - 8:27 am UTC
hi
is enable row movement very costy? large overhead?
January 13, 2004 - 5:44 pm UTC
no, it is an "attribute".
only when the row actually "Moves" (due to an update of a partition key that moves partitions) would it incurr any "extra work" (it is like a delete + insert instead of just update then)
Indexes
John French., January 14, 2004 - 12:44 pm UTC
Could you discuss the Impact of Row Movement on Partitions on Indexes - Both Global and Local.
January 14, 2004 - 4:11 pm UTC
think "delete plus insert"
that is what it is like....
the rowid changes - it is just like delete + insert.
John French, January 15, 2004 - 10:15 am UTC
I was looking for cases where the Indexes become unusable and the reasonn why they do and which of these Indexes can be used to regenerate/rebuild new Indexes.
January 15, 2004 - 10:32 am UTC
guess you looked at the wrong page then?
ALL unusable indexes can be rebuilt (that seems obvious?)
they become unusable because you did some operation to the base table that did not maintain the index -- partition operations, alter table moves, "big bulk operations on the table" in general have the opportunity to do this.
Lord of Oracle
Reader, January 15, 2004 - 11:55 am UTC
Tom
Is there anything that you really don't know in Oracle database ? Will you kindly lend me the Oracle part of your brain for just 30 days ?
Hats off to you again !
January 15, 2004 - 12:24 pm UTC
I learn something new about Oracle just about every day myself.
Very useful. Just altered a table to permit row movement
Kayode Afolayan (CodePoet), July 09, 2004 - 1:44 pm UTC
I came to this page after getting the "ORA-14402: updating partition key column would cause a partition change" error message. I then tried ALTER TABLE xYz ENABLE ROW MOVEMENT and, like a miracle, my problems fizzled away! I learnt a new thing too after 15 years of using oracle. Thanks Tom.
On point, on target, concise, just awesome
Nik Malenovic, January 26, 2005 - 1:47 pm UTC
my favorite math professors always used that math term - neccessary and sufficient condition; same applies to Tom's replies - he provides precisely the needed amount of information - not too much, not too little - just sufficient and neccessary.
great work Tom.
Thanks !!
Pratik, March 16, 2005 - 11:56 pm UTC
Thank you for provding a clean and elegant solution to the problem.
Row Movement
Sai, May 18, 2005 - 6:26 pm UTC
Thanks Tom... I got the problem regarding Row movement today and solved.
is patrtition key updatable when such change causes movement from partition
manaswee tuli, June 09, 2005 - 5:23 am UTC
the answer was very useful. thanks could you send a link of your site where we can ask more question without requiring login information
June 09, 2005 - 7:19 am UTC
see the dizwell forum (click the other resources above)
Fantastic Tom
Patrick Gonsalvez, June 16, 2005 - 7:06 am UTC
Your solution was straight to the point and it works! Thanks Tom, i will be sure to spread the word around of this site. Keep it up.
Excellent
awestruck, June 29, 2005 - 8:32 pm UTC
Tom,
Your website is an ocean of useful information and every time i get stuck in Oracle, I look up your web site.
Keep up your amazing work!
Thanx,
awestruck
Could row movement cause ORA 30926 (unable to get a stable set of rows in the source tables)?
Yanivus, August 09, 2005 - 1:27 pm UTC
Hi tom,
Following up on the row movement example above, here is a case (v9206) where I notice random ORA 30926 errors. It is quite reproducable.
-- create seq
CREATE SEQUENCE MYTAB_SEQ
INCREMENT BY 10
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
CACHE 1000
NOCYCLE
NOORDER
;
-- create partitioned table
-- all in USER tablespace to avoid TS name worries
CREATE TABLE MYTAB
(
MYTAB_OBJID NUMBER(38) NOT NULL,
ACTIVATED_DTSTAMP NUMBER(16) NOT NULL,
INACTIVATED_DTSTAMP NUMBER(16) DEFAULT 9999999999999999 NOT NULL,
CREATED_BY_USER_ID VARCHAR2(30) NOT NULL,
CREATED_DTM TIMESTAMP(6) NOT NULL,
MODIFIED_BY_USER_ID VARCHAR2(30) NOT NULL,
MODIFIED_DTM TIMESTAMP(6) NOT NULL,
REPLICATED_DTM TIMESTAMP(6) NULL
)
TABLESPACE USERS
LOGGING
PCTFREE 20
INITRANS 8
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
PARTITION BY RANGE(INACTIVATED_DTSTAMP)
(PARTITION DATE_082005 VALUES LESS THAN ('2005080100000000')
TABLESPACE USERS,
PARTITION DATE_092005 VALUES LESS THAN ('2005090100000000')
TABLESPACE USERS,
PARTITION DATE_102005 VALUES LESS THAN ('2005100100000000')
TABLESPACE USERS,
PARTITION DATE_112005 VALUES LESS THAN ('2005110100000000')
TABLESPACE USERS,
PARTITION DATE_122005 VALUES LESS THAN ('2005120100000000')
TABLESPACE USERS,
PARTITION DATE_012006 VALUES LESS THAN ('2006010100000000')
TABLESPACE USERS,
PARTITION BOOK_END VALUES LESS THAN (MAXVALUE)
TABLESPACE USERS)
ENABLE ROW MOVEMENT
/
-- create partitioned pk
CREATE UNIQUE INDEX MYTAB_UK01
ON MYTAB(MYTAB_OBJID,INACTIVATED_DTSTAMP)
LOGGING
PCTFREE 20
INITRANS 8
MAXTRANS 255
STORAGE(INITIAL 1M
NEXT 1M
PCTINCREASE 0
BUFFER_POOL DEFAULT)
NOPARALLEL
LOCAL
(PARTITION DATE_082005
TABLESPACE USERS,
PARTITION DATE_092005
TABLESPACE USERS,
PARTITION DATE_102005
TABLESPACE USERS,
PARTITION DATE_112005
TABLESPACE USERS,
PARTITION DATE_122005
TABLESPACE USERS,
PARTITION DATE_012006
TABLESPACE USERS,
PARTITION BOOK_END
TABLESPACE USERS)
NOCOMPRESS
/
ALTER TABLE MYTAB
ADD CONSTRAINT MYTAB_PK
PRIMARY KEY (MYTAB_OBJID)
USING INDEX TABLESPACE USERS
PCTFREE 20
INITRANS 4
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
LOGGING
ENABLE
VALIDATE
/
-- I donot think triggers are relevant but just for
-- the sake of it
CREATE OR REPLACE TRIGGER MYTAB_BI
BEFORE INSERT
ON MYTAB
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (NEW.MYTAB_objid IS NULL)
BEGIN
IF (USER <> 'GGSUSER') THEN
SELECT MYTAB_seq.NEXTVAL INTO :NEW.MYTAB_objid FROM DUAL;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20002, 'MYTAB_BI failed to insert next Id.' || CHR(10) || SQLERRM);
END;
/
CREATE OR REPLACE TRIGGER MYTAB_BIU
BEFORE INSERT OR UPDATE
ON MYTAB
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
IF (USER <> 'GGSUSER') THEN
IF (INSERTING) THEN
IF (:NEW.created_by_user_id IS NULL) THEN
:NEW.created_by_user_id := USER;
END IF;
:NEW.created_dtm := LOCALTIMESTAMP;
ELSE /* ensure values not reset */
:NEW.created_by_user_id := :OLD.created_by_user_id;
:NEW.created_dtm := :OLD.created_dtm;
END IF;
IF (:NEW.modified_by_user_id IS NULL) THEN
:NEW.modified_by_user_id := USER;
END IF;
:NEW.modified_dtm := LOCALTIMESTAMP;
ELSE
:NEW.replicated_dtm := LOCALTIMESTAMP;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001, 'MYTAB_BIU failed to set audit fields.' || CHR(10) || SQLERRM);
END;
/
-- now put in some data
begin
for i in 1 .. 15000
loop
insert into MYTAB(ACTIVATED_DTSTAMP, INACTIVATED_DTSTAMP)
values (1, 9999999999999999);
end loop;
end;
/
commit;
-- try the following updates - get random errors
update mytab set INACTIVATED_DTSTAMP = 2005072300000000;
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
update mytab set INACTIVATED_DTSTAMP = 9999999999999999;
update mytab set INACTIVATED_DTSTAMP = 9999999999999999
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
Worth a TAR, I guess.
August 09, 2005 - 3:17 pm UTC
CREATE TABLE MYTAB
(
MYTAB_OBJID NUMBER(38) NOT NULL,
ACTIVATED_DTSTAMP NUMBER(16) NOT NULL,
INACTIVATED_DTSTAMP NUMBER(16) DEFAULT 9999999999999999 NOT
....
PARTITION BY RANGE(INACTIVATED_DTSTAMP)
(PARTITION DATE_082005 VALUES LESS THAN ('2005080100000000')
that is not a good idea -- to have a NUMBER(16) and then compare it to a STRING
compare dates to dates
strings to strings
numbers to numbers
But that isn't the problem, and the trigger is very much part of the equation. I slimmed down the test case, made it generic and understandable easily. It took differing amounts of rows on different releases to reproduce, and it could be dependent on block size, sga, whatever (not sure, did not take it further)
drop TABLE MYTAB;
CREATE TABLE MYTAB
(
key number,
data1 char(2000) default 'x',
dt TIMESTAMP default localtimestamp
)
PARTITION BY RANGE(key)
(PARTITION p1 VALUES LESS THAN (5),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
)
ENABLE ROW MOVEMENT
/
CREATE OR REPLACE TRIGGER MYTAB_BIU
BEFORE UPDATE ON MYTAB FOR EACH ROW
BEGIN
:NEW.dt := LOCALTIMESTAMP;
END;
/
insert into MYTAB (key)
select 4
from all_objects
where rownum <= 20000;
commit;
update mytab set key = 6;
should reproduce on 9ir2 and 10gr1 (8k block size). I could not get it to reproduce in 9ir1 even with 31,000+ rows.
That'll be a much better test case to work support with.
Could row movement cause ORA 30926 (unable to get a stable set of rows in the source tables)?
Yanivus, August 09, 2005 - 6:16 pm UTC
<quote>
that is not a good idea -- to have a NUMBER(16) and then compare it to a STRING
</quote>
Point taken and will correct. Thank you.
--------
I was able to reproduce the same behaviour with your
"to-the-point" test case. The nearest bug I could find
was 923020 (UPDATE OF TABLE FAILED WITH ORA-30926 WHEN BITMAP INDEX IS USED) which affected 8.1.6.3 version
and fixed in 8.2. I am on 9.2.0.6.
Since this needs to be looked at little bit in
detail, I filed a TAR.
Thank you. Appreciate your input very much.
August 09, 2005 - 7:28 pm UTC
I could not find anything near close - I don't thing that is even in the ballpark (i removed all indexes).
(8.2 is 9ir1 by the way)
Could row movement cause ORA 30926 (unable to get a stable set of rows in the source tables)?
Yanivus, August 12, 2005 - 10:54 am UTC
Just an update on this issue: ORACLE reported a bug on the issue above (Bug# 4549673).
Single or Multiple Tablespaces for compensating extra overhead due to row movement?
Mohit, October 08, 2008 - 4:22 pm UTC
Tom,
Let's say we have 2 partitions in a table, Partition A and Partition B. We know that row movement caused due to update of partitioning key adds an extra overhead ( delete + insert ...instead of update). In order to compensate for the extra overhead, will it make sense for each partition to be on a separate tablespace rather than same so that the row movement is much quicker from Partition A to Partition B?
October 08, 2008 - 10:21 pm UTC
the tablespace will not have anything to do with it really. Tablespaces are about ease of administration, not performance so much.
How about hash partition key?
ssgoh, October 29, 2008 - 6:05 am UTC
I have a table with 12 millions of records, partitioned by hash with 2 keys - colA and colB.
I need to update all the colA data to have a zero "0" infront of the existing data, e.g. 12345 become 012345.
Can I enable row movement and follow by updating colA?
October 29, 2008 - 8:21 am UTC
well, you could
but it would be a disaster.
Every row, every single row, will move.
You would be best served by using dbms_redefinition if you need this to be done with no downtime.
You would be best served by using create table as select if you can do this with downtime.
Row Movement
A reader, December 10, 2008 - 3:35 pm UTC
Hi Tom,
I think I read in one of your book that row movement is bad design. We have scenario where last process flag(Y/N) is used to build data mart from staging. But once the rows are processed we want to update process_flag='Y' in staging rows. The source rows are quite large, but with processed_flag='N' will be quite small (few hundred thousands). We were thinking of creating list parition for processed flag, but update will result into row movement. Do you think we should not think in this direction because of overheads associated with row movement vs gains we will get (becuase of partition elimination)?
Thanks
December 10, 2008 - 4:26 pm UTC
...I think I read in one of your book that row movement is bad design....
What I wrote was:
...
You need to understand that, internally, row movement is done as if you had in fact deleted the row and reinserted it. It will update every single index on this table, and delete the old entry and insert a new one. It will do the physical work of a DELETE plus an INSERT. However, it is considered an update by Oracle even though it physically deletes and inserts the row¿therefore, it won¿t cause INSERT and DELETE triggers to fire, just the UPDATE triggers. Additionally, child tables that might prevent a DELETE due to a foreign key constraint won¿t. You do have to be prepared, however, for the extra work that will be performed; it is much more expensive than a normal UPDATE. Therefore, it would be a bad design decision to construct a system whereby the partition key was modified frequently and that modification would cause a partition movement.
......
Now, you modify the partition key once I presume - that is from "N" to "Y"
So, you do not modify the key of a row frequently.
However, if your goal is to find "N" rows "fast", you might consider naming the column
I_am_not_null_if_not_processed
and populating that with 'Y' (or 'N', whatever) and setting it to NULL when it is processed.
Then index it.
the index will be tiny (as only the not null keys are indexed...) and the optimizer will use it to find the first non-processed row...
From previous review
A reader, December 30, 2008 - 10:23 am UTC
Tom:
we are exactly in the similar kind of situation as mentioned above.
Would like to know if you would be inclined in updating the huge main table with the processed_flag of "Y" or would you be more inclined to insert processed rows into a separate table with the primary_key of the main_table, processed_id,processed_flag ?
The question would be how to always find non-processed records in the main table?
a) outer join between main_table, secondary processed table?
b) updating the flag in the main table?
Thanks,
January 05, 2009 - 9:46 am UTC
I would be more inclined to use AQ (advanced queues) as you obviously have a queue!
My preferred set of options, in order:
a) use AQ, you have a queue. You probably want to have multiple de-queuers, prioritization, and many other features people expect of queues. AQ would provide that.
b) use two tables, not_processed, processed. Move records from not_processed to processed as they are processed.
c) have a processed flag that is N when not processed, NULL otherwise. Index it. It will ONLY index the not yet processed records (and it will be a small index)
d) create a function based index on processed_flag. create index I on t( case when processed_flag = 'N' then 'N' else NULL end ); Use that same function in the where clause. It will have the same net effect as (c).
imran parvez, April 13, 2011 - 4:48 am UTC
the answer was very useful. thanks could you send a link of your site where we can ask more
question without requiring login information
April 13, 2011 - 10:01 am UTC
when did you ever have to login to asktom?