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

Sean,

You are defining the index to be local. That means you want the partitioning of the index "the same way" as the table partitioning. Your partitioning key is (last) on the table, naturally the local index has to be partitioned on the same key, too. When you define a primary key constraint, the key must be unique within the partition. If the key is (id), then searching within a partition is pointless; oracle must search all partitions of the index to see if there is a duplicate. It defeats the purpose of a local index.

You have two choices -

i) either define the primary key as (last, id) ii) create a global index, partitioned or not

In the latter case you lose some of the advantages of a partitioned table, namely every time you doa partiton operation on the table, the index becomes unusable and will need maintenance. On the other hand the former option may introduce duplicate values on id which you may not want.

HTH. Arup

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, January 10, 2003 3:18 AM

>
> 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
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: From
> INET: l-oracle_at_hypno.iheavy.com
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: arupnanda_at_hotmail.com

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).