jump to navigation

Primary Keys and Non-Unique Indexes (What’s Really Happening ?) June 4, 2008

Posted by Richard Foote in Constraints, Oracle General, Oracle Indexes, Oracle Myths, Primary Key.
trackback

Based on this OTN thread, it appears there may still be folk out there that think Oracle uses Unique Indexes to police a Primary Key (or Unique Key) constraint. This is of course not necessarily true as since 8.0 and the introduction of Deferrable Constraints, Oracle can just as easily police a PK (or UK) constraint with a Non-Unique Index.

To determine whether a new PK value currently exists or not, Oracle can almost (although not quite) just as easily perform an index look-up using a Non-Unique Index as it can with a Unique Index. Simple index look-up, is the value there, yes or no, proceed as appropriate.

Indeed, I’ve already discussed on these here pages various differences between using a Unique and a Non-Unique Index:

http://richardfoote.wordpress.com/2007/12/18/differences-between-unique-and-non-unique-indexes-part-i/

http://richardfoote.wordpress.com/2007/12/21/differences-between-unique-and-non-unique-indexes-part-ii/

http://richardfoote.wordpress.com/2007/12/30/differences-between-unique-and-non-unique-indexes-part-iii/

However, for those that may never have seen a case where creating a PK constraint has not created a Unique index, I thought it might be an idea to just go through a number of different scenarios when this is all quite possible.

The first example is when there’s already an existing non-unique index that Oracle can use. Oracle will not (and indeed can not) create a Unique Index where an identical Non-Unique index already exists.

SQL> create table bowie as select rownum id, ‘BOWIE’ text from dual connect by level <= 10000;

 

Table created.

 

SQL> create index bowie_i on bowie(id);

 

Index created.

 

SQL> alter table bowie add constraint bowie_pk primary key(id);

 

Table altered.

 

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =  ‘BOWIE_I';

 

INDEX_NAME COL_NAME UNIQUENES CON_NAME C
---------- -------- --------- -------- -
BOWIE_I    ID       NONUNIQUE BOWIE_PK P

A subtle difference to this scenario is where there’s an existing Non-Unique index but it only has the leading columns the same as the PK constraint to be. Again, even if there are additional columns within the index, the fact the leading columns match the PK constraint means Oracle can still use the index to police the constraint as the index must be in the same logical order of these leading columns.

For example:

SQL> alter table bowie drop primary key;

 

Table altered.

 

SQL> drop index bowie_i;

 

Index dropped.

 

SQL> create index bowie_i on bowie(id, text);

 

Index created.

 

SQL> alter table bowie add constraint bowie_pk primary key(id);

 

Table altered.

 

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name = ‘BOWIE_I';

 

INDEX_NAME COL_NAME UNIQUENES CON_NAME C
---------- -------- --------- -------- -
BOWIE_I    TEXT     NONUNIQUE BOWIE_PK P
BOWIE_I    ID       NONUNIQUE BOWIE_PK P

Another example is when the PK constraint is created as being DEFERRABLE. This means the constraint could be used to defer the policing of the constraint until the time of the COMMIT, rather than at the time of the DML statement. As such, for a period of time during a transaction, there could actually be duplicate values for the PK. Not only will Oracle create a Non-Unique index for such a Deferrable constraint, indeed Oracle MUST create a non-unique index. The existance of a previously created Unique Index will actually prevent Oracle from creating PK constraint as deferrable.

SQL> alter table bowie drop primary key;

 

Table altered.

 

SQL> drop index bowie_i;

 

Index dropped.

 

SQL> alter table bowie add constraint bowie_pk primary key(id) deferrable;

 

Table altered.

 

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.table_name = ‘BOWIE';

 

INDEX_NAME COL_NAME UNIQUENES CON_NAME C
---------- -------- --------- -------- -
BOWIE_PK   ID       NONUNIQUE BOWIE_PK P

Of course, another way to create a Non-Unique Index when defining a PK constraint is to simply create the specific index at the same time as the PK constraint. You can get pretty tricky with the create table syntax these days.

For example:

SQL> alter table bowie drop primary key;

 

Table altered.

 

SQL> drop index bowie_pk;

 

Index dropped.

 

SQL> alter table bowie add constraint bowie_pk primary key(id) using index (create index bowie_i on bowie(id));

 

Table altered.

 

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.table_name = ‘BOWIE';

 

INDEX_NAME COL_NAME UNIQUENES CON_NAME C
---------- -------- --------- -------- -
BOWIE_I    ID       NONUNIQUE BOWIE_PK P

The next time you read or hear mentioned Oracle uses a Unique Index to police a PK (or UK) constraint, you now know it might not necessarily be the case.

About these ads

Comments»

1. Tony - June 4, 2008

Awesome as usual. For me, this is one of the best blogs on the interweb …the content just can’t be beat.

2. Amit - June 4, 2008

Excellent Post !! New learning :)

3. Brian Tkatch - June 5, 2008

Thanx Richard, good stuff.

4. Polarski Bernard - June 6, 2008

great reading

5. Richard Foote - June 6, 2008

Hi Folks

