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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
