Paritioning Challenge: alternate unique constraint

From: Piet de Visser <piet.de.visser_at_logicacmg.com>
Date: Tue, 9 Mar 2004 08:45:54 +0100
Message-ID: <D1D50F9C5151D2119FF000104BB3F9B7051B47E1@NL-ROT-MAIL02>

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)

)
-- partition by hash ( emp_id )
-- partitions 4 store in ( tools, users ) partition by range (emp_id) (
  partition p1 values less than (10),
  partition p2 values less than (maxvalue) ) ;

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:

• We want another unique constraint (+ index), that does not include the partition-key.
• We want any index to be local for maintenance reasons.
  */
  

/*

  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.

---

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:43:15 CST