Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Posts
    104

    Unanswered: Question about index

    Hi,

    We use db2 8.2.6 on windows 2000.

    I have an elementary question about index/primary key


    We have a table that contains only 3 cols - parent(bigint),child(bigint),createdate.

    parent-child value is unique (qualify as key)

    SQL access always quote either parent or createdate for access.

    I created the table with primary key as parent+child and an index on createdate.

    Since table has only 3 colmns, does index realy speed performance? Or ther is no need for the primary key or the index

    Thanks in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Whether or not an index improves performance depends on:

    1. The number of rows in the table. If the table has less than a few thousand rows, no index may be needed other than to guarantee uniquneness (unique index or index that is created by defining the primary key).

    2. Whether or not DB2 actually uses the index. This may depend on whether DB2 has accurate statistics on the data (from runstats command).

    Assuming this is a large table (more than 100,000 rows), and all predicates that query the table have either parent or createdate (as you stated), then I would recommend the following indexes. You must create these objects in the order specified. Note the use of the INCLUDE in the first index (read the SQL Reference Vol 2 for create index for an explanation of INCLUDE).

    I have provided 2 options for index TABLE_NAME_IX1. The second option enables "index only" access (the table does not need to be accessed on a select), but the index size is larger.

    CREATE TABLE TABLE_NAME ( PARENT BIGINT NOT NULL , CHILD BIGINT NOT NULL , CREATEDATE TIMESTAMP NOT NULL WITH DEFAULT ) ;

    CREATE UNIQUE INDEX PK_TABLE_NAME ON TABLE_NAME (PARENT, CHILD) INCLUDE (CREATEDATE );

    ALTER TABLE TABLE_NAME ADD CONSTRAINT PK_TABLE_NAME PRIMARY KEY(PARENT, CHILD);

    CREATE INDEX TABLE_NAME_IX1 ON TABLE_NAME (CREATEDATE);
    or
    CREATE INDEX TABLE_NAME_IX1 ON TABLE_NAME (CREATEDATE, PARENT, CHILD);
    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
  •