Thanks for the nice comments, much appreciated :)

6. Asif Momen - June 6, 2008

Superb !!!

7. SeánMacGC - June 6, 2008

All good Richard, many thanks.

8. lscheng - July 29, 2008

Hi

This sort of indexing techniques is mostly used in Data Warehouse environments

9. Vladimir - August 22, 2008

Hi Richard,

Ok, you showed that Oracle can create the primary key based on non-unique index. But I don’t understand how Oracle uses it if such constraint is needed unique values, but the non-unique index admits non-unique values.

Is it mean that Oracle will prohibit inserting of repeated values in corresponding column after creating ptimary key?
Or Oracle will permit inserting of repeated values, but must do overheads to support uniqueness of the primary key?

Thanks.

10. Brian Tkatch - August 22, 2008

Vladimir. How does a PK work anyway with a UNIQUE INDEX? Basically, when the system looks to put a new values it first checks if it already exists in the INDEX. If it does not yet exist, the new entry is allowed. This policing is done by the INDEX, so the PK itself does not need to do anything.

If the PK is using a non-UNIQUE INDEX, the process is exactly the same–it still checks if the value exists–, the only difference is, the PK “itself” does the checking.

As such, the difference would seem to be not what is done, but who does it.

11. Vladimir - August 22, 2008

Brian Tkatch.

That is, PK as a “structure-object-program” works differently against type of the based index.

Well, it looks like true.. One question only – is it your personal guess or it is a accessible knowledge (where can I read about it)? :)

Thanks.

12. Richard Foote - August 25, 2008

Hi Vladimir

Brian has explained things nicely.

The non-unique index can not admit non-unique values because the unique contraint won’t allow it (unless the constraint is deferrable or novalidated).

Therefore the non-unique index is quite as capable of determining whether a new index entry currently exists or it doesn’t as a unique index when policing the constraint.

13. Vladimir - August 25, 2008

Thanks Richard!

14. Vladimir - August 26, 2008

Hi Richard,

One notice only.

That is, in lighting of all above we can assert that Oracle have two types of PK. On creating PK Oracle detects the type of the index which will work in pair with PK. And then Oracle pick out corresponding type of PK..

Does it look like true?

15. Richard Foote - August 26, 2008

Hi Vladimir

No, that’s not quite correct.

A PK is a PK and without the fancy options, works the same regardless of it being policed by a Unique or Non-Unique Index.

However, a PK can be deferrable, which means the policing of the constraint is deferred until the time of the commit. In which case, it must be policed by a non-unique index.

A PK can also be created as novalidate. Which means the existing data is not checked for correctness when the constraint is created and so existing data can potentially violate the PK constraint.

These options are determined when the constraint is created and so an appropriate index (eg. non-unique index for a deferrable constraint) must be used/created at the time the constraint is established.

16. Vladimir - August 26, 2008

Hi Richard

Thanks for clarification. (One more step to understanding for me..) Yes, I already know about it (thanks to your posts).

But I can’t understand, why PK always use the index in it’s working. My suggestion is because of using of existing structure in the system (it is unnecessary to create extra policying structure) despite of all the index problems.

Is it correct now?

17. sameer - December 11, 2009

Excellent Article. Thank you Richard for this article.
It spared me lots of time to explain it to fellow colleagues when we ran into some issues with PK.

Richard Foote - December 14, 2009

Hi Sameer

Great, glad it was of help. These things can be tricky sometimes.

18. Deepali - January 21, 2010

Thanks Richard. This article helped me to figure why some PK and UK indexes were not created in some of my tables.

Richard Foote - January 24, 2010

Hi Deepali

Great, glad it was of some help :)

19. Book Review: Oracle SQL Recipes: A Problem – Solution Approach « Charles Hooper's Oracle Notes - June 8, 2010

[...] Recipe 18-13, in the Note section, states that when a primary key constraint is created Oracle will create a unique index with the same name as the constraint.  That statement is true unless a unique or non-unique index already exists for the column or a composite index that has that column as its leading column exists.  Later in the recipe it was stated that the DBA could pre-create the index that will be used to help enforce the primary key constraint, but the Note section might cause a little confusion. [Reference/Test Case - Check the Comments Section Also] [...]

20. Linda Du - September 11, 2010

Recently, I found I have primary and unique keys using non-unique indexes by using TOAD database health check. The report list the index/tables, but I am not sure what I should do or if I should do something with it.

Thanks.

Linda

Richard Foote - September 15, 2010

Hi Linda

Likely, you can do nothing.

But if you want to avoid some of the overheads, perhaps use some of the newer 11g hints, if some of these indexes are accessed a very large number of times, all the little savings and the extra flexibility might be worthwhile.

21. Raj - June 2, 2011

Nice Post!!!
I have a related question.
Here is the scenario:

1. The table is composite partitioned range-hash [ range on a date column]
2. The PK that is defined does not include all the partition keys.
Hence, the index on it cannot be local.
3. There is a need to maintain retention on the table , i.e drop partitions based on a moving time window.
4. Dropping partitions renders the Global index used by the PK unusable.[which is to be avoided]

So, I thought of a workaround.
1. I tried creating a non-unique local index including the columns that the PK needs to be define. — it succeeded
2. Create the PK so that it uses the local index. — it failed.

create table test_part
( no number not null,txt varchar2(30) not null,dt date)
nologging parallel 8
initrans 2 maxtrans 255
partition by range(no,dt)
subpartition by hash(txt)
subpartitions 4
(
partition p1_max values less than (1,to_date(’31-dec-9999:00:00:00′,’dd-mon-yyyy:hh24:mi:ss’)),
partition p2_max values less than (2,to_date(’31-dec-9999:00:00:00′,’dd-mon-yyyy:hh24:mi:ss’)),
partition p3_max values less than (3,to_date(’31-dec-9999:00:00:00′,’dd-mon-yyyy:hh24:mi:ss’))
);

02:01:01 SQL> create index test_part_idx on test_part(no,txt) local;

Index created.

02:09:02 SQL> alter table test_part add constraint pk_test_part primary key(no,txt);
alter table test_part add constraint pk_test_part primary key(no,txt)
*
ERROR at line 1:
ORA-01408: such column list already indexed

02:10:14 SQL> alter table test_part add constraint pk_test_part primary key(no,txt) using index test_part_idx ;
alter table test_part add constraint pk_test_part primary key(no,txt) using index test_part_idx
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

However,
If I create the non-unique index without making it local [i.e Global] it allows the creation of the PK using the non-unique index.

02:06:36 SQL> create index test_part_idx on test_part(no,txt);
Index created.
02:06:42 SQL> alter table test_part add constraint pk_test_part primary key(no,txt);
Table altered.

But this does not serve my purpose.
Is there a way to create a local index on the mentioned column and enforce a PK on it?

TIA
—Raj

Richard Foote - June 6, 2011
Raj - June 7, 2011

Thank you Richard.
Seems the limitation cannot be circumvented.
–Raj

22. DOAG2011 – Addenda « Oraculix - November 20, 2011

[...] Potentielle Nachteile: Gute Erläuterung bei Richard Foote [...]

23. yakambram - December 17, 2011

the primary key and unique+not null are not equal because
1.Each table may contain any no.of not null+unique key but sql does not allow more than one primary key per table.
and composite unique+not null is not possible where as we can perform the composite primary key

2.when ever we use the primary key only two database objects are created{(i)primary key name and (ii)default index}
3.we can’t drop unique or not null behavior separately in primary key where as in not null+unique key we can drop the either not null or unique behavior seperately.
4.primary key gives the better performance where not null+unique key gives low performance

Richard Foote - December 19, 2011

Hi Yakambram

Thanks for your comments, although you’ve made a few which perhaps need some clarification :)

Not sure what you mean by composite unique+not null is not possible as you can of course have a composite unique key and all columns can also be defined as not null.

Note also that an index is not necessarily created with the constraint if a viable index already exists.

Finally, not sure what you mean by a PK gives better performance while a not null+unique key gives low performance, why would you suggest that ???

24. yakambram - December 17, 2011

in unique+not null key three database objects are created(unique,default index,not null)

Richard Foote - December 19, 2011

Hi Yakambram

While I’m in a picky mood :)

Constraints are not actually database objects as such and as mentioned in my previous comment, an index is not necessarily created if a viable index for policing the constraint already exists (although I do agree that an index is indeed a database object).
;)

25. Unterbewertet oder unbekannt: Deferred Constraints « Oraculix - March 12, 2012

[...] und Unique Key Constraints werden normalerweise durch Unique Indizes unterstützt. Nicht so im Fall von Deferrable Constraints: Da bei Änderungen an der Tabelle auch die Indizes unmittelbar mit geändert werden, müssen diese [...]

26. Michael - October 24, 2012

Can someone please clarify my understanding:

By definition, a PK contains unique values. A unqiue index on the PK columns will, of itself, ensure that only unique values are included. So, the PK constraint is not necessary to ensure uniqueness, as the unique index will do that. That is, there is no need to check the constraint as the index is enough to ensure uniqueness,

On the other hand, a non-unique index may contain, obviously, non-unique values. So it requires the PK constraint to determine if a value should be allowed in the index. That is, the existence of the PK constraint (and not just the index, as was the case above) is needed to ensure that the values in the index remain unique.

Isn’t that an extra step, checking the constraint, to determine if uniequness is to be checked ?

Richard Foote - October 24, 2012

Hi Michael

Not really because Oracle needs to check the associated constraints (business rules if you like) when performing DML. And not just the PK/Unique constraints but all of them (FK, Not Null, Check etc.). It’s a key step in the processing and ensures not only that the constraint rules aren’t violated, but that meaningful error messages are created if they are.

So the next step after determining such rules are in place is to police the constraints. So in the case of a PK, this checking/policing can be performed as efficiently with a non-unique as it can with a unique index (ie. does an index entry already exist that violates the rule).

Remembering of course that a PK can at times contain non-unique values as well, in the case of a deferrable or non-validated constraint.


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,823 other followers

%d bloggers like this: