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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Vishnu Potukanuma <vishnupotukanuma@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
