Skip to Main Content
  • Questions
  • primary key constraint vs non-unique index+PK

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 20, 2003 - 3:55 pm UTC

Last updated: April 26, 2012 - 3:37 pm UTC

Version: 8.1.7.4

Viewed 1000+ times

You Asked

Hi

I have some questions regarding definition of primary keys at database level. I know that in 8i we can enforce uniqueness using non-unique index and primary keys, this makes thing flexible in datawarehouse, when we have duplicate rows after doing direct load in partitioned tables we can delete these rows easily by disabling the PK without losing index (so I dont need to rebuild index for all partitions again), if I was using conventional PK then after I disable the PK I would lose my PK in all partitions (well I dont think we can disable PK for one partition no?) and I would have to create a brand new index for my hundreds of millions of rows table. Until now I gues smy understanding of this feature is quite correct, correct me if I am wrong.

My question is, since this is so flexible should we always create PK this way? Even in OLTP databases? What can this feature harm? Any disadvantages? I dont see any really....

THX

and we said...

There is great flexibility -- this is true. However, food for thought -- things to think about:

big_table@ORA920> alter table big_table add constraint big_table_pk
2 primary key(id);
Table altered.

big_table@ORA920> analyze index big_table_pk validate structure;
Index analyzed.

big_table@ORA920> @printtbl8 'select * from index_stats';
...
LF_BLKS : 8360
LF_ROWS_LEN : 60010430
LF_BLK_LEN : 7996
BR_ROWS : 8359
BR_BLKS : 14
BR_ROWS_LEN : 98135
...
BTREE_SPACE : 66958952
USED_SPACE : 60108565
-----------------

PL/SQL procedure successfully completed.

big_table@ORA920> alter table big_table drop constraint big_table_pk;

Table altered.

big_table@ORA920> alter table big_table add constraint big_table_pk
2 primary key(id) DEFERRABLE;

Table altered.

big_table@ORA920> analyze index big_table_pk validate structure;

Index analyzed.

big_table@ORA920> @printtbl8 'select * from index_stats';
...
LF_BLKS : 8900 (more)
LF_ROWS_LEN : 63826622 (bigger)
LF_BLK_LEN : 7996
BR_ROWS : 8899 (more)
BR_BLKS : 16 (more)
BR_ROWS_LEN : 113327 (bigger)
...
BTREE_SPACE : 71292848 (bigger)
USED_SPACE : 63939949 (bigger)
...
-----------------

PL/SQL procedure successfully completed.

that was on a number -- the number ranged from 1 .. 3,816,192 (number of rows in the table actually). A non-unique index takes more space...


Also, consider the query plan -- it is a NON-UNIQUE index and the optimizer has to treat it that way when generating plans. There is no reason why:

select * from big_table where id = :x

would not return 0, 1, or 1,000 rows -- even given a primary key on ID. Hence, it'll be a NON-UNIQUE index range scan.


That does not mean "don't use non-unique indexes for primary keys" for the benefits seem to outweigh the above by a long shot in your case.

Rating

  (37 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

primary key constraint vs non-unique index+PK", version 8.1.7.4

Joseph Fabiano, March 20, 2003 - 10:26 pm UTC

Tom,

Can you explain with an example the feature that this
person is describing? I'm interested in knowing how
to disable the PK, keep the index, perform a direct sqlload, then place the PK
back without recreating the index. I was able to
disable the PK and keep the index but the index was still
unique.

Tom Kyte
March 21, 2003 - 8:29 am UTC

just

alter table t add constraint t_pk primary key (a,b) DEFERRABLE;

you'll note at that point, the index generated is non-unique.

or,

create index t_idx on t(a,b);
alter table t add constraint t_pk primary key(a,b);

that way -- you'll have a non-deferrable constraint that uses a non-unique index to enforce the primary key.

for a partitioned table

A reader, March 21, 2003 - 1:23 pm UTC

Hi

For a partitioned table I think your only bet is create the non-unique index first then add the PK

alter table t add constraint t_pk primary key (a,b) DEFERRABLE;

doesnt work for a partitioned table does it?

Tom Kyte
March 21, 2003 - 2:19 pm UTC

ps$tkyte@ORA920> CREATE TABLE t
  2  (
  3    x int ,
  4    collection_year int,
  5    y int
  6  )
  7  PARTITION BY RANGE (COLLECTION_YEAR) (
  8    PARTITION PART_95 VALUES LESS THAN (1996) storage (initial 1m),
  9    PARTITION PART_96 VALUES LESS THAN (1997) storage (initial 5m),
 10    PARTITION PART_01 VALUES LESS THAN (MAXVALUE) storage (initial 1k)
 11  )
 12  ;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table t
  2  add constraint t_pk
  3  primary key(x) deferrable;

Table altered.


yes, it does work. 

yea well but I was referring partitioned PK

A reader, March 21, 2003 - 2:55 pm UTC

Hi

I was referring partitioned index, with your way it is not a partitioned index doh

Tom Kyte
March 21, 2003 - 3:06 pm UTC

so sorry, crystal ball is on the blink again.  Everything is being intrepreted literraly in the meanwhile ;)


unless  you are partitioning by the primary key -- no go in EITHER case

ops$tkyte@ORA920> alter table t
  2  add constraint t_pk
  3  primary key(x) deferrable
  4  using index local;
alter table t
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

<b>cannot create the locally partitioned index non-unique index using the all primary key...</b>

ops$tkyte@ORA920>
ops$tkyte@ORA920> create index t_pk on t(x) local;

Index created.

ops$tkyte@ORA920> alter table t
  2  add constraint t_pk
  3  primary key(x) deferrable ;
alter table t
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

<b>and if it is there , it fails</b>

ops$tkyte@ORA920> drop index t_pk;

Index dropped.

ops$tkyte@ORA920> create index t_idx on t(x) local;
Index created.

ops$tkyte@ORA920> alter table t
  2  add constraint t_pk
  3  primary key(x) deferrable ;
alter table t
*
ERROR at line 1:
ORA-01408: such column list already indexed

<b>as  you can see -- it is trying to create a global non-unique index</b>

the index in this case would have to be a global, non-unique index partitioned by the PRIMARY KEY values itself.  and that can in fact be done in the constraint itself:

ops$tkyte@ORA920> alter table t
  2  add constraint t_pk
  3  primary key(x) deferrable
  4  using index
  5  global
  6  partition by range(x)
  7  ( partition p1 values less than (1000),
  8    partition p2 values less than (maxvalue)  );

Table altered.




 

is this a typo?

A reader, March 21, 2003 - 7:12 pm UTC

Hi

from one of your post you mentioned this:
--------------------------------------------------------
alter table t add constraint t_pk primary key (a,b) DEFERRABLE;

you'll note at that point, the index generated is non-unique.
--------------------------------------------------------

In your last post however you said this:
--------------------------------------------------------
ops$tkyte@ORA920> alter table t
2 add constraint t_pk
3 primary key(x) deferrable ;
alter table t
*
ERROR at line 1:
ORA-01408: such column list already indexed

as you can see -- it is trying to create a unique index
--------------------------------------------------------

is DEFERRABLE trying to create a non-unique index or an unique index...?

Tom Kyte
March 21, 2003 - 7:23 pm UTC

corrected to read as:


as you can see -- it is trying to create a global non-unique index

why partition column must be part of unique ndex?

A reader, March 22, 2003 - 11:55 am UTC

hi

ops$tkyte@ORA920> alter table t
2 add constraint t_pk
3 primary key(x) deferrable
4 using index local;
alter table t
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index


Why we cannot create unique index partitioned locally without involving the partition column?

Also I have a question regarding unique values in partitions, if I have three partitions partitioned by date and my primary key is customer_id, can customer_id repeat in different partitions?

Tom Kyte
March 22, 2003 - 12:41 pm UTC

because it would obviate all of the usefulness of partitioning.


In order to insert a row -- we would have to lock ALL index partitions to prevent that same value from being inserted in there, there goes availability, there goes scalability, there goes concurrency -- right out the window.

If you hash partitioned into 10 partions
And you had a local index on (X)
And X was not the partition key (so X=5 could go into ANY partition)
And X had to be unique -- the only way to do that would be to lock the 9 index partitions you are NOT inserting into (to prevent X=5 from going in there, goodbye concurrency -- AFTER checking if X=5 was already in there, goodbye availability -- every partition MUST be available ALL of the time)....


The answer to the last question should be self evident.

IF your primary key for the table is customer id
THEN customer id in the table is unique.



I dont understand last explanation

A reader, March 22, 2003 - 1:35 pm UTC

Hi

I dont understand the last explanation at all :-(

Why we have to lock all partitions to insert a row?

If I have a table

empno hiredate
----- -----------
1 200110
2 200111
3 200112

hiredate is partition key by year-month

if let´s say we can create an partitioned local unique index on empno (ok its not possible but to simulate the locking issue...)

I then do

insert into table values (4, 200112);

and the other session just does the same in sert a few seconds after me what happens..?
Why this would lock the ther three partitions (200210, 11 and 12)?

if I do

insert into table values (5, '');

then this would go to the partitition with maxvalue, would this behaves as previous insert statement?



Tom Kyte
March 23, 2003 - 4:59 pm UTC

say session 1 does:

insert ( 4, 200112 );

and session 2 does

insert ( 4, 200111 )

and session 3 does

insert ( 4, 200110 )

Now, tell me what happens.... All of these go on at the same time bear in mind. Unless you locked the partions pertaining to 200111 and 200110 when you modified the index associated with partition 200112 -- what would prevent sessions 2 and 3 from creating another empno = 4?

And further, if session 1 did not scan these indexes -- how would session 1 know that empno = 4 doesn't already exist?



Maybe i fail to understand...

VJ, April 23, 2003 - 8:22 pm UTC

Hi, 

I want to create a partitioned table. I want to maintain a primary key to the table. One part of the table partition i would like to truncate and load data on a daily basis. I am having difficult time achieving this. 

---
I want partitioned table and i want primary key, so i wont insert duplicate records...
---

Kindly help.... 

Thanks a million 
VJ 

The steps i followed are here below: 
------- 
1 create table t7 ( 
2 id number, 
3 name varchar2(30), 
4 status varchar2(30), 
5 pkey number) 
6 partition by range (pkey) 
7 (partition p1 values less than (3), 
8* partition p2 values less than (6)) 
SQL> / 

Table created. 

Elapsed: 00:00:00.78 

SQL> alter table t7 add constraint pk_t7 primary key (id,name,status); 

Table altered. 

Elapsed: 00:00:00.41 

SQL> begin 
2 for i in 1..10 loop 
3 insert into t7 values (i,'vj','A',1); 
4 insert into t7 values (i,'dto','A',2); 
5 insert into t7 values (i,'vld','N',4); 
6 end loop; 
7 end; 
8 / 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:00.19 

SQL> select count(0) from t7 partition (p1); 

COUNT(0) 
---------- 
20 

Elapsed: 00:00:00.08 
SQL> select count(0) from t7 partition (p2); 

COUNT(0) 
---------- 
10 

Elapsed: 00:00:00.01 

SQL> alter table t7 truncate partition p1; 

Table truncated. 

Elapsed: 00:00:00.17 

SQL> begin 
2 for i in 1..10 loop 
3 insert into t7 values (i,'vj','A',1); 
4 insert into t7 values (i,'dto','A',2); 
5 end loop; 
6 end; 
7 / 
begin 
* 
ERROR at line 1: 
ORA-01502: index 'CDW_MKT.PK_T7' or partition of such index is in unusable 
state 
ORA-06512: at line 3 


Elapsed: 00:00:00.02 
 

Tom Kyte
April 23, 2003 - 9:11 pm UTC

search for LOCAL on this page and use a LOCALly partitioned index so the truncate doesn't invalidate the default GLOBAL index that is being used.

Maybe i fail to understand...

VJ, April 23, 2003 - 10:29 pm UTC

Tom,

I feel a quick example would do more good listing my requirements.

1) Paritiioned table
2) Maintaining Primary Key/Unique

Thanks a lot in advance
VJ

Tom Kyte
April 24, 2003 - 7:34 am UTC

I already HAVE an example, please -- READ this page. goto the top, search for LOCAL (like i said).

READ this page (it has an example of exactly what you asked for already)

READ the documentation -- for unless you do that, I can assure you of one fact -- you will FAIL miserably in your implementation that uses partition due to lack of understanding of how it really works.




Eyes Wide Shut, Now open...

VJ, April 24, 2003 - 1:07 pm UTC

Tom,

I guess i have read too much documentation on internet, that towards the end of the day i have started confusing my self every step of the way. Now, I got what i wanted and implemented it and it is working fine.

Thanks a lot once again,
VJ

Globally unique primary keys

A reader, January 07, 2004 - 7:06 pm UTC

Hi Tom,

For some reason I want to make the PK values in the tables unique in the whole schema. Which one is better?

1. Define one sequence and used it as the PK value for all primary key columns.
2. Define a sequence for each table and prefix it with table short name and use it as the PK. for example: 'TXN000001'.

