jump to navigation

Differences between Unique and Non-Unique Indexes (Part III) December 30, 2007

Posted by Richard Foote in Constraints, Index Internals, Oracle Indexes, Performance Tuning, Unique Indexes.
trackback

A comment by Robert in Part II of this series reminded me of another subtle difference between Unique and Non-Unique Indexes. Now this difference is likely to be of minimal consequence to most applications as most applications don’t generally have problems with Primary Key (PK) or Unique Key (UK) constraint violations (and if they do, this is likely to be the least of their worries). But it’s a interesting difference nonetheless, something to keep in the back of your mind and a little tit-bit to end the year on.

When a row is inserted into a table or when a PK or UK is modified, Oracle of course needs to ensure that either the PK or UK constraint is not violated. If the constraint is policed via a Unique index, as previously discussed, Oracle knows the value must and can only ever be unique and so performs the constraint validation before the Unique index is actually modified. If the PK or UK is violated, the Unique index can not possibly have been changed as all the associated index entries must always be unique and so only the undo (and redo) of the changes associated with the table data blocks are actually generated and need to be subsequently rolled back.

However, if the PK or UK constraint is policed via a Non-Unique index, the mechanism for applying the changes differs somewhat. As the index is Non-Unique, as previously discussed, Oracle is not quite so certain as to the state of play and performs the constraint validation after the associated changes are made to the Non Unique index. If the PK or UK constraint is violated, both undo and redo of the Non-Unique index has been generated and both changes to the table data blocks and the index blocks need to be rolled back.

This means there’s an extra cost associated with violating a constraint if the constraint is policed via a Non-Unique Index vs. a Unique index. When performing media recovery, it also means that there’s an additional cost associated with performing the recovery. Obviously the more frequent the constraint violations, the greater the overall penalties. Also, the larger the PK or UK values, the greater the penalties.

See this little demo to illustrate the differences between a Unique and a Non-Unique index in the redo and undo generated when a constraint is violated: Difference in redo and undo between a Unique and a Non-Unique Index.

As mentioned, this difference in behaviour between Unique and Non-Unique Indexes is unlikely to be an issue. However, in applications or environments where there may be a significant number of such violations, it may be something to keep in the back of your mind.

For a more detailed discussion and where it could be an issue, see Eric Emrick’s presentation.

About these ads

Comments»

1. Asif Momen - July 27, 2008

Hi,

Great stuff indeed.

One can use the following query to figure out any PK’s or UK’s using a Non-Unique index.

select uc.constraint_name,
uc.constraint_type,
uc.index_name,
ui.uniqueness
from user_constraints uc,
user_indexes ui
where uc.index_name = ui.index_name
and uc.constraint_type in (‘P’, ‘U’)
and ui.uniqueness = ‘NONUNIQUE’;

Link to Eric Emrick’s presentation is no more working. Is there any other place where I can read this?

Regards

2. Richard Foote - July 28, 2008

Hi Asif

Link still works for me ?

3. Asif Momen - July 29, 2008

Hi Richard,

Yea… it’s ok now….

Thanks

4. zhwsh - September 1, 2011
5. zhwsh - September 1, 2011

https://richardfoote.files.wordpress.com/2007/12/redo-demo-version-2.txt
— 403: Access Denied —

This file requires authorization:

You must both be a user of this blog as well as be currently logged into WordPress.com


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,704 other followers

%d bloggers like this: