Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    50

    Unanswered: Define PRIMARY KEY as CLUSTER

    Hi

    I use UDB 8.1 / Unix/Windows. I want to define a foreign key on table B which references table A AND to have the key from table A as CLUSTER.

    ALTER TABLE A
    ADD CONSTRAINT A_KEY
    PRIMARY KEY (A_ID)

    ALTER TABLE B
    ADD CONSTRAINT B_KEY
    PRIMARY KEY (B_ID)
    ADD CONSTRAINT B_REF_1
    FOREIGN KEY (A_ID)
    REFERENCES A
    ON DELETE CASCADE

    How can I define A_KEY as CLUSTER

    Marc

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: Define PRIMARY KEY as CLUSTER

    Marc,
    First it can be done.

    1) create cluster,unique index on table A that will be the primary key
    2) create the primary key
    3) create the foreign key.

    That being stated, I would have to question why you want to cluster on a unique value. Clustering allows DB2 to be able to use prefetching more efficiently, which means it works best when you have a low cardinality on the values being clustered. Unique values have a high cardinality (in respect to the total number of rows). In using clustering, you want DB2 to physically group together data that is generally accessed together. This is a big boon to performance. Using high cardinality clustering can actually will kill performance in some circumstances.

    I am not saying not to set up the primary key as a cluster, just make sure there are no other indexes that would benifit better from clustering.

    HTH

    Andy

    Originally posted by c149187
    Hi

    I use UDB 8.1 / Unix/Windows. I want to define a foreign key on table B which references table A AND to have the key from table A as CLUSTER.

    ALTER TABLE A
    ADD CONSTRAINT A_KEY
    PRIMARY KEY (A_ID)

    ALTER TABLE B
    ADD CONSTRAINT B_KEY
    PRIMARY KEY (B_ID)
    ADD CONSTRAINT B_REF_1
    FOREIGN KEY (A_ID)
    REFERENCES A
    ON DELETE CASCADE

    How can I define A_KEY as CLUSTER

    Marc

  3. #3
    Join Date
    Jul 2003
    Posts
    50
    Hi Andy

    We have a data-warehouse-like application.

    Table A = UserSearchCriteria, PrimaryKey: UserId, SearchNr
    Table B = UserSearchResults, PrimaryKey: UserId, SearchNr, SequenceNr
    ...
    <other tables with customer data>

    The user can dynamically (at runtime) create a SQL-search (table A) on customer data and save the results (up to 10000 records , table B). If there are a lof of concurrent users, the INSERTs are slow, because all users put their data at the end of the file at the same time.

    So, I want to force UDB NOT to put the data at the same position. I do this by :
    1). CLUSTER-Index on UserId, SearchNr
    2). CLUSTER-Index on UserId, SearchNr, SequenceNr
    3) Both indexes must have a high FREESPACE (PCTFREE)
    4) REORG
    -> When user 1 with PKey abc inserts data, and user 2 with PKey xyz at the same time, too, so UDB avoids a "HotSpot" at the end of file by trying to place the data at different positions. I made tests, and I could significantly improve performance on mass-INSERTS with concurrent users.

    I think, I will also benefit from PREFETCH on table A and B, because I search just with a part of the PKey (Table A: UserId, Table B: UserId, SearchNr), therefore having a lower cardinality.

    Marc

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Marc,
    I must of misunderstood your original post. You want to cluster Table B on the foreign key to Table A? I thought you meant to cluster table A on its primary key. By all means cluster B the way you want it. Another
    way you maybe able to increase performance on mass inserts is to use a DMS tablespace for the tables.

    HTH

    Andy

    Originally posted by c149187
    Hi Andy

    We have a data-warehouse-like application.

    Table A = UserSearchCriteria, PrimaryKey: UserId, SearchNr
    Table B = UserSearchResults, PrimaryKey: UserId, SearchNr, SequenceNr
    ...
    <other tables with customer data>

    The user can dynamically (at runtime) create a SQL-search (table A) on customer data and save the results (up to 10000 records , table B). If there are a lof of concurrent users, the INSERTs are slow, because all users put their data at the end of the file at the same time.

    So, I want to force UDB NOT to put the data at the same position. I do this by :
    1). CLUSTER-Index on UserId, SearchNr
    2). CLUSTER-Index on UserId, SearchNr, SequenceNr
    3) Both indexes must have a high FREESPACE (PCTFREE)
    4) REORG
    -> When user 1 with PKey abc inserts data, and user 2 with PKey xyz at the same time, too, so UDB avoids a "HotSpot" at the end of file by trying to place the data at different positions. I made tests, and I could significantly improve performance on mass-INSERTS with concurrent users.

    I think, I will also benefit from PREFETCH on table A and B, because I search just with a part of the PKey (Table A: UserId, Table B: UserId, SearchNr), therefore having a lower cardinality.

    Marc

  5. #5
    Join Date
    Jul 2003
    Posts
    50
    First, a correction:

    FALSE: Table B = UserSearchResults, PrimaryKey: UserId, SearchNr, SequenceNr
    RIGHT:Table B = UserSearchResults, PrimaryKey: SequenceNr (identity column), CLUSTER index: UserId, SearchNr

    I also want to cluster table A, but I think this wouldn't be necessary, whereas in the case of table B, it would be.

    - What's about db2empfa (Enable Multi-page File Allocation) for SMS tablespaces. Can it be recommended ?

    - APPEND ON: Does this option not cause concurrency problems

    Marc

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Marc,
    I do not have any experience with db2empfa as well as append mode.
    Although according to the manual, append mode may increase performance, especially if no deletes are ever performed. I am fairly certain the IBM would efficiently handle concurrency issues here. The very next paragraph in the manual mentions your method of using the cluster index for insert performance.

    Andy

    Originally posted by c149187
    First, a correction:

    FALSE: Table B = UserSearchResults, PrimaryKey: UserId, SearchNr, SequenceNr
    RIGHT:Table B = UserSearchResults, PrimaryKey: SequenceNr (identity column), CLUSTER index: UserId, SearchNr

    I also want to cluster table A, but I think this wouldn't be necessary, whereas in the case of table B, it would be.

    - What's about db2empfa (Enable Multi-page File Allocation) for SMS tablespaces. Can it be recommended ?

    - APPEND ON: Does this option not cause concurrency problems

    Marc

  7. #7
    Join Date
    Dec 2002
    Posts
    134
    I do not think append and clustrering can coexist well. If I understand correctly, they are completly different by nature.

    Append on - will insert records after HWM (no searching for the free space on the page).
    Cluster - will try to insert records next to each other(index over the time can become not clustered) .

    regards,
    dmitri

  8. #8
    Join Date
    Jul 2003
    Posts
    50
    Hi

    I think, UDB does not permit to create a table with APPEND ON and an index that is defined as CLUSTER.

    Marc

Posting Permissions

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