Results 1 to 4 of 4

Thread: Clustered Index

  1. #1
    Join Date
    May 2003
    Posts
    13

    Unanswered: Clustered Index

    In DB2 when you don't specify the clustered index what will happen?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Then when DB2 writes the data to disk, the pages that contain the data will essentially have random data on them. This will cause reduced performance for large tables because DB2 cannot use prefetching to read the pages from disk. Since you can only specify one cluster index, then chose one that will be used to access the table most often. For example, if you have a table that is queried often for a date range, then the date field should be the primary column in a cluster index.

    HTH

    Andy

  3. #3
    Join Date
    Apr 2003
    Location
    Trier, Germany
    Posts
    28
    Hi Andy,

    your right, but keep in mind, that sometimes it is better NOT to use a clustered index or to switch clustering off (MEMBERCLUSTER field on DB2/390 in the tablespace definition). This is e.g. when you use index only access or tablespace scans only. In this case, a clustering index would degrade performance on inserts without a benefit.

    Ingo

    Originally posted by ARWinner
    Then when DB2 writes the data to disk, the pages that contain the data will essentially have random data on them. This will cause reduced performance for large tables because DB2 cannot use prefetching to read the pages from disk. Since you can only specify one cluster index, then chose one that will be used to access the table most often. For example, if you have a table that is queried often for a date range, then the date field should be the primary column in a cluster index.

    HTH

    Andy

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    With DB2 for OS/390, the first index defined is used as the clustering index if no clustering index is explicitly defined (unless clustering is switched off with the MEMBER CLUSTER on the tablespace). With clustering turned off, DB2 uses the tablespace space maps to find empty space and does not necessarily insert at the end of the tablespace. The use of space maps works somewhat differently for simple, segmented, and partitioned tablespaces.

    However, DB2 UDB for Linux, Unix, and Windows does not work that way. Instead, it works as follows, quoted from the “DB2 UDB Administration Guide: Performance V8”:

    “INSERT search algorithm first searches the Free Space Control Records (FSCRs) to find a page with enough space. However, even when the FSCR indicates a page has enough free space, the space may not be usable because it is reserved by an uncommitted DELETE from another transaction. To ensure that uncommitted free space is usable, you should COMMIT transactions frequently.

    The setting of the DB2MAXFSCRSEARCH registry variable determines the number of FSCRs in a table that are searched for an INSERT. The default value for this registry variable is five. If no space is found within the specified number of FSCRs, the inserted record is appended at the end of the table. To optimize INSERT speed, subsequent records are also appended to the end of the table until two extents are filled. After the two extents are filled, the next INSERT resumes searching at the FSCR where the last search ended.

    Note: To optimize for INSERT speed at the possible expense of faster table growth, set the DB2MAXFSCRSEARCH registry variable to a small number. To optimize for space reuse at the possible expense of INSERT speed, set DB2MAXFSCRSEARCH to a larger number.

    After all FSCRs in the entire table have been searched in this way, the records to be inserted are appended without additional searching. Searching using the FSCRs is not done again until space is created somewhere in the table, such as following a DELETE.

    There are two other INSERT algorithm options, as follows:

    APPEND MODE
    In this mode, new rows are always appended to the end of the table. No searching or maintenance of FSCRs takes place. This option is enabled using the ALTER TABLE APPEND ON statement, and can improve performance for tables that only grow, like journals.

    CLUSTERING INDEX DEFINED ON THE TABLE
    In this case, the database manager attempts to insert records on the same page as other records with similar index key values. If there is no space on that page, the attempt is made to put the record into the surrounding pages. If there is still no success, the FSCR search algorithm, described above, is used, except that a worst-fit approach is used instead of a first-fit approach. This worst-fit approach tends to choose pages with more free space. This method establishes a new clustering area for rows with this key
    value.

    When you define a clustering index on a table, use ALTER TABLE...PCTFREE before you either load or reorganize the table. The PCTFREE clause specifies the percentage of free space that should remain on the data page of the table after loading and reorganizing. This increases the probability that the cluster index operation will find free space on the appropriate page.”

Posting Permissions

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