RE: Can I partition my primary key constraint index?

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Mon, 14 May 2001 10:09:37 -0700
Message-ID: <F001.00302C37.20010514101555@fatcity.com>

Hi Cherie/Diana,
 Here is how to do this.... Precreate the required tablespaces.

ALTER TABLE DBM.customer_listings
ADD CONSTRAINT custlist_pk PRIMARY KEY

    (

        DIR_PUB_YR,
        CUST_ID,
        SEQ_NUM,
        SYSTEM_SOURCE_CDE

   )
   USING INDEX
   LOCAL

      (
        PARTITION cust_list_92_pk
                  PCTFREE 5
                  TABLESPACE cust_list_pk_92
                  STORAGE (initial 60M next 5M maxextents 10 pctincrease 0),
        PARTITION cust_list_93_pk
                  PCTFREE 5
                  TABLESPACE cust_list_pk_93
                  STORAGE (initial 60M next 5M maxextents 10 pctincrease 0),
        PARTITION cust_list_94_pk
     
       ............. and so on....

HTH.. Regards,
- Kirti Deshpande
  Verizon Information Services
   http://www.superpages.com

> -----Original Message-----
> From: Diana_Duncan_at_ttpartners.com [SMTP:Diana_Duncan_at_ttpartners.com]
> Sent: Monday, May 14, 2001 12:31 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Can I partition my primary key constraint index?
>
>
> Cherie,
>
> Hm, good question. I think I remember (unfortunately, this was at my
> previous job, so I don't have the scripts) that in order to specify LOCAL,
> you lost control of the storage parameters -- they automatically default
> to
> the same as the table. But it's certainly worth a try.
>
> I created separate data and index tablespaces for each partition, which
> made the dropping of a partition and it's associated space quite simple.
>
> Diana Duncan
> TITAN Technology Partners
> One Copley Parkway, Ste 540
> Morrisville, NC 27560
> VM: 919.466.7337 x 316
> F: 919.466.7427
> E: Diana_Duncan_at_ttpartners.com
>
>
>
>
> Cherie_Machler
>
> @gelco.com To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent by: cc:
>
> root_at_fatcity.c Fax to:
>
> om Subject: Re: Can I partition
> my primary key constraint index?
>
>
>
>
> 05/14/2001
>
> 10:17 AM
>
> Please respond
>
> to ORACLE-L
>
> Diana,
>
> Can I build the initial local primary key index using the storage and
> tablespace clause to place the index in the correct index tablespace
> to begin with. This index is multiple gigs and my database is tight
> on space right now. I'm not sure that I have enough room to put
> the index in the data tablespace and then move it to the appropriate
> index partition tablespaces?
>
> Do you put each of your primary key index partitions in a separate
> tablespace?
>
> The last DBA put all of our indexes into three tablespaces
> (small, medium, large) which doesn't work very well when you
> go to get rid of a partition.
>
> Thanks,
>
> Cherie
>
>
>

-- 
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 Mon May 14 2001 - 12:09:37 CDT