Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Compound index in DB2

    Hi,
    I am trouble shooting a performance problem.
    I run a similar query against two different tables, both have a compound primary key and in both this compound key is indexed.
    The columns that are the compound primary key and the index are:
    ClassId and Index.

    The 1st Query is

    SELECT CLASSID, INDEX, HEIGHT, WEIGHT, AGE FROM PERSON
    WHERE CLASSID = 5 ORDER BY CLASSID, INDEX;

    The 2nd Query is against EMPLOYEE and is

    SELECT CLASSID, INDEX, HEIGHT, WEIGHT, AGE FROM EMPLOYEE
    WHERE CLASSID = 6 ORDER BY CLASSID, INDEX;;

    When I look at the explain plans, one query uses the index the other does not.
    My question is if a index is a combination of columns, when is that index actually used?
    Is it when
    -both columns are specified in the where clause?
    -both columns are specified in the orderBy clause?
    -when one of the two columns is specifed in the where clause?
    -when one of the two columns is specifed in the order by clause?
    or
    -all the time as it is also the primary key?

    Any help greatly appreciated!
    Last edited by Breako; 01-08-07 at 13:57.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Neither of these statements is correct. The right way to put it would be "An index, whether compound or not, is used when the optimizer thinks it is beneficial".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There are 2 kinds of index usage:

    1. B-tree access using the non-leaf pages to quickly locate the leaf pages with the corresponding RID's. This is what most people think of when they say "index access".

    2. Scanning all the leaf pages without any access to the non-leaf pages. This is like a tablespace scan of the the entire index.

    To use access type number 1 above with a composite index, then at least the left most column(s) of the multi-column index must be supplied in the predicate (WHERE Clause). Otherwise, DB2 will not use the index, or may use access type 2 above.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2006
    Posts
    119
    Quote Originally Posted by Marcus_A
    There are 2 kinds of index usage:

    1. B-tree access using the non-leaf pages to quickly locate the leaf pages with the corresponding RID's. This is what most people think of when they say "index access".

    2. Scanning all the leaf pages without any access to the non-leaf pages. This is like a tablespace scan of the the entire index.

    To use access type number 1 above with a composite index, then at least the left most column(s) of the multi-column index must be supplied in the predicate (WHERE Clause). Otherwise, DB2 will not use the index, or may use access type 2 above.
    Thanks for your replies.
    It appears that if the table has no data an index scan is used, but if the table has data an index scan is not used. Any comments appreciated.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 will usually do a table scan if the number of rows is relatively small, because it is faster than using an index. This assumes runstats has been run and DB2 knows the number of rows in the table. A new table may have stats set to -1, which means that runstats has not been run.

    If you alter the table to volatile, then DB2 will ignore the stats and is more likely to use an index if a suitable index is available.
    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
  •