Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to enforce DB2 to use index scan instread of table scan?

    Hi,

    The following SQL:
    Code:
    SELECT A.DependentCol1, B.ParentCol1 FROM
    DependentTable A LEFT OUTER JOIN ParentTable B
    ON (A.DependentColFK = B.ParentColPK)
    after reorg and runstats the above SQL uses 'table scan' (seeing from explain).

    But adding a condition "AND B.ParentCol = COALESCE(B.ParentCol, B.ParentCol)" makes DB2 uses the 'index scan' which is in my case 3-times faster. Index is on column B.ParentColPK - this column is PK of ParentTable.

    Target SQL looks like this:
    Code:
    SELECT A.DependentCol1, B.ParentCol1 FROM
    DependentTable A LEFT OUTER JOIN ParentTable B
    ON (A.DependentColFK = B.ParentColPK)
    AND B.ParentCol = COALESCE(B.ParentCol, B.ParentCol)
    Are there any general rules to enforce 'index scan' instread of 'table scan'? I am just looking is some general tips...

    Thanks,
    Grofaty

  2. #2
    Join Date
    Mar 2004
    Posts
    46
    There is no direct way of "enforcing the optimizer" (well its evolving in DB2) like in some of the other RDBMSes (e.g Oracle).

    What you can do is enable the "volatile" property of the table as
    ALTER TABLE X VOLATILE

    The volatile property tells the optimizer to prefer index scan or table scan.
    Read up the documentation and see if it helps.

    If you are on DB2 v8.2 or above (8.1 fixpack 7 or above), then you can try to use the DB2 Advisor - it may shed some light on what will help your query.

    Jayesh

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    Have you looked into using an optimization hint?

  4. #4
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    What if you use an inner join from the parent to the child instead of an outer join from the child to the parent?

    ex:
    SELECT A.DependentCol1, B.ParentCol1 FROM
    ParentTable B INNER JOIN DependentTable A
    ON (A.DependentColFK = B.ParentColPK)

    It should be functionally equivalent, assuming there is a true parent-child relationship between the tables. The optimizer might be smarter about it.

Posting Permissions

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