jump to navigation

Differences between Unique and Non-Unique Indexes (Part II) December 21, 2007

Posted by Richard Foote in Index Access Path, Index Internals, Indexing Tricks, Oracle Cost Based Optimizer, Oracle Indexes, Primary Key, Unique Indexes.

The most significant difference between a Unique and a Non-Unique index is of course the simple fact that in one index, all index entries MUST be unique and in the other index there can be duplicates of an index entry.

Although an obvious distinction between the two, it’s also a crucial difference as well.

When Oracle uses a Unique Index to scan for a specific value (via an equality predicate on all indexed columns or when policing a constraint ), there can only be one of two possible results. The value can exist returning at the very most one value or the value doesn’t exist returning 0 values. That’s it, 1 row or none. The value either exists or it doesn’t.

This fact means Oracle doesn’t have to worry about a whole bunch of things when dealing with Unique indexes during equality or unique checking processes. It doesn’t have to check the next index entry just in case there’s a second or more entries with the same value. It doesn’t have to worry about the potential of having to skip across to the next leaf page if the specific value it reads happens to be the maximum value in the current leaf page. It doesn’t have to worry about pointers to these “adjacent” leaf blocks changing on it due to block splits. It doesn’t have to concern itself with potentially visiting more than the one table data block during the index access operation.

Life is simple, it’s either 1 row or none.

Not so for Non-Unique indexes. With a Non-Unique index, there are no such guarantees. With a Non-Unique index, there are 3 categories of possibilities. An index scan could return 0 rows, it could return 1 row or it could return more than one row. It could potentially need to go and visit more than the current leaf block to return all the matching rows. It could potentially need to go and visit more than one table block.

Life’s not quite so “simple” for a Non-Unique index.

Note also and most importantly that life gets no easier for a Non-Unique index that polices a PK or Unique key constraint.

Even though there’s a PK or Unique constraint on a column, to Oracle, it’s just another Non-Unique index with the same “vague” possibilities. Remember that PK and Unique constraints can be enabled with NOVALIDATE meaning that even with a PK or Unique constraint, there’s still the possibility of duplicate index entries in the Non-Unique index. Remember constraints can be DEFERRABLE, meaning that even with a PK or Unique constraint, there’s still the possibility of duplicate index entries in the Non-Unique index.

This means that Oracle has to concern itself with a number of additional overheads, including having to “check” the next index entry, “just in case” it matches the required index value. It has to concern itself even with the possibility of having to visit the next index leaf block, “just in case”.

You will note when Oracle performs an equality search using a Unique Index, Oracle will perform an “INDEX UNIQUE SCAN” because the index entries MUST be unique.

You will note however when Oracle performs an equality search using a Non-Unique index, even if there’s a PK or Unique constraint of the column(s), Oracle will perform an INDEX RANGE SCAN, because it needs to scan multiple index entries “just in case”.

So are there any actual implications as a result of any of this ?


When Oracle actually reads an index and processes the associated blocks in the buffer cache(s), Oracle uses a number of latches. These latches are used primarily to “protect” memory structures from concurrent activity. Very simplistically, by grabbing a latch, Oracle effectively performs a “lock” on the associated memory structure, perform whatever activity needs to be performed and releases the latch. These latches get grabbed and released (hopefully) extremely quickly (order of 1/10s of ms), but it’s a non zero value.

The issue with latches is that they’re a point of serialisation. If two (or more) processes want a specific latch, one (or more) has to wait. Latches also burn CPU. Only a teensy weeny bit at a time but some CPU nonetheless. They burn CPU while acquiring the latch and if fail due to latch contention, while attempting again and again to acquire the latch. They also burn CPU while performing the specific operation necessary of the latch.

Basically, the more latches, the greater the potential for contention, the greater the potential for latch related wait activity and perhaps most important of all, more CPU is required. In busy systems, there can be massive numbers of latch events and the best way to tune these events is to reduce where possible the number of latches required by the database environment. It’s one of the key reasons we try and reduce LIOs in a database as much as possible, to reduce the latch and CPU load on the system.

Because of the differences highlighted between Unique and Non-Unique indexes, the number and manner of latches required between the two indexes differs. And it differs significantly …

