Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Unanswered: Optimiser hints???

    I'm having problems with the access path chosen by DB2 when using outer joins.

    e.g.

    select col1
    , col2
    from table1 a left outer join table2 b
    on a.col3 = b.col3
    , table3 c
    where a.colx = <some value>
    and a.col4 = c.col4;

    The query above will retrieve the results of the left join and THEN filter by <some value>. It would be far more efficient to filter by this predicate first and then resolve the outer join.

    I can rewrite the above so that the optimiser decides to use the more efficient path...

    select col1
    , col2
    from table1 a inner join table3
    on a.col4 = c.col4
    and a.colx = <some value>
    left outer join table2 b
    on a.col3 = b.col3;

    but...

    When I have a query that consists of a parent table left joined to several others where the ‘where’ clause naturally follows the outer join syntax, I have no idea how to persuade the optimiser to decide to apply this filter first before satisfying the left join conditions.

    e.g.

    select col1
    , col2
    from table1 a left outer join table3
    on a.col4 = c.col4
    left outer join table2 b
    on a.col3 = b.col3
    where a.colx = <some value>;

    (The <some value> is always from a keyed/indexed field)

    Any suggestions?

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    Damian,

    You can use nested table expressions:

    select col1
    , col2
    from
    (select col1
    , col2
    , col3
    from table1 a inner join table3 c
    on a.col4 = c.col4
    where a.colx = <some value>
    ) aa
    left outer join table2 b
    on aa.col3 = b.col3;
    I hope it helps.

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I'm still finding that access path is determined by the order of my left joins. The optimiser doesn't seem to want to evaluate the operation order and rewrite it when necessary.

    I've been told that I can set the optimisation level to force the optimiser to 'try harder' to find the more efficient path but I can't find any reference to it.

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I'm still finding that access path is determined by the order of my left joins. The optimiser doesn't seem to want to evaluate the operation order and rewrite it when necessary.

    I've been told that I can set the optimisation level to force the optimiser to 'try harder' to find the more efficient path but I can't find any reference to it.

  5. #5
    Join Date
    Apr 2002
    Posts
    18
    If I remember rightly the option your talking about is on the bind. Look up paramters for the bind and you should come across something. In the meantime you can use hints, see below.

    http://www.idug.org/member/journal/w...0/articl10.cfm

Posting Permissions

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