Results 1 to 11 of 11

Thread: Coalesce

  1. #1
    Join Date
    Nov 2003
    Posts
    18

    Unanswered: Coalesce

    It seems that using the coalesce funtion in my select statements are causing table scans. Does anyone know if this is true and if it is a work around for this? It doesn't makes sense that it does since it is part of the select statement. I want the ability if I get a null value in certain situations to change the column value.

    Thanks,
    Ted

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tmacksam
    It seems that using the coalesce funtion in my select statements are causing table scans. Does anyone know if this is true and if it is a work around for this?
    No I don't think it's true; therefore a work-around is not necessary. A function, COALESCE or not, does not cause table scans by itself - but the way you use that function can.

    What's wrong with table scans, anyway?

  3. #3
    Join Date
    Nov 2003
    Posts
    18
    Here is an example of where it is happening for me:

    Select BPPLNAME, BPFAMILY, BPMODEL, BPPRICE, BPNET, BPEFFI, BPEFFO, COALESCE(BPINCTXT,''), IDESC, IFLG02
    FROM PRCBP LEFT OUTER JOIN IIML01 ON (BPITEM = IPROD) WHERE BPPLNAME = 'NECAD' AND '2005-02-01' BETWEEN BPEFFI AND BPEFFO;

    If I run this it does a table scan on PRCBP if I remove the COALESCE function it uses the logical I have setup. I agree it shouldn't cause a table scan and don't understand why this function would cause this to happen.

    Table Scans if possible should always be avoided, I don't want to read thru the entire table if I don't want to. In this scenario the query runs 3ms slower with COALESCE in the select now granted 3ms is not much but if you have bigger queries the impact is going to be greater.

    This is makes no sense why this would be causing a table scan, I thought it might be how I have written my query but when I remove COALESCE it uses the logicals.

    Any help or suggestions would be greatly appreciated!
    Thanks,
    Ted

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    So your are saying this runs much faster:

    Select BPPLNAME, BPFAMILY, BPMODEL, BPPRICE, BPNET, BPEFFI, BPEFFO, BPINCTXT, IDESC, IFLG02
    FROM PRCBP LEFT OUTER JOIN IIML01 ON (BPITEM = IPROD) WHERE BPPLNAME = 'NECAD' AND '2005-02-01' BETWEEN BPEFFI AND BPEFFO;

    or did you remove the column BPINCTXT completely from the query when it ran faster?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2003
    Posts
    18
    I ran this:
    Select BPPLNAME, BPFAMILY, BPMODEL, BPPRICE, BPNET, BPEFFI, BPEFFO, BPINCTXT, IDESC, IFLG02
    FROM PRCBP LEFT OUTER JOIN IIML01 ON (BPITEM = IPROD) WHERE BPPLNAME = 'NECAD' AND '2005-02-01' BETWEEN BPEFFI AND BPEFFO;

    just removing the COALESCE around BPINCTXT kept this column.

    It isn't much faster 3ms but I am very confused/concerned by adding COALESCE around a column in my select why it won't use the logical. In my opinion this shouldn't affect how the query goes after the data, I am just saying if the value happens to be null change it to an empty space. A co-worker of mine just showed me how she can get it to do a table scan if you add a CAST to your select statement or change a column name by saying column AS NewName. This even makes less sense. I am using Ops Navigator to explain my sql and this is the tool that is showing me the table scans.

    My concern is that this query is going to be used by a lot of users of over time and why am I taking a 3ms penatly in this situation just because I am using a function in my select.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Ops Navigator? Is that an IBM tool? Did you try the DB2 visual explain?

    Also, need information on DB2 release/fixpack, OS, whether you ran extensive runstats (with distribution on key columns), cardinality of table, etc.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Table and index definitions would also be helpful...

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Marcus_A
    Ops Navigator? Is that an IBM tool?
    It turns out those folks have got an iSeries - Operations Navigator is a system management tool for AS/400...

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2/400? All bets are off.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Nov 2003
    Posts
    18
    Thanks for all the help and suggestions but I figured it out and I can't believe the solution instead of doing COALESCE(BPINCTXT,'') I had to do COALESCE(BPINCTXT,' '), I don't understand stand why an empty string would cause this but it did.

  11. #11
    Join Date
    Jan 2005
    Posts
    191
    I would raise a PMR on this - or whatever is your method is of raising bug reports to IBM.

    James Campbell

Posting Permissions

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