Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index (Oracle 10.2 Enterprise Edition, RHEL4)
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index [message #417010] Wed, 05 August 2009 12:52 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I'm attempting to create a unique compound partitioned index and getting the below:
SQL> create unique index nonmem_unq_tony on tony(emailaddress, websiteid) local tablespace idx_test01;
create unique index nonmem_unq_tony on tony(emailaddress, websiteid) local tablespace idx_test01
                                                *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index


Searching around, I find the explanation:
Quote:

Oracle Error :: ORA-14039

partitioning columns must form a subset of key columns of a UNIQUE index
Cause

User attempted to create a UNIQUE partitioned index whose partitioning columns do not form a subset of its key columns which is illegal
Action

If the user, indeed, desired to create an index whose partitioning columns do not form a subset of its key columns, it must be created as non-UNIQUE; otherwise, correct the list of key and/or partitioning columns to ensure that the index" partitioning columns form a subset of its key columns



Neither of these fields are my partition key. Is there any other way I can create a unique compound partitioned index on this table? Or, as I suspect, am I just out of luck?

Thanks.

[Updated on: Wed, 05 August 2009 13:03] by Moderator

Report message to a moderator

Re: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index [message #417011 is a reply to message #417010] Wed, 05 August 2009 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can do it as a global index not a local one.

Regards
Michel
Re: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index [message #417012 is a reply to message #417011] Wed, 05 August 2009 13:19 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Yeah, that's the problem. I was hoping to create local partitioned indexes so I could purge old partitions without affecting the usability of the indexes. Oh well.
Thanks for your response.
Re: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index [message #417013 is a reply to message #417012] Wed, 05 August 2009 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, you have to add the partition key to the index, this should not be a problem.

Regards
Michel
Re: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index [message #417016 is a reply to message #417013] Wed, 05 August 2009 13:40 Go to previous message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Perfect, you're absolutely right. I added the partition key and I created the unique index.
create unique index nonmem_unq_tony on tony(emailaddress, websiteid, addeddate) local tablespace idx_test01;


Thanks for your help!
Previous Topic: Replacing First Five Digits in SSN
Next Topic: ora-00928 Please Help!
Goto Forum:
  


Current Time: Thu Dec 08 14:40:23 CST 2016

Total time taken to generate the page: 0.08491 seconds