order of columns in the primary key
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: order of columns in the primary key

  1. #1
    Join Date
    Feb 2000
    Posts
    142
    Hi,
    Does it matter if the columns are changed in the Primary key?

    Please help!!

    Thanks.



  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    It depends. The PK create an index, if you have specific where criteria which makes use of the compsite primary key you are creating, try to create PK column order such a way it benifits your queries.
    Reddy,Sam

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    In a concatenated Index, the order of columns are important. The leading (first) column should be of a high cardinality value, followed by other columns in that order.
    Let us consider an example:
    Table T has 5 columns, viz colA, colB, colC , colD and colE. Now you want a concatenated index on columns colC + colB+ColE.
    Before creating the index, check the distinct values of columns C, B and E.
    C has 200 distinct values, B has 400 and E has 1000 values.

    In that case, I would prefer to create the index with ColE+ColB+ColC.
    Because of the high cardinality in the first columns, your index will be evenly balanced.


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tamilselvan
    In a concatenated Index, the order of columns are important. The leading (first) column should be of a high cardinality value, followed by other columns in that order.
    With the appearance of index compression in Oracle8i, the order of columns should be just the opposite if the cardinality is the only deciding factor. Columns with least cardinality should be the leading columns in the composite index and not the other way around.

    So in tamiselvan example the order of columns in index should be C-B-E and not E-B-C. And of course index should be compressed.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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