In this little demo, Latch Differences Between Unique and Non-Unique Indexes Demo, we compare the latches required to read an identical table, using a 2 level index. The  differences between the latch overheads of a Unique and a Non-Unique index are most interesting.

When using a Unique Index, Oracle required 3 consistent gets (one for the index root block, one for the leaf block and one for the table block). BUT, each consistent get was a consistent gets – examination, a special type of consistent get which only requires 1 latch (rather than the standard 2 latches).

So that’s a sum of 3 latches.

However, when using a Non-Unique index, Oracle required 4 consistent gets (one for the index root block, one for the leaf block, one for the table block and an additional one to recheck the leaf block for any duplicate index entries). BUT, only the 1 consistent read (for the index root block) was actually the “cheaper” consistent gets – examination, the other 3 were the more costly 2 latch variety.

So that’s a sum of 7 latches.

3 latches for the Unique index and 7 latches for the Non-Unique index.

That’s an increase of 133.3% in latches between the two types of indexes.

Now, the height of the index will change the ratio of latch difference between the two indexes. Also, in a busy system, there could potentially be differences in the types of latches used due to the current state or additional activity in a block.

However, the potential difference in latch requirements between a Unique or Non-Unique index can be very significant. But does a few additional latches here and there really make much of a difference ?

Well, of course it depends. On small scale systems with smaller loads, fewer indexes, fewer users and excess resources, the noticeable differences may be negligible.

However, in larger scale (especially OLTP) environments, a particular index may be accessed 100s or maybe 1000s of times a second. There may be 1000s of tables with 1000s of corresponding PK and Unique constraints policed by 1000s of Unique (or Non-Unique) indexes. It’s therefore not really of question of a few latches here or there. It’s a question of potentially a very significant proportion of overall latch related overheads.

Potentially when accessed, Non-Unique indexes could be generating double the latch related overheads for equality unique scan or unique checking index activity. Remember, the best way to tune latches and reduce latch contention is to simply reduce the requirement and load for latches.

The overall reduction in CPU and latch related wait activity could be significant between Unique and Non-Unique indexes because by using Non-Unique indexes you in the order of double the latches required for such activities.

Note also this doesn’t require any special parameters to be set or special tuning or monitoring by the DBA. It simply requires using Unique indexes to police PK or Unique constraints when there are no requirements of Non-Unique indexes. You then potentially gain a benefit each and every time the index is used for unique scan accesses.

Guess what type of access is extremely common in large scale OLTP environments …

The next time you complain about high CPU consumption or high latch contention and you’re tuned the application to death, just ask yourself how many Non-unique indexes are policing your PK or Unique Key constraints …

About these ads


1. Nigel Thomas - December 21, 2007

Just for clarity, it’s worth saying that these comparisons only apply when all else is equal – ie when you are using all the PK/UK constraint columns. If you have a unique index on (A,B,C) but your predicate only sets values for A and B, that’s a non-unique access (ie a range scan), with all the “next key” issues you mentioned.

Regards Nigel

2. Alberto Dell'Era - December 21, 2007

I’ve run your test case in and the results are exactly the ones you described – just to stress that what you have said is extremely relevant also for this state-of-the art release.

A maybe interesting observation is that the constraint policed by the non-unique index (second part of your test case) is NON DEFERRABLE and VALIDATED, hence Oracle might, in principle, know that it is going to find at most one row, so it could, again in principle, use the same optimization (INDEX UNIQUE SCAN) used for the unique index .

I wonder why the kernel doesn’t do it – I mean, whether they simply haven’t implemented it (yet), or if there are other reasons that might prevent such an optimization from working (maybe, perhaps, because there could be two index entries with the same pk value when two different transactions try to insert two rows with the same pk value at the “same time”, until one transaction end, if I recall correctly ? But shouldn’t it be the same for unique indexes ?).

It would be quite an important, relevant in real-life optimization: if you have a lot of queries such as
“select name from ziggy where id = …;”
it is natural to consider creating an index on (id,name) and then the PK constraint, thus putting us in the non-unique index, NOT DEFERRABLE and VALIDATED PK scenario.

BTW, this blog entry is one of the best I’ve seen in years: clear, concise, to the point, relevant. In my opinion – great work.

3. dizwell - December 21, 2007

