RE: Paritioning Challenge: alternate unique constraint
- From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Mar 2004 01:00:01 -0700
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Piet de Visser
Sent: Tuesday, March 09, 2004 12:46 AM
To: 'oracle-l@xxxxxxxxxxxxx'
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)
)
-- 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
