Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004

    Angry Unanswered: EXPLAIN output ...... Confusing ?

    When EXPLAIN'd Matched colums = 0 for a table with index defined on the columns used in the SQL.

    I am not sure What could be the reason for it ? could somebody help me out ?

    Attached Files Attached Files

  2. #2
    Join Date
    May 2003
    Note: Please list your DB2 version and Operating System in future posts. (DB2 for OS/390 version ?).

    Matching columns is the number of leading columns in an index supplied in a predicate (WHERE clause).

    For example with one composite index on (col1, col2) and the following SQL, the number of matching columns is as follows (assuming that DB2 uses the index).

    matching columns = 0
    select * from table_name where col2 = ?
    matching columns = 1
    select * from table_name where col1 = ?
    matching columns = 2
    select * from table_name where col1 = ? and col2 = ?
    When matching columns is zero, DB2 reads the entire index from top to bottom (all of the leaf pages) sort of like a tablespace scan, but on the index. When matching columns is 1 or greater, DB2 uses the b-tree structure of the index to get to the index leaf pages needed.

    The matching columns 0 in your example appears to be in a join with mutilple access paths needed. It may, or may not be, a problem depending on a number of factors.
    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