Re: Constraint policing - redo generation? transaction failing

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 23 Nov 2019 09:48:02 +0000
Message-ID: <CWXP265MB1750AB711EA17CAAA3E1858EA5480_at_CWXP265MB1750.GBRP265.PROD.OUTLOOK.COM>


You need to do a couple more experiments.

In general the uniqueness constraint can only be checked after the statement is (nearly) complte because you could have multi-row updates e.g.

update temp set roll = decode (roll,1,999999,999999,1) where roll in (1,999999);

It's is perfectly feasible, though, that the single row update (which can obviously be recognised by the optimizer) has been coded specially to attempt the index leaf block insert before the index leaf block delete specifically because that avoids a little of the work needed in a rollback on duplicate key.

You really need to dump the redo log for your transaction to check exactly what Oracle does do in this case; but another test you could do is to attempt to change two rows with entries in different leaf blocks to the same pre-existing key value, e.g.

update temp set roll = 1 where roll in (80001,90001);

and see which blocks become dirty.

Regards
Jonathan Lewis



From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Vishnu Potukanuma <vishnupotukanuma_at_gmail.com> Sent: 23 November 2019 08:51
To: Oracle L
Subject: Constraint policing - redo generation? transaction failing

Hi,

the scenario is this.
create table temp (roll number, name varchar2(20), mark1 number, mark2 number); alter table temp add constraint temp_pk primary key (Roll); insert into temp select rownum, 'VISHNU',12,123) from dual connect by level < 10; commit;

now we have 10 rows in the table with rolls as 1,2,3 ... 10 when i insert the following row into the table. insert into temp values (1,'vishnu',123,123,123,);

Since the index is a unique... oracle checks to see if a row exists already even before modifying the index leaf block and since the row exists already it throws an exception ORA-00001: unique constraint (VISHNU.TEMP_PK) violated.. in this case, the index leaf block retains its status before and after. this is not the case when the index is a non unique index.

But the handling appears to be different for update statements, the same case as above when the constraint is policed via an unique index... consider the statement
update temp set roll=2 where roll = 1;
we know that roll=2 already exists in table, and the constraint check appears to be postponed until after the update statement is executed or the block is modified, since the index leaf block becomes dirty in this case... i was wondering whether it is during the deletion phase or insertion phase, i believe update is equivalent to delete and insert when it comes to indexes, i may not be 100% correct here but please be patient.

now we know that the block is being modified. since here both the rows 1,2 are present in the same leaf block, i tried a different strategy, in this case i recreated the same table and loaded over 100k rows, created the constraint (unique index).

now things get a little weird, the two distinct leaf blocks say A and B where A stores the value 1 and B stores the value 99999. their block status are clean (I have restarted the database). now i issue the update statement as follows: update temp set roll=99999 where roll = 1;

so we know before modifying a block, unique constraints are validated when we have an unique index. the result of the above update statement is as follows: leaf block A (which holds the key value 1) becomes dirty (or modified). Leaf block B retains the clean status.

A valid explanation to this can be that only before modifying a block to add an new entry to the leaf block oracle checks the unique constraint, but since the update statement, it has to modify the leaf block by deleting an entry in the initial position, but in our case, the update statement looks like it failing after deleting the entry and during inserting.

Can someone please tell me whether I am correct or I am missing anything here.

Thanks,
Vishnu

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Nov 23 2019 - 10:48:02 CET

Original text of this message