Re: Constraint policing - redo generation? transaction failing

Sure Jonathan, I will do the same, for the initial tests i used x$bh to verify that the block is dirty or not..

Thanks,
Vishnu

On Sat, Nov 23, 2019 at 3:18 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l