DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Primary Key and Partitioned Tables

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Question Primary Key and Partitioned Tables

    I have read the manual and Metalink and still do not get it. I want to create a partitioned table by range(one per month). I want a primary key that is also paritioned(not by the month). I also want to be able to drop a month partition and add a month parition every month.

    Oracle recommends a Global Partitioned Index to enforce the primary key on the table. When I drop the partition and add the partition this index will become unusable? Is there a way to do this where the primary key index does not become unusable?? Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Primary Key and Partitioned Tables

    Originally posted by lesstjm
    Oracle recommends a Global Partitioned Index to enforce the primary key on the table. When I drop the partition and add the partition this index will become unusable?

    yup

    Is there a way to do this where the primary key index does not become unusable??
    Hopefully, you're on 9. You can tell the drop operation and the add operation to rebuild the global indexes.
    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    Posts
    515

    Another Question

    We have a paritioned table by date range. On that table we have a local index that matches the primary key constraint. The question is when a record is inserted does oracle search every local index to make sure that value is not in the table?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    No, I don't believe it would. I knows what partition the new value would fall in, so it only has to touch one index partition.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jan 2001
    Posts
    515

    We don't know what partiton it falls in.

    If I have a partition by month and the local index and primary key are on account number. When I insert a record in the table it would have to look in all the local indexes to see if that account number is there. At least I think this is what happens??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width