| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Paritioning Challenge: alternate unique constraint
As I understand it, you want to create local indexes on a partitioned table that do not include the partition key.
Logically, this sort of construct doesn't strike me as possible. Since uniqueness has to apply to the whole table, you logically need to, in this case, have a single object to store all possible first & last names. This would require a global index. If you did have a number of local indexes, Oracle would have to scan each index before it inserted a new row in any partition, which would likely be a rather poorly performing option.
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Piet de Visser
Sent: Tuesday, March 09, 2004 12:46 AM
To: 'oracle-l_at_freelists.org'
Subject: Paritioning Challenge: alternate unique constraint
List,
Cannot imagine others have not already been-there-done-this...
Basically,
a partitioned table can not have a unique constraint that does not include
the part-key.
Or so it seems.
How limiting that can be is illustrated below.
Try the following code in sqlplus,
and let me know your ideas.
I would like a workaround.
---->
spool ct_emp_part
set echo on
set ver on
/*
We are using a table to hold
person-data (uhm-ploo-jeees)
*/
drop table emp_part ;
create table emp_part
(
emp_id number , emp_first varchar2(20) , emp_last varchar2(20)
CREATE UNIQUE INDEX emp_part_pk ON emp_part
( emp_id )
local
;
ALTER TABLE emp_part ADD (
CONSTRAINT emp_part_pk PRIMARY KEY ( emp_id )
);
/*
This is where trouble starts:
/*
Unique index : This will not work,
local index must include part-key
*/
CREATE unique INDEX emp_part_u ON emp_part
( emp_last, emp_first )
local
;
/*
non-unique local-index,
and then try to enforce a constraint on that:
Index works fine, but constraint fails..
*/
CREATE INDEX emp_part_u ON emp_part
( emp_last, emp_first )
local
;
ALTER TABLE emp_part ADD (
CONSTRAINT emp_part_u1 UNIQUE ( emp_last, emp_first )
using index
);
drop index emp_part_u ;
/*
Unique Constraint : This will create a global-index,
to be invalidated on any partition maintenance.
*/
ALTER TABLE emp_part ADD (
CONSTRAINT emp_part_u UNIQUE ( emp_last, emp_first )
);
/*
Ideas anyone ?
With a bit of luck, I just failed to RTFM,
I cannot image there is not some
sort of solution or workaround to this...
*/
spool off
<-----
Regards,
PdV
(and a big Hi
to all the SDOF ppl out there lurking)
This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Mar 09 2004 - 01:53:46 CST
 |
 |