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

Thread: Partitions-Urgent plz

  1. #1
    Join Date
    Feb 2007
    Posts
    20

    Partitions-Urgent plz

    If i have composite index on(deptno,job) columns and i am doing partitioning on "sal"(salary column) ...so here sal column in not part of composite index.

    still it is said that we can have 'sal' column as partition key column.

    But why can sal only be "non-unique"..why only deptno and job if used as partition key column can be "unique" ???
    Last edited by janki_mehta; 03-15-2007 at 06:34 AM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The question isn't clear to me. You can partition on columns that are constrained to be unique -- if you have a column that is unique and you do not partition by it then the uniqueness constraint must be supported by a global index. Is that what you mean?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Let me see if I understand your scenario.

    You have a table somehow partitioned by SAL column.
    You have an composite index on columns (deptno,job) pointing to that partitioned table.
    You are asking why you can't have an unique index on SAL column.

    Did I get it right?

    Now I'm assuming you are trying to build your unique index on SAL column as locally partitioned. Did I get it right?

    If this is the case, the answer is "local unique indexes must contain all of the columns of the partition key."

    You can always build a non-partitioned index on SAL column and define it as unique, no problem at all.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Just remembered the story of five blind men & an elephant...

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by PAVB
    You have a table somehow partitioned by SAL column.
    You have an composite index on columns (deptno,job) pointing to that partitioned table.
    You are asking why you can't have an unique index on SAL column.

    Did I get it right?

    Now I'm assuming you are trying to build your unique index on SAL column as locally partitioned. Did I get it right?

    If this is the case, the answer is "local unique indexes must contain all of the columns of the partition key."

    You can always build a non-partitioned index on SAL column and define it as unique, no problem at all.
    If the table is partitioned on SAL then a unique constraint on SAL can be supported by a local index. The index on (dept, job) is irrelevant.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    You can have a unique index on your partition key, using global & local indexes.

    You can have a unique index on non-partition keys using global indexes.

    You can have a unique composite index using global indexes if the leading edge of the index does not contain the partition key.

    You can have a unique composite index using local indexes if the leading edge of the index is the partition key.

    Oracle cant't inspect multiple indexes to verify a uniqueness constraint, so you can't have a unique composite local index UNLESS the composite index includes the partition key as the leading column.

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