The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question, narayanan.

Asked: March 18, 2005 - 7:58 am UTC

Last updated: November 01, 2013 - 9:30 pm UTC

Version: 10g

Viewed 50K+ times! This question is

You Asked

from the oracle10g concepts guide

"Oracle recommends that unique indexes be created explicitly, and not through
enabling a unique constraint on a table.
Alternatively, you can define UNIQUE integrity constraints on the desired columns.
Oracle enforces UNIQUE integrity constraints by automatically defining a unique
index on the unique key. However, it is advisable that any index that exists for
query performance, including unique indexes, be created explicitly."

what's the difference between creating unique constraint while creating table
(any way unique constraint will create unique index to enforce integrity)
and creating unique index explicitly?

and Tom said...

Thanks, I filed a documentation bug on that erroneous text in the concepts guide! It reads:

chapter 5 of the concepts guide states:

<quote>
Oracle recommends that unique indexes be created explicitly, and not through
enabling a unique constraint on a table.

Alternatively, you can define UNIQUE integrity constraints on the desired
columns. Oracle enforces UNIQUE integrity constraints by automatically
defining a unique index on the unique key. However, it is advisable that any
index that exists for query performance, including unique indexes, be created
explicitly.
</quote>

A unique constraint does not necessarily create an index.
A unique constraint does not necessarily create a UNIQUE index.

ops$tkyte@ORA9IR2> create table t( x int, y int );
Table created.

ops$tkyte@ORA9IR2> create index t_idx on t(x,y);
Index created.

ops$tkyte@ORA9IR2> alter table t add constraint t_unique unique(x);
Table altered.

ops$tkyte@ORA9IR2> select index_name from user_indexes where table_name =
'T';

INDEX_NAME
------------------------------
T_IDX

ops$tkyte@ORA9IR2> drop index t_idx;
drop index t_idx
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

That shows

a) unique did not create an index
b) unique does not need a unique index (deferrable unique constraints, if
they create an index -- it is NOT a unique index, just a "index")

I suggest the text read something like:

If you want a unique index in place, it is suggested you explicitly create it
using CREATE UNIQUE INDEX. A primary key or unique constraint is not
guaranteed to create a new index, nor is the index they create guaranteed to
be a unique index. Therefore, if you desire a unique index to be created for
query performance issues, you should explicitly create one.

Rating

(21 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

unique index

narayanan, March 19, 2005 - 2:21 am UTC

thanks tom,

got clarified regarding unique index

Stefano giostra, March 23, 2005 - 5:43 am UTC

Unique Index

hareesh, March 15, 2006 - 9:32 am UTC

Thanks for this thread

unique constraint\index - still confused

Shai Itzkovitch, April 02, 2006 - 11:57 am UTC

Hi, I did not manage to understand when a unique index is NOT created as an outcome of unique constraint definition (besides defferable constraint which creates a non-unique index). Tom, the example you provided just prooved that a unique index was created as a result of the constraint and that it can not be dropped.
Can you please clarify this issue? (any example that shows index is not created would be perfect!).

April 02, 2006 - 1:28 pm UTC

that example proved NO SUCH THING.

please re-read it.

In fact, my stated conclusions as a result of this example where:

That shows

a) unique did not create an index
b) unique does not need a unique index

the very first example shows the addition of a unique constraint, on a table with NO unique indexes, that DID NOT create a unique index (or in fact any index at all) of its own.

unique constraint\index - continued

Shai Itzkovitch, April 02, 2006 - 12:05 pm UTC

My previous comment assumes, there are currently no indexes defined on any combination of the columns which are subject to the unique constraint.

April 02, 2006 - 1:30 pm UTC

then you were talking about a non-existent example!!!

if you create a unique constraint
AND
the index does not already have an index that can be used for the enforcement
of the constraint
THEN
if the constraint is non-deferrable, Oracle will create a unique index
else it'll create a non-unique index
end if

unique constraints do not need unique indexes
unique constraints may or may not create an additional index on the table.

Is this a proper way of enforcing PRIMARY KEY??

reader, April 02, 2006 - 2:53 pm UTC

Hello Tom,

While reading the topic I got confuse about PRIMARY KEY!!

I know we were talking about UNIQUE Index but still..

So what is the best way of implementing PK out of following 2 options?