I think the moment we have to scare readers witless with references to “a 133% increase”, when in fact it’s a difference between 7 and 3 latches, we are straining to make a point!

And yes, if you postulate a hugely-busy OLTP environment in which thousands of accesses are taking place per second, you can undoubtedly scale the thing up to worrying-looking figures quite easily.

It is fairly obvious, I think, that trivial*vast quantities=fairly high quantities. And if you are indeed running one of those sorts of environments where there are vast quantities of concurrent index accesses, perhaps -maybe even ‘probably’- unique indexes will make the necessary difference between comfort and discomfort. As Alberto put it, it would indeed then be an important, relevant optimization to keep in mind.

But I’d not expect that to be true for most people running Oracle databases, for whom the tiny increase in cost of non-uniques would not be a cause for concern. Without the scaling factors you cite, in other words, it’s a non-issue.

You’re again counselling taking prescription medicines, preventatively, just in case. I myself would wait until I’d diagnosed that latch contention or high CPU was actually an issue (or could intelligently forecast that they would likely be a problem) before I’d start taking these particular pills (which, after all and as you acknowledged in Part 1, do indeed come with sometimes severe side-effects).

And that’s especially true since, as Nigel points out, even by taking the unique pills, you don’t make this “huge” increase in latching disappear in all circumstances.

4. Richard Foote - December 21, 2007

Hi Nigel

Yes thank-you, I’m perhaps not clear enough on that point. These are equality, *unique* scans and policing of PK and Unique constraints where Oracle makes these assumptions.

5. Richard Foote - December 21, 2007

Hi Alberto

Thanks for your kind comments, they’re much appreciated.

I think Oracle sees the checking on the current state of a constraint when a Non_unique index is referenced each and every time it performs a hard parse as too much of an overhead.

That’s what Unique indexes are for. With a Unique index, Oracle doesn’t have to make any assumptions or perform any additional checking. If the CBO “sees” a Unique index as a viable execution path, it can immediately without any additional checking make these sorts of assumptions. If it only has Non-Unique indexes, then it can’t.

6. Richard Foote - December 21, 2007

Hi Howard

Note that an increase from 1 to just 2 is only just a tiny little 1. But that’s double, or a 100% increase. So the increase is of course all relative.

If you performed something 1 billion times, you now perform something 2 billion times, an increase of 1 billion. Maybe that tiny little 1 is not quite so little …

If you can avoid doing something (say) 1 billion times, if you end up increasing the workload of an activity by 100% unnecessarily, perhaps the point isn’t being so strained after all.

Of course, you’re welcome to your opinion.

Hope you have a lovely time over Xmas and safe new year.

7. dizwell - December 21, 2007

That was my point, of course. The 100% increase is trivial… **unless** you scale it by some huge number (such as 1 billion). Without that scaling factor, you’re left with just ‘trivial’.

Just publishing the headline 100% is, I think, straining things, unless the ‘do you have such scaling factors? If not, don’t worry!’ caveat is made clear.

8. Richard Foote - December 21, 2007

Hi Howard

You don’t think where I say:

“But does a few additional latches here and there really make much of a difference ?

Well, of course it depends. On small scale systems with smaller loads, fewer indexes, fewer users and excess resources, the noticeable differences may be negligible.”

makes that very point ?

9. dizwell - December 21, 2007

Actually, I was going to add another post which said, ‘Incidentally, I think you probably did make it fairly clear, but I’d make it even clearer’, and then thought I was probably posting here too often, so didn’t.

10. Alberto Dell'Era - December 21, 2007

“I think Oracle sees the checking on the current state of a constraint when a Non_unique index is referenced each and every time it performs a hard parse as too much of an overhead.”

Yes, that could be the reason; even if a well designed system shouldn’t perform a lot of hard parses, if any.

For soft parses, when a constraint is NOT DEFERRABLE, the only way to flip/flop between ENABLED/DISABLED, and VALIDATED/NOT VALIDATED, is to ALTER the constraint – the latter being DDL, it invalidates the plan, hence there’s no reason to check at soft parse time (or execution time, of course). And altering a constraint is for sure a maintenance operation, or at most done routinely only once a day during the data load in DWHes.

Another reason could be that unique indexes were born much before unique constraints, hence the CBO code doesn’t exploit the latter as much as the former. Ok, well, I think I’m switching too much into guess mode – even if this sort of ruminations sometimes make for new interesting discoveries.

11. Gary - December 22, 2007

“If you have a unique index on (A,B,C) but your predicate only sets values for A and B, that’s a non-unique access (ie a range scan), with all the “next key” issues you mentioned.”
Sometimes you might think about adding a column to an index so that a query doesn’t have to visit the base table. If the original index is unique (say on one ‘id’ column) and you add a second ‘val’ column, then all searches on ‘id’ will be negatively impacted. That would be something to consider (and test for) when considering this sort of change.

12. Richard Foote - December 22, 2007

Hi Alberto

I agree Oracle could be a little cleverer with regard to how it actually deals with non-unique indexes and constraints they police. I suspect it’s likely a historical thing as well or there’s simply a really good reason ;)

Merry Xmas

13. Richard Foote - December 22, 2007

Hi Gary

Very good point. More usually it’s a non-unique index where extra columns get added but I have known of Unique indexes as well.

Just something else that needs to be considered in the whole scheme of things when one is configuring an environment to be as optimal as possible.

Merry Xmas

14. Robert - December 22, 2007

Richard, thanks for these insights!

This reminds me of an application I once was working on. Basically it was a reporting application on top of a star schema but not a pure DWH. The difference was that there was data coming into the DB continuously. We changed unique indexes on dimension tables to non unique to allow for concurrent inserts.

There would be a CREATE INDEX user_idx ON user_table(name) and then SELECT MIN(user_id) FROM user_table WHERE name = :1. If two processes accidentally would be looking for the same name two entries would be made but that way inserts were more independent and reports would still work because grouping would be done on the “name” column.

This seemed the best solution vs. catching an error and redoing inserts or other more complicated measures. We never evaluated whether there were any other performance penalties but with the relatively low likelihood of these kinds of collisions the overhead in rows was not too big.

So, as always, it all depends…

Merry Christmas and a great 2008!

15. Richard Foote - December 23, 2007

Hi Roberts

No worries, glad you find some things useful in here.

Absolutely it always depends. Hopefully by highlighting some of these things, more people will understand some of these factors better and appreciate on what things actually depend on.

Probably early in the New Year, I’ll discuss how the rollback mechanism differs between Unique and Non-Unique indexes and how Non-Unique indexes can potentially generate more undo in situations not unlike what you’re described.

Thanks for your comments, have a great Xmas.

16. Differences between Unique and Non-Unique Indexes (Part III) « Richard Foote’s Oracle Blog - December 30, 2007

[...] December 30, 2007 Posted by Richard Foote in Oracle Indexes. trackback A comment by Robert in Part II of this series reminded me of another subtle difference between Unique and Non-Unique [...]

17. Jonathan Lewis - January 12, 2008

Another difference – possibly rather important – between unique and non-unique indexes is that the optimizer can use the distinct_keys value from a unique index as a ‘sanity check’ in the optimizer arithmetic even when it decides not to use the index in the execution path.

Change an index (that isn’t being used) from unique to non-unique (or vice versa) and a join cardinality may change, which could give you a different execution plan which still doesn’t use the index.

Jonathan Lewis

18. Richard Foote - January 13, 2008

Hi Jonathan

Indeed, thank you for the example. The CBO deals with unique indexes differently from non-unique indexes, even with associated constraints in place and can make important assumptions about the data. The more info the CBO has, generally the better and it’s one of the reasons for my preference to use Unique Indexes unless there’s a strong business rule or requirement for the contrary.

BTW, congratulations on reaching 500,000 hits on your blog. Here was I feeling pleased for myself for reaching 10,000 ;)

19. Girish Sharma - August 25, 2013


I am not able to get below text file :
to see, how latch difference in unique and non-unique index. Actually, I am not getting the easy meaning of “Highlights the fact Non-Unique indexes have in the order of double the associated latch overheads of Unique indexes”, probably non-native English speaking!

Girish Sharma

20. Richard Foote - September 11, 2013

Hi Girish

WordPress stopped supporting the importing of plain text files which has caused me grief with these older posts. As people complain, I try and convert them to PDF. So the link above should work now :)

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


Get every new post delivered to your Inbox.

Join 1,713 other followers

%d bloggers like this: