RE: Can I locally partition my primary key constraint index?

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Fri, 03 Aug 2001 17:12:02 -0700
Message-ID: <F001.0035F5DE.20010803071238@fatcity.com>

Cherie,
I handle it a bit differently.
Here is what I have been using with no problems.  ALTER TABLE DBM.CUST_CATEGORY_TOTALS
   ADD CONSTRAINT CUSTCT_PK PRIMARY KEY

      (
      DIR_PUB_YR,   <----- This is partitioning key for the table partitions
      DIR_NUM,
      CUST_ID,
      ITEM_TYPE_CDE,
      SYSTEM_SOURCE_CDE)

  USING INDEX
   LOCAL
   (
   PARTITION CUST_CT_PK_92 <--- 1992 partition ... and so on..    PCTFREE 5
   INITRANS 2
   MAXTRANS 255
   TABLESPACE CUST_CT_PK_92
   STORAGE
   (

      INITIAL 73400320
      NEXT 5242880
      MINEXTENTS 1
      MAXEXTENTS 50
      PCTINCREASE 0

   ) NOLOGGING,
   PARTITION CUST_CT_PK_93
   PCTFREE 5
   INITRANS 2
   MAXTRANS 255
   TABLESPACE CUST_CT_PK_93
   STORAGE

   ................................... 

   ...... and so on..

HTH, Regards,

• Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]
> Sent: Friday, August 03, 2001 9:48 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Can I locally partition my primary key constraint index?
>
>
> Kevin,
>
> Have you actually done this?
>
> I have a test copy of the database now and have been testing this.
> I successfully created a locally partitioned index and then tried
> to add the primary key constraint to the same table using the same
> columns (in the same order) as the index was created using and
> I got the following error:
>
> ORA-01408: such column list already indexed.
>
> Is there any way to tell the alter table create constraint command
> not to try to create a new index?
>
> Thanks,
>
> Cherie
>
>
>
>
> "Toepke, Kevin
>
> M" To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com>
> <ktoepke_at_cms.ce cc:
>
> ndant.com> Subject: RE: Can I
> partition my primary key constraint index?
> Sent by:
>
> root_at_fatcity.co
>
> m
>
>
>
>
>
> 05/14/01 12:36
>
> PM
>
> Please respond
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
> Ok, I'll jump in here 'cause I may be able to simplify the issue.
>
> Starting with Oracle 8, you can create a Primary Key that uses an existing
> index. This makes the administration stuff easier. You can create your own
> locally partitioned unique index (using regular CREATE INDEX syntax).
>
> Once the index is created, you can create the PK (ALTER TABLE x ADD
> CONSTRAINT y PRIMARY KEY (z);). The existing index will be used if Oracle
> can use it.
>
> This works with partitioned tables as long as the PK index is
> equipartitioned with the base table.
>
> Kevin
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

Received on Fri Aug 03 2001 - 19:12:02 CDT