*** Create a table with a PK policed initially by a Unique index and insert a row


SQL> CREATE TABLE redo_test (id NUMBER, value VARCHAR2(100) PRIMARY KEY);

Table created.


SQL> INSERT INTO redo_test VALUES (1, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

1 row created.

SQL> COMMIT;

Commit complete.


---------

*** In one session, run the following SQL (where sid = sid of other session) before the INSERT statement is run in the other session:

*** NOTE: Some additional rows not of interest have been removed from those displayed


SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and n.name like '%do%' and value <> 0 and s.sid=144;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                           4563
redo size                                                          32299592
undo change vector size                                               42144
rollback changes - undo records applied                                  15
IMU undo allocation size                                               9996
IMU Redo allocation size                                              13176


*** In the another session, run the following INSERT to cause a violation of the Primary Key:


SQL> INSERT INTO redo_test VALUES (1, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO redo_test VALUES (1, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')
*
ERROR at line 1:
ORA-00001: unique constraint (BOWIE.SYS_C0048291) violated



*** Back in the first session, run the select again. Note the figures in () represent the differences in values between the select statements


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                           4567     (4) 
redo size                                                          32300380   (788) 
undo change vector size                                               42248   (104)
rollback changes - undo records applied                                  16     (1)
IMU undo allocation size                                              10364   (368)
IMU Redo allocation size                                              13672   (496)


*** Note specifically that redo entries has increased by 4 and redo size has increased by 788.


----------------

*** Now repeat demo again, this time with the PK using a non-unique index.


SQL> DROP TABLE redo_test;

Table dropped.

SQL> CREATE TABLE redo_test (id NUMBER, value VARCHAR2(100) PRIMARY KEY USING INDEX (CREATE INDEX redo_test_i ON redo_test(value)));

Table created.

SQL> INSERT INTO redo_test VALUES (1, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

1 row created.

SQL> COMMIT;

Commit complete.


---------

**** In one session, run the following (where sid = sid of other session):


SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and n.name like '%do%' and value <> 0 and s.sid=144;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                           4687
redo size                                                          32330364
undo change vector size                                               50524
rollback changes - undo records applied                                  16
IMU undo allocation size                                              11140
IMU Redo allocation size                                              13672


*** In the another session, run the following INSERT to again cause a violation of the Primary Key:


SQL> INSERT INTO redo_test VALUES (1, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO redo_test VALUES (1, '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')
*
ERROR at line 1:
ORA-00001: unique constraint (BOWIE.SYS_C0048291) violated



*** Back in the first session, run the select again. 

*** Note the figures in the first () represent the differences in values between the select statements. 

*** The figures in the second () represent the differences between the Unique and Non-Unique index


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                           4695    (8)        (4)
redo size                                                          32333044 (2680)     (1892)  
undo change vector size                                               51340  (816)      (712)
rollback changes - undo records applied                                  21    (5)        (4)
IMU undo allocation size                                              13140 (2000)     (1632)
IMU Redo allocation size                                              15508 (1836)     (1340) 



*** Note specifically that redo entries has increased by 8 (not 4) and redo size has increased by 2680 (not 788).

*** Note also that costs associated with undo have also increased from the first run with the Unique PK