Thanks for your comments.

BTW, the IRID and IVID columns in Designer Repository look extremely largely to me and they are not generated thru sequences. What's the internal mechanism for generating them?

Tom Kyte
January 08, 2004 - 12:41 pm UTC

or 3

3) create sequence_t1 seq start with 1 increment by 10000;
create sequence t2_seq start with 2 increment by 10000;
...
create sequence tN_seq start with N increment by 10000;

allows you to have upto 10,000 tables -- each with their own sequence, each generating unique numbers.

or

4) use sys_guid()



Not familar with designers schema -- sorry.

Mark, July 09, 2004 - 1:51 pm UTC

Tom, I am looking to use a Non Unique PK on a table for a OLTP application. The table in question is a child table of the main header table and will always be accessed via the Header_id only. All rows for a given header_id will always be returned. Each header row will have between 1 to maybe 15 child records added over time. And the Header table will grow approx 150K/year

I have the option of using date field as part of the PK to make it Unique but whats the point. I will never use it in the WHERE statement. All queries will be with joining with the header table.

Here is what it looks like.

CREATE TABLE T1
(HEADER_ID NUMBER(15),
FK1 NUMBER(15),
FK2 NUMBER(2),
TEXT1 VARCHAR2(50),
TEXT2 VARCHAR2(50),
DATE1 DATE);

CREATE TABLE T2
(HEADER_ID NUMBER(15),
ACT_DATE DATE
CODE1 NUMBER(1),
USER_ID NUMBER(15)
TEXT3 VARCHAR2(255));

Thanks for your wisdom...



Tom Kyte
July 09, 2004 - 2:03 pm UTC

"Non Unique PK" is "an oxymoron"

guess what you are saying is "my table won't have a primary key" -- which is technically fine - i might use a different name from HEADER_ID in that table as it would seem to denote "unique" by name.

Mark, July 09, 2004 - 1:52 pm UTC

Sorry,

Forgot to add. DB Version 9.2.0.5

Thanks

why we create non-unique index for PK in DWH

A reader, August 24, 2004 - 8:05 am UTC

Hi

From the original post where he says that he creates non-unique index for PK.

What is the point of doing this? We disable our indexes before data load and I dont see the benefit of using nonunique index for PK

thx

Tom Kyte
August 24, 2004 - 8:53 am UTC

ok, so you disable a unique index, what then?  you cannot insert into it...


ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> create unique index t_idx on t(x);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx unusable;
 
Index altered.
 
ops$tkyte@ORA9IR2> alter session set skip_unusable_indexes=true;
 
Session altered.
 
ops$tkyte@ORA9IR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-01502: index 'OPS$TKYTE.T_IDX' or partition of such index is in unusable
state
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop index t_idx;
 
Index dropped.
 
ops$tkyte@ORA9IR2> create index t_idx on t(x);
 
Index created.
 
ops$tkyte@ORA9IR2> alter index t_idx unusable;
 
Index altered.
 
ops$tkyte@ORA9IR2> insert into t values ( 1 );
 
1 row created.



So, the advantage is you can easily disable the index, load the data and rebuild the index after the load -- potentially increasing the load performance, without risk of LOSING an index (i'm not a fan of drop index, load, create index -- you will lose an INDEX some day and spend a week wondering "why is the system so slow" -- with the unusable index, you'll know immediately -- the first time a query goes to use the index -- that it is broken) 

To explain why you may want to use this feature

A reader, October 12, 2004 - 7:17 am UTC

Often in a datawarehouse you range partition the fact tables on date in order to give a rolling window. The primary key sometimes does not include the date and hence it is unpartitioned or globally partitioned (e.g. a telecoms model where the date/time only goes down to the second and you may have more then one call in the same second so you use a sequence number)

If you make your index partitions unusable you will have to rebuild your primary key in full as you probably will not be able to determine the partitions effected by the change.

If you have a large number of local bitmap indexes on your fact table and are doing historical corrections it is often much faster to do a delete of the data to be replaced then a sqlloader direct load (as the indexes get deferred). This forces a delete/insert approach to an update. (remember to rebuild the indexes later as they may have grown alarmingly)

If the delete fails (we all do maintenance and make mistakes) you get duplicate entries in the primary key and the whole index becomes unusable. You are now faced with finding and removing these duplicates without an index.

Using a non-unique index as a base for the primary key constraint solves this problem and reduces the down-time.

Tom Kyte
October 12, 2004 - 8:28 am UTC

but you cannot -- you do understand that right?

think about it... think about what would have to happen if you used a locally partitioned, non-unique index on the primary key (and the primary key is not the partition key) -- and you insert. Think about what would physically have to happen (eg: all other local index partitions would have to be necessarily LOCKED and INSPECTED -- and kept locked. EG: one session and one session only could ever modify such a table at a time - no parallel, no nothing)

(and you have the ability to maintain global indexes during partition operations -- thusly ELIMINATING downtime, not reducing it -- but getting rid of it)


Unique Constraint W/O Unique Index

robert, December 02, 2004 - 12:10 pm UTC

8.1.7.4/CBO
I have a Global Temp T (That has NO INDEX) that my procedure inserts 1 ~ 2000 "transaction id" at first.
Step#2 I eliminate duplicates.

Afterwards, several JOIN-UPDATEs of this GTT to other tables based on this "transaction_id"

I'm tuning this proc now and I want to elimate Step#2.

Can I have a unique constraint WITHOUT getting an unique index ?
Because I read your "Index is NOT always good" writing
and think, maybe incorrectly, that index lookup on this GTT of this size is counterproductive and should go full-scan.

thanks




Tom Kyte
December 02, 2004 - 12:26 pm UTC

you should use a unique constraint -- it'll be faster than doing 2,000 "lookups"

PK using local unique index

Sami, April 28, 2005 - 11:41 pm UTC

Dear Tom,

Could you please explain the lockinh stuff --When we have primary key enabled using local partitioned unique index.


Earlier in this thread you mentioned that

<Asktom>
say session 1 does:

insert ( 4, 200112 );

and session 2 does

insert ( 4, 200111 )

and session 3 does

insert ( 4, 200110 )

Now, tell me what happens....  All of these go on at the same time bear in mind. 
 Unless you locked the partions pertaining to 200111 and 200110 when you 
modified the index associated with partition 200112 -- what would prevent 
sessions 2 and 3 from creating another empno = 4?

And further, if session 1 did not scan these indexes -- how would session 1 know 
that empno = 4 doesn't already exist?

</Asktom>



SQL> create table p(id number,year number,nonkeycol varchar2(100)) 
partition by
range(year) 
(
partition part_a values  less than (2000),
partition part_b values less than (2001),
partition part_c values less than (2002),
partition part_max values less than (maxvalue)
);

Table created.


SQL> create unique index p_idx1 on p(id,year) local;
Index created.


SQL> alter table p add constraint p_pk primary key(id,year) using index local;
Table altered.

SQL> desc p
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------

 ID                                        NOT NULL NUMBER
 YEAR                                      NOT NULL NUMBER
 NONKEYCOL                                          VARCHAR2(100)


---------SESSION 1-----------------------
SQL> insert into p values(1,1999,'part_a');
1 row created.

SQL> insert into p values(1,2000,'part_b');

1 row created.

SQL>

------------END----------------------------


------------SESSION 2 ------------------------
SQL> insert into p values(1,2001,'part_c');

1 row created.

------------END----------------------------


I don't see any lock on part_c while I am inserting value "1" into part_a or part_b.
 

Tom Kyte
April 29, 2005 - 8:04 am UTC

the concept was "EMPNO MUST BE UNIQUE", "TABLE PARTITIONED BY YEAR"

You cannot create a locally partitioned UNIQUE index on EMPNO in this case. In order to do that, we HYPOTHETICALLY would have to lock ALL index partitions when inserting into any single one of them and check all of them for the existence of empno=4. We don't do that, unique locally partitioned indexes are not supported unless they include the partition key.

the example I had was "unique constraint on X, partition by Y"

your example is "partition by Y, unique constraint on X,Y"

ORA-14039: Still dont get it

A reader, August 03, 2005 - 4:17 pm UTC

<quote>
the example I had was "unique constraint on X, partition by Y"

your example is "partition by Y, unique constraint on X,Y"
</quote>

I still dont get it. I have a non-partitioned table with a pk of accnt_id. I create a partitioned table with a PK of (as_of_dt,accnt_id), range or list partitioned by as_of_dt.

Why cant I create a local UNQIUE index on ACCNT_ID alone? I get the ORA-14039 when I try to do this. But when I create the local unique index on (accnt_id,as_of_dt), it works fine.

Thanks

Tom Kyte
August 03, 2005 - 6:09 pm UTC

think of how the data is organized.

You have it partitioned by DT, ID

dt=01-jan-2005, id=ANY ->partition 1
dt=02-jan-2005, id=ANY ->partition 2

and so on. id=42 may appear in zero, one or more of these partitions, hence a unique LOCAL index on ID (if it were possible, which it is not) would have to lock ALL index partitions in order to insert 42 and CHECK all index partitions in order to insert 42

In other words, it would not work very well outside of a single user database.

ORA-14039: Still dont get it

A reader, August 03, 2005 - 4:20 pm UTC

"we HYPOTHETICALLY would have to lock ALL index partitions when inserting into any single one of them and check all of them for the existence of empno=4"

Why? empno is unique in a partition, so when inserting empno=4, after deciding which partition it goes into depending on the partition key, why would it lock all other index partitions?

Tom Kyte
August 03, 2005 - 6:10 pm UTC

to prevent YOU from also inserting empno=4 in some other partition of course.


We have to prevent empno=4 from being inserted into ALL partitions if you want to put it into partition=1.

ORA-14039

VA, August 03, 2005 - 9:25 pm UTC

"to prevent YOU from also inserting empno=4 in some other partition of course."

Still dont get it. Whats wrong with inserting empno=4 in some other partition? empno is unique in a partition. Across partitions, the same empno can be repeated, so why lock them?

Yes, id=42 may appear in 0, 1 or more partitions, so what? If I insert id=42 in partition 1, why do we need to check other partitions for id=42? The other partitions might contain id=42 or they might not, how is that relevant?

Tom Kyte
August 04, 2005 - 8:15 am UTC

Then, you have just totally lost me.

I thought the goal was "empno unique", that is what I was writing about.

What are *your* requirements. For they are obviously very different from what we were discussing on this page (which was a UNIQUE constraint being enforced by a LOCAL index when the constraint is on columns that are NOT part of the partition key)



ORA-14039: Still dont get it

VA, August 04, 2005 - 9:24 am UTC

"I thought the goal was "empno unique", that is what I was writing about"

Yes, that is the goal, but what I am talking about is "empno unique" PER PARTITION. See, when I think of a LOCAL INDEX I think of it as a local index segment on just ONE partition of the table. Similar to a UNIQUE index on a non-partitioned table.

What I am realizing is that

create unique index i on mytab(accnt_id) LOCAL;

is attempting to create a UNIQUE CONSTRAINT as well in addition to the index!! ACCNT_ID is, of course, not UNIQUE to the entire table, it is unique to just a partition, so all your earlier comments are valid.

But then the question becomes, why is the CREATE UNIQUE INDEX trying to create a UNIQUE constraint? Didnt you say somewhere else on this site that indexes and constraints are really independent concepts? You can have one without the other?

Thanks

Tom Kyte
August 04, 2005 - 9:59 am UTC

what is your question here -- i'm very much lost.


That create unique index -- it is not trying to create a unique constraint, it is trying the CREATE A UNIQUE INDEX -- the contents of the INDEX must be unique. The fact that physically it would be partitioned underneath is not even relevant, the thing you are creating is an INDEX -- and an index in a single partition (old fashioned, regular stuff) should behave the same logically as an index in 2 partitions -- either the data in the INDEX is unique -- OR NOT.

You asked for unique.

ORA-14039: Still dont get it

VA, August 04, 2005 - 11:03 am UTC

"CREATE A UNIQUE INDEX -- the contents of the INDEX must be unique"

But they are unique WITHIN A PARTITION (thats what the LOCAL keyword means). So why is it trying to enforce that ACCNT_ID should be unique in the entire table?

create unique index i on mytab(accnt_id) LOCAL;

should just go to each partition, and create a regular old-fashioned index segment on THAT PARTITION only, the accnt_id IS unique in that partition.

Again, why is it trying to enforce that ACCNT_ID should be unique in the entire table when I just asked for a UNIQUE LOCAL index on ACCNT_ID?

Thanks

Tom Kyte
August 04, 2005 - 12:09 pm UTC

because you have a unique index.

forget the table
forget partitioning

you have an index.
the index is on accnt_id.
the index is unique
therefore accnt_id must be unique in the index

it does not matter if the index is stored in 1 or 100000000 partitioned, accnt_id must be UNIQUE.

partitioning is a red herring here, it is a physical implementation.

The crux of the situation is you have a UNIQUE INDEX, not partitions, not tables, not anything. You just have a UNIQUE INDEX, period. ACCNT_ID must be UNIQUE in that index.

period.


ORA-14039: Still dont get it

VA, August 04, 2005 - 1:30 pm UTC

OK I see what you are saying, hope you understand what I was trying to say.

See

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#460696 <code>

That diagram makes it appear like each local index segment is totally independent of the other index segments. So, even though I have a UNIQUE index, the "uniqueness" is enforced at each index segment level, not across ALL the index segments.

Obviously, it seems to be enforcing it across ALL the segments. I see absolutely no reason for Oracle to do this.

This is a LOCAL index, each index segment indexes/stores rowids only for its associated local table partition, why in the world would it care about if ACCNT_ID=4 exists in some other partition? The job of the UNIQUE LOCAL index should be to index and enforce uniqueness IN THE TABLE PARTITION THAT IT IS INDEXING.

I guess what confused me is that the "sense" of the UNIQUE keyword seems to be "global" but that contradicts with the LOCAL keyword later on in the SQL statement.

Thanks

Tom Kyte
August 04, 2005 - 1:58 pm UTC

No, I don't understand why you believe that a unique index that is LOCAL should only be unique in each partition. That, does not make sense to me.


you have an index
the index either is
a) unique
b) non-unique


you pick, the INDEX is the only thing we are talking about here. That an index might be physically made up from many partitions, extents, blocks, datafiles, whatever -- not relevant.


I can draw a picture that looks like that regarding extents -- a regular nonpartition index is a segment, a segment is made up of independent, non-connected, separate extents. Should index entries be unique in an extent?

why in the world would it care about if
ACCNT_ID=4 exists in some other partition?


BECAUSE you asked for a UNIQUE INDEX ON THE ATTRIBUTE ACCNT_ID. A UNIQUE INDEX, not a unique index partition (no such syntax!!). You asked for a UNIQUE INDEX.

LOCAL is a physical implementation, a partitioning scheme.

UNIQUE is unique, there is a single unique index that physically is implemented in many partitions that happen to use a local partitioning scheme.


There is just "an index"

ORA-14039: Finally got it

VA, August 04, 2005 - 2:11 pm UTC

Ah, thanks, your persistence paid off, thanks again for bearing with me.

Your comparison with segments and extents drove it home for me (from a "why" perspective).

I guess I will go ahead and
CREATE UNIQUE INDEX i on mytab(accnt_id,as_of_dt) LOCAL;
that works fine.

Thanks again, appreciate it.

Difference between local and global indexes in enforcing uniqueness

Logan Palanisamy, August 04, 2005 - 7:39 pm UTC

Here is a demo which hopefully answers some of the questions  raised on this thread. It certainly helped clear up some of  my mis-conceptions regarding local indexes and uniqueness.


SQL> -- Regular non-partitioned table
SQL> drop Table t;

Table dropped.

SQL> Create Table t
  2   (
  3      acctid       number    Not Null,
  4      date_of_opening date         Not Null
  5  );

Table created.

SQL> 
SQL> create unique index t_idx on t(acctid);

Index created.

SQL> 
SQL> insert into t values (1, '01-JAN-2005');

1 row created.

SQL> insert into t values (1, '02-JAN-2005');
insert into t values (1, '02-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated 


SQL> insert into t values (1, '31-JAN-2005');
insert into t values (1, '31-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated 


SQL> insert into t values (1, '01-FEB-2005');
insert into t values (1, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated 


SQL> insert into t values (2, '01-FEB-2005');

1 row created.

SQL> insert into t values (2, '01-FEB-2005');
insert into t values (2, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated 


SQL> insert into t values (2, '28-FEB-2005');
insert into t values (2, '28-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated 


SQL> 
SQL> select * from t;

    ACCTID DATE_OF_OPE                                                          
---------- -----------                                                          
         1 01-JAN-2005                                                          
         2 01-FEB-2005                                                          

SQL> 
SQL> 
SQL> 
SQL> -- Same table partitioned on date_of_opening
SQL> 
SQL> drop Table pt;

Table dropped.

SQL> Create Table pt
  2   (
  3      acctid       number    Not Null,
  4      date_of_opening date         Not Null
  5  )
  6  partition by range(date_of_opening)
  7  (
  8       partition p_jan05 values less than
  9  (to_date('01-FEB-2005','DD-MON-YYYY')),
 10       partition p_feb05 values less than
 11  (to_date('01-MAR-2005','DD-MON-YYYY')),
 12       partition p_maR05 values less than
 13  (to_date('01-APR-2005','DD-MON-YYYY'))
 14  );

Table created.

SQL> 
SQL> -- This index will fail because it doesn't have the partition as part of it.
SQL> create unique index pt_l_idx on pt(acctid) local;
create unique index pt_l_idx on pt(acctid) local
                                *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE 
index 


SQL> 
SQL> -- This index gets created because of the inclusion of the partition key
SQL> create unique index pt_l_idx on pt(acctid, date_of_opening) local;

Index created.

SQL> 
SQL> -- Consequently it allows the same account id even within the same partion, let alone another partition. Beware.
SQL> insert into pt values (1, '01-JAN-2005');

1 row created.

SQL> insert into pt values (1, '01-JAN-2005');
insert into pt values (1, '01-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_L_IDX) violated 


SQL> insert into pt values (1, '31-JAN-2005');

1 row created.

SQL> insert into pt values (1, '01-FEB-2005');

1 row created.

SQL> insert into pt values (1, '01-FEB-2005');
insert into pt values (1, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_L_IDX) violated 


SQL> insert into pt values (1, '28-FEB-2005');

1 row created.

SQL> 
SQL> select * from pt;

    ACCTID DATE_OF_OPE                                                          
---------- -----------                                                          
         1 01-JAN-2005                                                          
         1 31-JAN-2005                                                          
         1 01-FEB-2005                                                          
         1 28-FEB-2005                                                          

SQL> select * from pt partition (p_jan05);

    ACCTID DATE_OF_OPE                                                          
---------- -----------                                                          
         1 01-JAN-2005                                                          
         1 31-JAN-2005                                                          

SQL> select * from pt partition (p_feb05);

    ACCTID DATE_OF_OPE                                                          
---------- -----------                                                          
         1 01-FEB-2005                                                          
         1 28-FEB-2005                                                          

SQL> 
SQL> delete from pt;

4 rows deleted.

SQL> drop index pt_l_idx;

Index dropped.

SQL> 
SQL> -- If you want the account-id to be really unique across table, you have no choice but to create a global index.
SQL> -- As you can see, it doesn't allow any duplicates
SQL> drop index pt_g_idx;
drop index pt_g_idx
           *
ERROR at line 1:
ORA-01418: specified index does not exist 


SQL> create unique index pt_g_idx on pt(acctid);

Index created.

SQL> insert into pt values (1, '01-JAN-2005');

1 row created.

SQL> insert into pt values (1, '01-JAN-2005');
insert into pt values (1, '01-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated 


SQL> insert into pt values (1, '31-JAN-2005');
insert into pt values (1, '31-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated 


SQL> insert into pt values (1, '01-FEB-2005');
insert into pt values (1, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated 


SQL> insert into pt values (2, '01-FEB-2005');

1 row created.

SQL> insert into pt values (2, '01-FEB-2005');
insert into pt values (2, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated 


SQL> insert into pt values (2, '28-FEB-2005');
insert into pt values (2, '28-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated 


SQL> select * from pt;

    ACCTID DATE_OF_OPE                                                          
---------- -----------                                                          
         1 01-JAN-2005                                                          
         2 01-FEB-2005                                                          

SQL> select * from pt partition (p_jan05);

    ACCTID DATE_OF_OPE                                                          
---------- -----------                                                          
         1 01-JAN-2005                                                          

SQL> select * from pt partition (p_feb05);

    ACCTID DATE_OF_OPE                                                          
---------- -----------                                                          
         2 01-FEB-2005                                                          

SQL> spool off
 

reader

A reader, August 09, 2005 - 2:18 pm UTC

SQL> alter table owner.table_name drop primary key;
alter table owner.table_name drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

How do I trace the foreign keys. If I disable the foreign 
key is sufficient or I need to drop the foreign keys
before dropping the primary key 

Tom Kyte
August 09, 2005 - 3:25 pm UTC

select * from all_constraints where r_constraint_name = 'that primary key' and r_owner = 'that owner'

you have to drop the referencing constraints.



ops$tkyte@ORA9IR1> create table p ( x int constraint p_pk primary key );
 
Table created.
 
ops$tkyte@ORA9IR1> create table c ( x constraint c_fk references p );
 
Table created.
 
ops$tkyte@ORA9IR1>
ops$tkyte@ORA9IR1> select constraint_name, owner, table_name
  2    from all_constraints
  3   where r_constraint_name = 'P_PK'
  4     and r_owner = user;
 
CONSTRAINT_NAME                OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_FK                           OPS$TKYTE
C
 
 
ops$tkyte@ORA9IR1>
ops$tkyte@ORA9IR1> alter table p drop constraint p_pk;
alter table p drop constraint p_pk
                              *
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
 
 
ops$tkyte@ORA9IR1> alter table c disable constraint c_fk;
 
Table altered.
 
ops$tkyte@ORA9IR1> alter table p drop constraint p_pk;
alter table p drop constraint p_pk
                              *
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
 
 
ops$tkyte@ORA9IR1> alter table c drop constraint c_fk;
 
Table altered.
 
ops$tkyte@ORA9IR1> alter table p drop constraint p_pk;
 
Table altered.
 

Thanks

A reader, August 09, 2005 - 7:48 pm UTC


Soma, March 28, 2006 - 5:54 am UTC

Dear Tom

I have one table with No Primary Key, but i have created a unique index of a particular field,
say ex. Customer table
for Cust_code field i have created the Unique index,

My question is IS there is any performance degradation when i am selecting this table with Cust_Code in the where Conditon, This table is having more than 1 lac records.



Tom Kyte
March 28, 2006 - 7:59 am UTC

the answer is always: it depends :)

In general, I'd have to say "no, the unique index in most cases is sufficient"

however, a PRIMARY KEY is more than a unique index (in fact, it need not have a unique index - that is not a requirement)

A primary key SAYS something.
A primary key is meta-data
A primary key's existence is used by the optimizer when optimizing.
A primary key is more than unique - it is also NOT NULL


So, even if this has zero effect on your particular runtime performance - you have still done it wrong. It is a primary key, state that fact (since you already have the index, adding the key will do nothing more than add more information to your database! What a bonus. It'll let you create FOREIGN KEYS to this table too!!!!)

Create Unique Constraint Without Unique Index

Saravanan Ramalingam, May 09, 2006 - 9:02 am UTC

Hi Tom,
Is it possible to create Unique Constraint on a table without creating an unique index.



Tom Kyte
May 09, 2006 - 9:53 am UTC

yes.

It can use a non-unique index. As, well, demonstrated above in the original answer ;)

SQL Expert contradicts

Anindya Mitra, May 24, 2006 - 6:14 am UTC

Hi Tom,
Please check the link below:
"</code> http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1168808_tax301455,00.html?track=NL-93&ad=547742" <code>
It says...
"Question - What does "the primary key of the table is enforced using a non-unique index" really mean?

This question posed on 23 February 2006


It means you got that nugget of information (hint: it's wrong) from an unverified source. <grin>

NOTE: If this was, by some remote chance, something that I wrote, then please let me know which article. I'll see to getting it corrected right away. It's obviously just a typo.

A primary key is implemented using a unique index, with a not null constraint..."

The Question was posted on Feb,06; so the answer is also recent.

Tom Kyte
May 24, 2006 - 7:17 am UTC

It (the quoted article) is obviously wrong, the example is staring at you right above.

You cannot contradict an example such as the above.

Smaller example:

ops$tkyte@ORA10GR2> drop table t;

Table dropped.

ops$tkyte@ORA10GR2> create table t
  2  ( x int primary key using index ( create index t_idx on t(x) ) )
  3  /

Table created.

ops$tkyte@ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          NONUNIQUE

ops$tkyte@ORA10GR2> drop table t;

Table dropped.

ops$tkyte@ORA10GR2> create table t
  2  ( x int primary key using index ( create UNIQUE index t_idx on t(x) ) )
  3  /

Table created.

ops$tkyte@ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          UNIQUE



The "currency" of an answer is no indication of it's correctness.


Also, the article says:

<quote>
That's why the database optimizer automatically builds an index when you declare a primary key. 
</quote>

that is technically not true. 

ops$tkyte@ORA10GR2> create table t
  2  ( x int,
  3    y int,
  4    z int
  5  )
  6  /

Table created.

ops$tkyte@ORA10GR2> create index MY_IDX on t(x,y,z);

Index created.

ops$tkyte@ORA10GR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte@ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
MY_IDX                         NONUNIQUE



See, the database did not create yet another index on T(x) - since an index that was good enough already exists.



 

Uniqueness in partitioned table.

Aru, September 24, 2008 - 2:16 am UTC

Hi Tom,
We have been asked to convert an existing table to a partitioned table.

CREATE TABLE SOURCE.TDDB_ROCMO_CONPOS_HIS1 (
 ID NUMBER(10) NOT NULL, 
 TRADING_DATE DATE NOT NULL, 
 TRADING_PERIOD 
 NUMBER(10) NOT NULL, 
 NODE VARCHAR2(8) NOT NULL, 
 NET_POSITION NUMBER(10), 
 MARKET_LOAD NUMBER(10), 
 CONTRACT_LOAD NUMBER(10), 
 SPOT_LOAD NUMBER(10), 
 CALCULATION_DATE DATE NOT NULL,
 LAST_UPDATE_TIME DATE NOT NULL)   
     PARTITION BY RANGE (TRADING_DATE) 
       (PARTITION TDDB_ROCMO_CONPOS_HIS1_P1 VALUES LESS THAN  (TO_DATE('2008-1-1','YYYY-MM-DD')) TABLESPACE TDDB_LOG_DATA ,
        PARTITION TDDB_ROCMO_CONPOS_HIS1_P2 VALUES LESS THAN  (TO_DATE('2009-1-1','YYYY-MM-DD')) TABLESPACE TDDB_LOG_DATA , 
        PARTITION TDDB_ROCMO_CONPOS_HIS1_P3 VALUES LESS THAN  (TO_DATE('2010-1-1','YYYY-MM-DD')) TABLESPACE TDDB_LOG_DATA);
         
CREATE INDEX SOURCE.IDX_TDDB_ROCMO_CONPOS_HIS1_TRADING_DATE ON SOURCE.TDDB_ROCMO_CONPOS_HIS1 (TRADING_DATE) LOCAL 

insert into tddb_rocmo_conpos_his1 (select * from tddb_rocmo_conpos_hist);

CREATE UNIQUE INDEX TDDB_CONPOS_ROCMO_HIS_UK on TDDB_ROCMO_CONPOS_HIS1(ID,TRADING_DATE) LOCAL(
 partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
 partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
 partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX);
 
ALTER TABLE tddb_rocmo_conpos_his1 ADD CONSTRAINT tddb_rocmo_conpos_his1_pk primary key(ID,TRADING_DATE) using index local (
 partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
 partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
 partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX);
 
Till here everything works fine. But when we go to create a composite unique index as below it gives error:-

 1  ALTER TABLE tddb_rocmo_conpos_his1 ADD CONSTRAINT tddb_rocmo_conpos_comp_hi
s1_uk unique (TRADING_DATE,trading_period,node,net_position,market_load,contract
_load,spot_load) using index local (
  2     partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
  3     partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
  4*    partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX)
SQL> /
ALTER TABLE tddb_rocmo_conpos_his1 ADD CONSTRAINT tddb_rocmo_conpos_comp_his1_uk unique (TRADING_DATE,trading_period,node,net_position,market_load,contract_load,spot_load) using index local (
                                                
ERROR at line 1:
ORA-02299: cannot validate (SOURCE.TDDB_ROCMO_CONPOS_COMP_HIS1_UK) - duplicate
keys found


The same constraints work fine in heap organized table and the data is exactly the same. Please can you explain if this is specific to partitioned tables or is my methodology wrong as I am trying to create local indexes here?

Tom Kyte
September 24, 2008 - 7:18 pm UTC

do exceptions into - see what data is bad - for you see, unless I can reproduce it... I cannot see it....

I don't have your data - but an exceptions into might be "telling"

Unique index.

Aru, September 24, 2008 - 8:12 pm UTC

Thanks Tom,
Will do the exceptions into and check the results.
One real basic question I have here:-

I got the DDL to create the table using TOAD from production.

Instead of doing:-
CREATE UNIQUE INDEX TDDB_CONPOS_ROCMO_HIS_UK on TDDB_ROCMO_CONPOS_HIS1(ID,TRADING_DATE) LOCAL(
partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX);

ALTER TABLE tddb_rocmo_conpos_his1 ADD CONSTRAINT tddb_rocmo_conpos_his1_pk primary key(ID,TRADING_DATE) using index local (
partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX);


Won't just creating a primary key on the two columns give the same result? Why first create a unique index on the columns and then add primary key, why not just create a primary key and it would create the unique index as well?

Regards,
ARU.

Constraints

Aru, September 24, 2008 - 10:35 pm UTC

Hi Tom,
Just did a quick test where tddb_rocmo_conpos_his is the original table. I created two tables tddb_rocmo_conpos_his2 and tddb_rocmo_conpos_his3, where tddb_rocmo_conpos_his3 is partitioned. But when after creating the constraints the result is different, as below.
Don't think it should happen even if there are duplicate data in the table. Please can you check?

1  CREATE TABLE TDDB_ROCMO_CONPOS_HIS2 (
2      ID NUMBER              NOT NULL,
3      TRADING_DATE DATE      NOT NULL,
4      TRADING_PERIOD NUMBER  NOT NULL,
5      NODE VARCHAR2(8)       NOT NULL,
6      NET_POSITION           NUMBER,
7      MARKET_LOAD            NUMBER,
8      CONTRACT_LOAD          NUMBER,
9      SPOT_LOAD              NUMBER,
10      CALCULATION_DATE DATE  NOT NULL,
11      LAST_UPDATE_TIME DATE  NOT NULL,
12      CONSTRAINT TDDB_RCPH_PK2 PRIMARY KEY(trading_date,ID) USING INDEX TABLE
        SPACE TDDB_LOG_INDEX,
13      CONSTRAINT TDDB_RCPH_UK2 UNIQUE(TRADING_DATE, TRADING_PERIOD, NODE, NET
    _POSITION, MARKET_LOAD, CONTRACT_LOAD, SPOT_LOAD) USING INDEX
14*     TABLESPACE TDDB_LOG_INDEX )
SQL> /
Table created.
SQL> insert into TDDB_ROCMO_CONPOS_HIS2 (select * from TDDB_ROCMO_CONPOS_HIS);
    
9305249 rows created.



  1  CREATE TABLE TDDB_ROCMO_CONPOS_HIS3 (
  2             ID NUMBER(10)                   NOT NULL,
  3             TRADING_DATE DATE               NOT NULL,
  4             TRADING_PERIOD NUMBER(10)       NOT NULL,
  5             NODE VARCHAR2(8 byte)           NOT NULL,
  6             NET_POSITION            NUMBER(10),
  7             MARKET_LOAD                     NUMBER(10),
  8             CONTRACT_LOAD           NUMBER(10),
  9             SPOT_LOAD                       NUMBER(10),
 10             CALCULATION_DATE DATE   NOT NULL,
 11             LAST_UPDATE_TIME DATE   NOT NULL,
 12             CONSTRAINT TDDB_ROCMO_CONPOS_HIS3_PK3 PRIMARY KEY(trading_date,I
D) USING INDEX TABLESPACE TDDB_LOG_INDEX,
 13                CONSTRAINT TDDB_RCPH_UK3 UNIQUE(TRADING_DATE, TRADING_PERIOD,
 NODE, NET_POSITION, MARKET_LOAD, CONTRACT_LOAD, SPOT_LOAD) USING INDEX
 14                TABLESPACE TDDB_LOG_INDEX)
 15             PARTITION BY RANGE (TRADING_DATE) (
 16             PARTITION TDDB_ROCMO_CONPOS_HIS3_P1 VALUES LESS THAN (TO_DATE('2
008-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPA
CE TDDB_LOG_DATA ,
 17             PARTITION TDDB_ROCMO_CONPOS_HIS3_P2 VALUES LESS THAN (TO_DATE('2
009-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPA
CE TDDB_LOG_DATA,
 18*            PARTITION TDDB_ROCMO_CONPOS_HIS3_P3 VALUES LESS THAN (TO_DATE('2
010-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPA
CE TDDB_LOG_DATA)
SQL> /

Table created.

SQL> insert into TDDB_ROCMO_CONPOS_HIS3 (select * from TDDB_ROCMO_CONPOS_HIS);
insert into TDDB_ROCMO_CONPOS_HIS3 (select * from TDDB_ROCMO_CONPOS_HIS)
*
ERROR at line 1:
ORA-00001: unique constraint (SOURCE.TDDB_RCPH_UK3) violated

Regards,
Aru.

Tom Kyte
September 25, 2008 - 3:10 pm UTC

Aru,

unless I can reproduce it - and since I don't have your data - I cannot right now, I'll just ask you to do what I asked you to do - in the hopes we can get a reproducible test case. Use the exceptions into, find the offending row(s), create a tiny - teeny tiny - complete test case - then, if it is a bug, we are ready to go to support. If it is not a bug, it will be obvious what it is.

I haven't really parsed your sql here too intensely - I'm waiting for the teeny tiny test case, exceptions into will be the *first* step towards that

version and OS information would be useful as well.

it looks like a bug, but I'll need something to reproduce with. The question is whether

a) the non-partitioned table is the bug
b) the partitioned table is the bug

eg: is there actually duplicate data that one is not catching, or is there no duplicate data that the other is erroneously reporting.

partitoned local index key not in PK

Walker, November 27, 2008 - 3:25 pm UTC

Hi ,

I have a table partitioned on col b but the PK is col a . Is it possible to use local partitioned index (unique / non unique )for PK ?

When I try to create a local unique index on a it disallows saying that b should be a sub set of the local unique index .

When I create a non unique index on a , it succeeds but when I try to enforce PK on a it probably tries to create a global index ............ I don not wish to use Global Index for obvious truncate reasons ..... does it mean that in order to have PK on locally partitioned indexes the partitioned key needs to be in the PK ?
Tom Kyte
November 28, 2008 - 5:30 pm UTC

no, it is not.


you have to create a global index for that.


Your truncate reasons are far from obvious? In current releases of the software you do not have to ever lose availability of the data due to a global index becoming unusable if you do not want to. So, what is the "obvious" reason?

a very long unique key

A reader, January 13, 2012 - 7:35 am UTC

Dear Tom,

We have a very important table say t1(id, col1,col2,...................,col35)

where id is a primary key.

Actually, we are communicating information about that table to several external applications which are giving us feedback again using this primary key (id) as a matching criteria. We would like to change our (id) matching criteria by a functionnal unique key. The problem is that our functionnal unique key contains 10 columns uk(col1,col2,.....col10);

I am not very confident with creating a unique key having 10 columns in it.

Do you have any other proposition?

Thanks in advance

I searched your site about a question looking
Tom Kyte
January 17, 2012 - 9:27 am UTC

We would like to change our (id) matching criteria by a
functionnal unique key.


why? what goal are you attempting to meet?

given that ID already exists, and that communicating with external sources is already done via ID and ID is a single column (which is going to be easier all around than a 10 column primary key) - why wouldn't you stick with ID?

what is the goal behind making this change, why are you thinking about doing this? If we understand that, we might be able to suggest something.

A reader, January 18, 2012 - 2:06 am UTC

Thank you very much for taking time to answer my question

"what is the goal behind making this change, why are you thinking about doing this? If we understand that, we might be able to suggest something"

The client for which I am working now, wants that all communications with externals applications, from now and on, start to be done via a functional key. He doesn't want us to continue using this primary key id.

That would have been easy for us if we have identified a unique fonctional key with 2 or 3 columns. Unfortunately, the sole unique functional key we've identified is formed with 10 columns. And I am not so confident with creating this 10 columns unique key; hence my question.

Best regards
Tom Kyte
January 18, 2012 - 7:34 am UTC

tell this client

a) there is no such term as a functional key, I believe you mean natural key.

b) you are the client, you are not the software developer

c) it would not make sense to make this change

d) but if you want to pay us 1,000,000 dollars, we'll gladly cut you a custom version that will not run as well as the existing code.


why would a client care about such a thing? They either buy your product or not and your product works technically the way it works.


It would not make sense to me (given the above information - that is all I'm working from) to change an interface from a nice single column surrogate key to an unwieldy 10 column 'natural key'.



(are all 10 of these columns defined as NOT NULL?)

A reader, January 19, 2012 - 3:31 am UTC

Yes, all of thoses 10 columns are declared to be NOT NULL. Does this change anything relatively to your last answer?

Definitely I agree with you.

Thanks a lot
Tom Kyte
January 19, 2012 - 5:29 am UTC

I was hoping one of them would be NULLABLE. that would preclude them from being a primary key.


Good luck, I would just tell them "no, it doesn't work that way" personally. Unless they want to personally foot the bill (plus some) to do the work to make things that much harder for everyone.

A reader, April 26, 2012 - 2:38 pm UTC

Hi tom,

oracle use existing index to polish the unique/primary key constraint.

can you show in which scenario it will be apllicable?

Thanks


Tom Kyte
April 26, 2012 - 3:37 pm UTC

ops$tkyte%ORA11GR2> create table t as select * from all_users;

Table created.

ops$tkyte%ORA11GR2> create index t_idx on t(user_id);

Index created.

ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key(user_id);

Table altered.

ops$tkyte%ORA11GR2> select index_name from user_indexes;

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

ops$tkyte%ORA11GR2> 



not sure what you mean truly - but there is an example of the database using an existing index - T_PK needs an index to enforce itself, the only index is the one I created before the constraint was there.