(1) Create table and define PK constraint while creating the table or after creating table use ALTER TABLE to add PK constraint. This will implement business rule of NOT NULL as well as will create unique index of PK column (Am I missing anything?I considered this as 1st option only!!)
(2) While creating table define column as NOT NULL and then create UNIQUE index using CREATE UNIQUE INDEX ...
command.(I hve never used this option for implementing PK)

Cheers,

April 03, 2006 - 7:59 am UTC

the only way to enforce a primary key is.....

via a primary key constraint.

Nothing else is a primary key.

option 2 above for example is a set of columns that are not nullable. They happen to have a unique index on them, but so what.

If you have a primary key, please call it that!

may be a basic question on Unique index implementation

Reader, January 18, 2007 - 3:30 pm UTC

Tom,

I have a question on the basic implementation of UNIQUE index by Oracle.

1. I have created a table with 5 columns.
2. Table has a PK
3. Has a UNIQUE index on two columns -- these two columns are used in queries.
4. Session 1 inserts data into this table, does not commit or rollback.
5. Session2 tries to insert the same data into the table and it waits ... until the first one commits or rollsback.
6. While session2 was waiting, session three can go and insert a different set of data into the table successfully.

My question is, how did Oracle know to wait for session 1 to commit/rollback before doing anything from session 2. It must have somehow known that duplicate data is getting into the table.

Session3 does not have any issues becuase it is not trying to insert duplicate data.

Would you please explain?

Implicit and Explicit creation of unique index

A reader, March 07, 2008 - 6:01 am UTC

Hi Tom,

Occasionally, i came across the documentation describing the unique constraint.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/clauses002.htm#i78179
.....
When you specify a unique constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement.
.....

I went through the following test cases.

Test Case 1:

SQL> create table t (x number constraint tx_uk unique);

Table created.

SQL> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
TX_UK

SQL> select constraint_name from user_constraints where table_name = 'T';

CONSTRAINT_NAME
------------------------------
TX_UK

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TX_UK) violated

Test Case 2:

SQL> drop table t cascade constraints;

Table dropped.

SQL> create table t (x number);

Table created.

SQL> create unique index uk_tx on t (x);

Index created.

SQL> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
UK_TX

SQL> select constraint_name from user_constraints where table_name = 'T';

no rows selected            => i.e. no constraint is present

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (ENGV.UK_TX) violated

I want to ask,

1. As you mentioned that you've filed a documentation bug for this piece of text. It's still around, even in 11g docs.!

In test case 1 unique constraint is implemented by inline specification. Therefore an implicit unique index and a unique constraint is created and hence insert statement shows that uniqueness is implemented. Test case 2 uses out-of-line specification method, unique index is created explicitly and no unique constraint is declared and hence there is no constraint in user_constraints. However the insert statements shows that uniqueness is enforced.

2. How uniqueness is maintained when there is no constraint defined in test case 2?
3. Isn't uniqueness maintained by a unique constraint?
4. Is unique index enough to do the job alone without constraint?
5. Then why in test case 1 both constraint and index are used to do the job?

Regards.

March 10, 2008 - 10:11 am UTC

I am working on the next release of the concepts guide and have absolutely corrected that text.

.....
When you specify a unique constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement.
.....

it is not accurate.

Oracle MIGHT create an index, then again it might not have to.

1) umm, well of course uniqueness is 'enforced', you have a unique index - not sure what the goal of #1 was...

2) you have a UNIQUE INDEX - meaning - it has to be unique? (sorry, but this seems "obvious"?)

3) the correct way to assert "this set of attributes must be unique" is via a unique constraint yes. But a unique index has a side effect - of - making things unique. But it is not a unique constraint, it is a unique index.

4) a unique index will enforce uniqueness - however - it will not be sufficient for foreign keys and other things. the only RIGHT way to do this is a unique constraint.

5) because the mechanics, the physics behind unique enforcement involves an index. The constraint is the metadata, the index is the physical thing that "does it"

the database needs BOTH - the metadata and the index.

Implicit and Explicit creation of unique index

A reader, March 10, 2008 - 1:21 am UTC

Hi Tom,

Would you like to have a followup for the above post?

Regards.

March 10, 2008 - 11:33 am UTC

no, I wouldn't - but only because I already knew the answer. (and they were a bit, well, if you ask me - "obvious" - a unique index, and asking "why is it unique"? anyway...)

However, it seems you would - and you know - I already did a followup before seeing this.

I too - occasionally - take the weekend off. That, and I have a day job.

Unique constraint/index standard?

greg, April 24, 2008 - 3:57 pm UTC

Hi tom,

I was curious if there is a recommended order/method (by you) of creating the unique index/constraint on a new table.

I know this may sound "trivial", but I'm just curious, I'd like to avoid duplicating any work (ie indexes/constraints), but I do have a table with a requirement of a Unique Constraint.

For example:

create table t ( pkid number, uid number, junk varchar2(10) );

alter table t add constraint t_pk primary key (pkid);

alter table t add constraint t_uq unique (uid);

... but that goes and creates an index named "t_uq".
Our naming standards demand I create the index named "t_ind1"

... but if I do this instead:

alter table t add constraint t_pk primary key (pkid);

create unique index t_ind1 on t ( uid );

.. it creates a constraint named "t_ind1" .. out naming standards .. well, you get the idea .. :\

I checked some of the syntax for these, but I'm not seeing it ... (probably can't see it for looking at it).

Do you have any preferred order or method in creating these suckers so that you can have the Unique constraint (if and when you want it), and the unique index (if and when you want it) ??

(obviously you've covered the pre-reqs earlier, I am NOT asking for a unique constraint with no index, I understand it needs ... "something", and vice versa ... no sweat ... it'll be there, I just want more control of the names ?)

Thanks!!

April 28, 2008 - 12:29 pm UTC

ops$tkyte%ORA10GR2> create table t
  2  ( pkid number constraint t_pk primary key using index ( create index t_pk on t(pkid) ),
  3    "UID"  number constraint t_uq unique using index ( create index t_ind1 on t("UID") ),
  4    junk varchar2(10)
  5  )
  6  /

Table created.

hopefully, you do not use "UID" in real life.... but just use the create index clause of the constraint if you want.

Thanks!

Greg, April 28, 2008 - 3:12 pm UTC

"hopefully, you do not use "UID" in real life"

Nope, it was just a over-simplified example.

Thank-you!!

For some reason, I always have trouble wrapping my head around the (simple) logic of the create table statement .. *sigh* ... (analytics - no problem, Model clause - no sweat, create table? what? huh? ) ;)

UNIQUE: constraint vs index

adderek, January 19, 2010 - 6:38 am UTC

Hi Tom,

You have specified that we should create a unique constraint to ensure uniqueness of a columnset instead of creating unique index only.
How would we deal with the following situation then? (tested on Oracle 10.2G).

create table t (c timestamp with time zone);
create unique index t_ui on t(c);
insert into t values(timestamp'2009-01-01 00:00:00+00:00');
insert into t values(timestamp'2009-01-01 00:00:00+00:00');
-- ORA-00001 Unique constraint (USER.T_UI) violated
drop table t;

create table t (c timestamp with time zone);
alter table t add constraint c_u unique (c);
-- ORA-02329 column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key
drop table t;

Seems that we have the constraint created for the first block but we are not able to create it in the second block.
How can we explicitly create a unique constraint on timestamp with time zone?

January 19, 2010 - 4:54 pm UTC

this is in process bug #3980704

the unique index is not to be allowed, the bug is the accidental creation of that index is allowed

Bug #3980704

A reader, January 20, 2010 - 4:53 am UTC

Tom,

You wrote
"this is in process bug #3980704".

and I am asking:
1. Where can I get the details of this bug from?
2. Is there any way to create UNIQUE constraint on a TIMESTAMP WITH TIME ZONE column in the way it currently works for that unique index?
Regards

January 20, 2010 - 11:35 am UTC

1) support
2) do not use the unique index as that will STOP working at somepoint.

The problem is:

insert into t values(timestamp'2009-01-01 00:00:00+00:00');
insert into t values(timestamp'2009-01-01 01:00:00+01:00');

are the same, but they are not the same. Are they the same? They are the same time - but they are different - they are different times in their time zone. We don't know if they are the same or not - it is ambiguous.

You could create a virtual column in 11g that normalizes the timezone if that is your goal and constrain that - OR - create a unique function based index before 11g to achieve the same.

Unique constraint on timestamp with time zone - is it possible?

adderek, January 21, 2010 - 4:09 am UTC

Hi Tom,
I have tried the following options:

CREATE TABLE t (c TIMESTAMP WITH TIME ZONE);
INSERT INTO t VALUES(TIMESTAMP'2009-01-01 01:00:00+01:00'); 
COMMIT;

1. SYS_EXTRACT_UTC

ALTER TABLE t ADD CONSTRAINT c_u UNIQUE (SYS_EXTRACT_UTC(c));
-- ORA-00904: : invalid identifier

2. timezone AT

ALTER TABLE t ADD CONSTRAINT c_u UNIQUE (c AT TIME ZONE 'GMT');
-- ORA-00904: : invalid identifier

3. own function

CREATE OR REPLACE FUNCTION f
(c_in IN TIMESTAMP WITH TIME ZONE) RETURN TIMESTAMP IS
BEGIN
   RETURN SYS_EXTRACT_UTC(c_in);
END f;
/
ALTER TABLE t ADD CONSTRAINT c_u UNIQUE (f(c));
-- ORA-00904: : invalid identifier

4. invalid identifier... have I really specified non-existing column?!?

SELECT c FROM t;

C             
------------- 
2009-01-01 01.00.00.000000000 +01:00

5. As far as I know my Oracle is 10.2... but I should make sure for the sake of this comment:

SELECT * FROM V$VERSION
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

So please, tell me, how can I create function based unique index on a TIMESTAMP WITH TIME ZONE column that would work as the "buggy unique index" is working currently?

January 21, 2010 - 8:42 am UTC

I said:

11g - virtual column + unique constraint

10g and before - unique function based index.

you are in 10g, trying to use a constraint, but a constraint cannot be used, we'll have to use an unique index on a function.

ops$tkyte%ORA10GR2> create table t ( x timestamp with time zone );

Table created.

ops$tkyte%ORA10GR2> insert into t values(timestamp'2009-01-01 00:00:00+00:00');

1 row created.

ops$tkyte%ORA10GR2> insert into t values(timestamp'2009-01-01 01:00:00+01:00');

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select x, sys_extract_utc(x) from t;

X
---------------------------------------------------------------------------
SYS_EXTRACT_UTC(X)
---------------------------------------------------------------------------
01-JAN-09 12.00.00.000000 AM +00:00
01-JAN-09 12.00.00.000000 AM

01-JAN-09 01.00.00.000000 AM +01:00
01-JAN-09 12.00.00.000000 AM


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create unique index t_unique on t(sys_extract_utc(x));
create unique index t_unique on t(sys_extract_utc(x))
                                                  *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


ops$tkyte%ORA10GR2> delete from t;

2 rows deleted.

ops$tkyte%ORA10GR2> create unique index t_unique on t(sys_extract_utc(x));

Index created.

ops$tkyte%ORA10GR2> insert into t values(timestamp'2009-01-01 00:00:00+00:00');

1 row created.

ops$tkyte%ORA10GR2> insert into t values(timestamp'2009-01-01 01:00:00+01:00');
insert into t values(timestamp'2009-01-01 01:00:00+01:00')
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_UNIQUE) violated

In 11g, you can do this:

ops$tkyte%ORA11GR1> create table t ( x timestamp with time zone , y as (sys_extract_utc(x)) );

Table created.

ops$tkyte%ORA11GR1> pause

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into t(x) values(timestamp'2009-01-01 00:00:00+00:00');

1 row created.

ops$tkyte%ORA11GR1> insert into t(x) values(timestamp'2009-01-01 01:00:00+01:00');

1 row created.

ops$tkyte%ORA11GR1> alter table t add constraint t_unique unique(y);
alter table t add constraint t_unique unique(y)
                             *
ERROR at line 1:
ORA-02299: cannot validate (OPS$TKYTE.T_UNIQUE) - duplicate keys found


ops$tkyte%ORA11GR1> delete from t;

2 rows deleted.

ops$tkyte%ORA11GR1> alter table t add constraint t_unique unique(y);

Table altered.

ops$tkyte%ORA11GR1> insert into t(x) values(timestamp'2009-01-01 00:00:00+00:00');

1 row created.

ops$tkyte%ORA11GR1> insert into t(x) values(timestamp'2009-01-01 01:00:00+01:00');
insert into t(x) values(timestamp'2009-01-01 01:00:00+01:00')
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_UNIQUE) violated

Difference between UNIQUE INDEX, UNIQUE COSNTRAINT, PRIMARY KEY

Matthias Schulz, March 15, 2010 - 6:21 am UTC

Hello Tom!

Our application developers ask me from time to time the following questions and I would like to respond with more insight than just "because you should":

a) Why do I need a unique constraint when I already have a unique index?

b) Why do I need a primary key when I already have a unique constraint?

I would be glad if you could aswer these questions by explaining the relevant internal differences for the Oracle Database.

Best regards,
Matthias Schulz

March 15, 2010 - 11:27 am UTC

a) because a constraint says more, it says what you mean We can use either a unique OR non-unique index to support a unique constraint. Constraints are metadata, more metadata is good.

You can define a foreign key to a unique constraint, not so a unique index.

But most importantly because:

it is the right way to do it.

b) because a primary key says more and it does more. A primary key is a unique constraint PLUS a not-null constraint.

You cannot compare "internal differences" for things that are not comparable. This is like asking one to compare a hammer (your unique index) which is a tool that MIGHT used to build a house, and a house (which is your unique constraint).

Primary Key on Unique Index Vs. Primary Key on Normal Index, DML performance

Durga Gadiraju, April 22, 2010 - 7:39 pm UTC

This post has clarified most of my questions in case of unique indexes. We have debate in our company about Primary Key on top of Unique Index and Primary Key on top of Normal Index. The other person is saying that when Primary Key is created on Unique Index, uniqueness will be checked twice, once due to UNIQUENESS on Index and Primary Key constraint which does not sound logical to me. He is saying that side effect of this is slow dml into the table compared to PK on normal index.

I argue that PK is smart enough to validate uniqueness only once whether underlying index is normal or unique. DML will be faster using PK on Unique Index as it will not store rowid and hence less IO.

April 23, 2010 - 9:02 am UTC

why doesn't anyone suggest what I would obviously do? what you've seen me do over and over and over again.....

Put up or shut up, do not hypothesize about how YOU would do it, show how Oracle does it.

DML will be faster using PK on Unique
Index as it will not store rowid and hence less IO.

that is not accurate, but it fortunately is not relevant to the question either :)

so, let's see:

ops$tkyte%ORA11GR2> drop table t1;

Table dropped.

ops$tkyte%ORA11GR2> drop table t2;

Table dropped.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t1 ( x number constraint t_pk primary key );

Table created.

ops$tkyte%ORA11GR2> create table t2 ( y number not null );

Table created.

ops$tkyte%ORA11GR2> create UNIQUE index t2_idx on t2(y);

Index created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR2> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
23-apr-2010 09:55:36

ops$tkyte%ORA11GR2> begin
  2          for i in 1 .. 100000
  3          loop
  4                  insert into t1 (x) values ( i );
  5                  insert into t2 (y) values ( i );
  6          end loop;
  7          insert into t1 (x)
  8          select 100000+rownum
  9            from dual
 10          connect by level < 100000;
 11          insert into t2 (y)
 12          select 100000+rownum
 13            from dual
 14          connect by level < 100000;
 15  end;
 16  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
23-apr-2010 09:56:45

ops$tkyte%ORA11GR2> @tk "sys=no"
ops$tkyte%ORA11GR2> column trace new_val TRACE
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
  2    from v$process a, v$session b, v$parameter c, v$instance d
  3   where a.addr = b.paddr
  4     and b.audsid = userenv('sessionid')
  5     and c.name = 'user_dump_dest'
  6  /

TRACE
-------------------------------------------------------------------------------
/home/ora11gr2/app/ora11gr2/diag/rdbms/orcl/ora11gr2/trace/ora11gr2_ora_24921.t
rc


ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ops$tkyte%ORA11GR2> !tkprof &TRACE ./tk.prf &1

TKPROF: Release 11.2.0.1.0 - Development on Fri Apr 23 09:56:47 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.



ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> edit tk.prf


INSERT INTO T1 (X) VALUES ( :B1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  99999     21.42      22.77         10        780     309266       99999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100000     21.42      22.77         10        780     309266       99999
********************************************************************************
INSERT INTO T2 (Y) VALUES ( :B1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  99999     21.31      22.76         15        779     309247       99999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100000     21.31      22.76         15        779     309247       99999
********************************************************************************
INSERT INTO T1 (X) SELECT 100000+ROWNUM FROM DUAL CONNECT BY LEVEL < 100000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.61       2.00         31        704       6514       99999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.61       2.03         31        704       6514       99999
********************************************************************************
INSERT INTO T2 (Y) SELECT 100000+ROWNUM FROM DUAL CONNECT BY LEVEL < 100000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.63       2.69         50        700       6489       99999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.63       2.70         50        700       6489       99999


ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> begin
  2          for i in 200000 .. 300000
  3          loop
  4                  insert into t1 (x) values ( i );
  5          end loop;
  6          insert into t1 (x)
  7          select 300000+rownum
  8            from dual
  9          connect by level < 100000;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> begin
  2          for i in 200000 .. 300000
  3          loop
  4                  insert into t2 (y) values ( i );
  5          end loop;
  6          insert into t2 (y)
  7          select 300000+rownum
  8            from dual
  9          connect by level < 100000;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec runStats_pkg.rs_stop(5000);
Run1 ran in 779 cpu hsecs
Run2 ran in 735 cpu hsecs
run 1 ran in 105.99% of the time

Name                                  Run1        Run2        Diff
STAT...redo size                57,682,208  57,676,944      -5,264
STAT...file io wait time             2,741       9,079       6,338
LATCH.JS slv state obj latch       -64,268           1      64,269
STAT...session uga memory                0      65,512      65,512
STAT...session cursor cache hi      34,495     -31,051     -65,546
STAT...physical read bytes         466,944     622,592     155,648
STAT...physical read total byt     466,944     622,592     155,648
STAT...cell physical IO interc     466,944     622,592     155,648
STAT...session pga memory          -17,028     327,680     344,708
STAT...session uga memory max    1,952,852           0  -1,952,852
STAT...session pga memory max    2,407,804           0  -2,407,804

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
1,462,344   1,526,381      64,037     95.80%

PL/SQL procedure successfully completed.

Nope, doesn't look like it to me - does it look like it to you?

Use the primary key constraint - and tell this other person to have some form of evidence that what they say is probably true before they say it - it'll not only save time but will make them look better going forward :)

adderek, July 28, 2010 - 4:53 pm UTC

Unique constraint for a set of columns induces a unique index for the same set of columns

Chandrashekar, May 10, 2013 - 12:10 pm UTC

Hi Tom,

I went through the previous comments and I am facing a similar issue. I wanted to implement unique constraint on a set of columns (7 to be precise). When I added the unique constraint using Alter Table statement
ALTER TABLE XXX ADD CONSTRAINT MY_CONSTRAINT UNIQUE(A,B,C,D,E,F,G)

This created an index MY_CONSTRAINT to the table. In the test data that I am using, few column values (assume B,C,D are nullable) are null.

This is creating a problem because I am not able to insert tuples into the table using a webservice, it throws and error saying "Null values cannot be inserted into unique indexed column(s)".

May 10, 2013 - 12:47 pm UTC

well, that isn't one of our error messages. We don't care if you insert null values into columns that allow for nulls - regardless of the existence of a constraint.

ops$tkyte%ORA11GR2> create table t ( a int not null, b int, c int, d int, e int not null, f int not null, g int not null, h int not null );

Table created.

ops$tkyte%ORA11GR2> alter table t add constraint t_unique unique( a,b,c,d,e,f,g );

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t (a,e,f,g, h) values ( 1,1,1,1, 100 );

1 row created.

so, this isn't the database throwing an error, this must be something in the logic of the webservice that you coded.

creation of unique constraint

Michal Pravda, June 18, 2013 - 8:17 am UTC

Hi,

can I (in some dictionary view I guess) investigate whether Oracle used existing index to enforce an unique constraint or created a new one?

The difference is significant for example when I want to remove the constraint. In one case it removes the index as well, in the other the index stays and from the application form of view still enforces uniqueness (if it is unique index). Of course that there are other foolproof "ORA-xxx free" solutions to this "problem" (test whether index remains, use plsql block to "drop it" whether it exists or not,...), but I would rather know the status in advance.

Test example if my explanation is not clear enough:

drop table test;
create table test(one_step number, two_steps number);
alter table test add constraint u1 unique(one_step);

create unique index u2 on test(two_steps);
alter table test add constraint u2 unique(two_steps);

now there is not a difference between the constraints or the indexes below that I can find.

select * from dba_constraints
where table_name ='TEST';
select * from dba_indexes
where table_name ='TEST';
select * from dba_cons_columns
where constraint_name in ('U1', 'U2');
select * from dba_ind_columns where index_name in ('U1', 'U2');

But if I drop both constraints I would still be unable to insert duplicate values into two_steps column because the index remains.

alter table test drop constraint u1;
alter table test drop constraint u2;
insert into test values (1,1);
insert into test values (1,1);

ora-0001

Where is the right place to look for the difference?

June 18, 2013 - 4:01 pm UTC

that information is unfortunately not exposed in the data dictionary... see:
http://jonathanlewis.wordpress.com/2012/04/19/drop-constraint/

for some approaches.

you can use drop index


ops$tkyte%ORA11GR2> create table test(one_step number, two_steps number);

Table created.

ops$tkyte%ORA11GR2> alter table test add constraint u1 unique(one_step);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create unique index u2 on test(two_steps);

Index created.

ops$tkyte%ORA11GR2> alter table test add constraint u2 unique(two_steps);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table test drop constraint u1 drop index;

Table altered.

ops$tkyte%ORA11GR2> alter table test drop constraint u2 drop index;

Table altered.

ops$tkyte%ORA11GR2> insert into test values ( 1,1 );

1 row created.

ops$tkyte%ORA11GR2> insert into test values ( 1,1 );

1 row created.

ops$tkyte%ORA11GR2>

Facing issue with Composite Unique Key.

R.Kumar, October 11, 2013 - 2:13 pm UTC

Hi Tom,

I have a table having columns staff_id,country_id,createdate,updatedate etc. Unique constraint was defined on only staff_id in tha past as every staff used to work for only one country he is assigned to. Now as per the new requirement each staff can work for multiple countries . So now we recieve multiple records having same staff_id but different country id. As per the new requirement unique constraint is dropped on staff_id column and recreated on staff_id,country_id columns as composite unique key.
ALTER TABLE Staff_profile DROP CONSTRAINT Staff_profile_UK01;
ALTER TABLE Staff_profile
ADD CONSTRAINT Staff_profile_UK01 UNIQUE(STAFF_ID,COUNTRY_ID);
But even after i create composite unique key on two columns as above it is not accepting duplicate values into staff_id column even though staff_id,country_id combination is unique. I dont understand where the issue is..

October 11, 2013 - 4:16 pm UTC

did someone manually create a unique index on staff_id, query user_indexes - if they did, drop it.

Regarding Unique Index and Index

surswm, October 31, 2013 - 3:11 pm UTC

Hi Tom,

Could you please help me understand the below thing.

1)Can we track (Statspack) the DDL commands that were exeucted with nologging

I am trying to create below indexes parallely.

OS : UNIX AIX

Db: Oracle 9i

Process: 32

Below listed commands are issued one by one using nohup from unix command prompt.

Problem: taking long time to create Index on the table with 1.7 billion records.

It seems like the index creation 2 and 3 are clashing at some point waiting for resources.

As they have two common columns (a,b).

Can we run the DDLs like below in parallel( UNIX nohup background).

OR

Should we run them one after the other?.

OR

Is there something else that might be causing issue?.

Command :1
CREATE INDEX stsc.xindex01 ON scott.temp
(x)
NOLOGGING
TABLESPACE POINDEX
PARALLEL (DEGREE 32);

Command :2
CREATE UNIQUE INDEX stsc.xindex02 ON scott.temp (
a,b,c,d,e,f,g,h
)
NOLOGGING
TABLESPACE POINDEX
PARALLEL (DEGREE 32);

Command :3
CREATE INDEX stsc.xindex03 ON scott.temp(
a,b
)
NOLOGGING
TABLESPACE POINDEX
PARALLEL (DEGREE 32);

November 01, 2013 - 9:30 pm UTC

it would probably only make sense to use parallel query and run the indexes on after the other. otherwise - they'll be fighting each other to read the same data.

are you sure 32 is the correct degree. since you are running such very old software, I'll assume you are running on an old, slow, few cpu machine with not too much IO capability. would suggest using 2*cpu_count to start with.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

unique index

Unique Index

unique constraint\index - still confused

unique constraint\index - continued

Is this a proper way of enforcing PRIMARY KEY??

may be a basic question on Unique index implementation

Implicit and Explicit creation of unique index

Implicit and Explicit creation of unique index

Unique constraint/index standard?

Thanks!

UNIQUE: constraint vs index

Bug #3980704

Unique constraint on timestamp with time zone - is it possible?

Difference between UNIQUE INDEX, UNIQUE COSNTRAINT, PRIMARY KEY

Primary Key on Unique Index Vs. Primary Key on Normal Index, DML performance

Unique constraint for a set of columns induces a unique index for the same set of columns

creation of unique constraint

Facing issue with Composite Unique Key.

Regarding Unique Index and Index

More to Explore

Live SQL

Dev Gym