Re: primary key, local partition index - prefixed/non-prefixed

Sean,

In your case the PK would need to be on (Last, ID) in order for the index to be Local. Otherwise, it's gotta be Global or else you need to change your partition key.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
JApplewhite_at_austin.isd.tenet.edu

                                                                                                           
                      From                                                                                 
                      <l-oracle_at_hypno.i        To:       Multiple recipients of list ORACLE-L              
                      heavy.com>                <ORACLE-L_at_fatcity.com>                                     
                      Sent by:                 cc:                                                         
                      root_at_fatcity.com         Subject:  primary key, local partition index -              
                                                prefixed/non-prefixed                                      
                                                                                                           
                      01/10/2003 02:18                                                                     
                      AM                                                                                   
                      Please respond to                                                                    
                      ORACLE-L                                                                             
                                                                                                           
                                                                                                           

Hi all...

I'm messing around with partitioning, and trying to create a primary key index which is locally partition to match the underlying table. Table looks something like this:

create table mytable (
id number,
last date)
tablespace data
storage (initial 64k next 64k pctincrease 0) partition by range (last)
(partition lastq1_03 values less than(to_date
('01-APR-2003','DD-MON-YYYY')),

 partition lastq2_03 values less than(to_date
('01-JUL-2003','DD-MON-YYYY')),

 partition lastq3_03 values less than(to_date
('01-OCT-2003','DD-MON-YYYY')),

 partition lastq4_03 values less than(to_date
('01-JAN-2004','DD-MON-YYYY')))

enable row movement;

SQL> alter table mytable add primary key (id) using index tablespace "INDX" local enable;
alter table mytable add primary key (id) using index tablespace "INDX" local enable
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

Basically what Oracle is saying is hey, your table is partitioned on last, and you want to create a local index on id, no can do. So how *DO* I do that, and for that matter, how do I manage with a foreign key constraint?

I've looked at the docs, but I really need a good example. If anyone can point me in the right direction, I'd appreciate it.

Thanks,
Sean

--
Author: From
  INET: l-oracle_at_hypno.iheavy.com




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: JApplewhite_at_austin.isd.tenet.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).