Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    73

    Unanswered: how many columns can i have for PK

    UDB : v8.2
    OS : Solaris

    How many columns can i have for a PK.
    I am seeing 4 -5 columns as PK for few tables?
    what is the impact of having these many columns as PK.

    Will it have any effect while loading ,deleting data?

    Any inputs are welcome

    thanks

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    Any time you update/delete , you made changes to the indexes as part of
    automatic index maintainence(done by db2), so more columns also more log
    generation.


    regards,

    mujeeb

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    According to the manual (SQL Reference - Vol 1 - Appendix A - SQL Limits, 16 columns is the limit for an index (which is what a PK will be).

    We have several tables that have 4-6 columns for the PK. The performance seems to be OK. The only drawback is doing joins to these tables and having to write the sql for all those columns.

    Andy

  4. #4
    Join Date
    Mar 2005
    Posts
    73
    thanks Mujeeb and AR Winner

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The total length of the PK, and the order of the columns, is more important than the number of columns. If possible, put columns with higher cardinarlity up front, even if it does not seem as logical.

    However, if any of the columns of the PK are changed, that could slow things down a bit, and in that case it is often advisable to use non-meaningfull keys.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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